AX 2009 SP1, Parameter Sniffing finally addressed by Microsoft

Most of you have probably struggeled with Parameter Sniffing issues in AX solutions and it finally seems like Microsoft has been able to address this design issue also for AX 2009 SP1! The fix introduced in AX 2012 R2 CU6 has been backported to AX 2012 R0/R1 and now also AX 2009 SP1.Please see AX 2012 R2 CU6, Feature to reduce the effect of Parameter Sniffing.

Please have a look at SQL Server Parameter Sniffing with Dynamics AX, just plain evil. (Redux 5-19-2014) and note that kernel builds after 5.0.1600.2553 (KB2934938) will include a fix that changes the way SQL statements are generated from the AOS by always sending the value for the column DATAAREAID as a literal instead of a parameter (the x-factor).

Also please note that the instructions for updating the value in the table SQLSYSTEMVARIABLES are wrong - the value for the column PARM in the row added to SQLSYSTEMVARIABLES as part of implementing this kernel build, is DATAAREALITERAL (NOT DATAAREAIDLITERAL).

The correct SQL Update Statement to enable literals for DATAAREAID is

UPDATE SQLSYSTEMVARIABLES SET VALUE = '1' WHERE PARM = 'DATAAREALITERAL'

It took me a couple of minutes to realize this when first enabling the DATAAREALITERAL in an AX 2009 SP1 solution for the first time, but a quick SELECT aginst SQLSYSTEMVARIABLES, returned a row containing the value DATAAREALITERAL.

This fix should be evaluated and implemented in every AX 2009 SP1 solution having one company contributing to most rows in shared tables to avoid "Plain Evil", frustration and ad hoc work.

Verification:

Actual value for DATAAREAID is replaced by 'xxx'.

SELECT SUM(A.QTY) FROM PROJINVOICEITEM A,PROJITEMTRANS B WHERE (A.DATAAREAID=N'xxx') AND ((B.DATAAREAID=N'xxx') AND ((B.PROJTRANSID=A.PROJTRANSID) AND (B.PROJTRANSIDREF=@P1)))

SELECT A.AGREEMENT,A.ITEMCODE,A.ACCOUNTCODE,A.ITEMRELATION,A.ACCOUNTRELATION,A.QUANTITYAMOUNT,A.FROMDATE,A.TODATE,A.AMOUNT,A.CURRENCY,A.PERCENT1,A.PERCENT2,A.DELIVERYTIME,A.SEARCHAGAIN,A.PRICEUNIT,A.RELATION,A.UNITID,A.MARKUP,A.ALLOCATEMARKUP,A.MODULE,A.INVENTDIMID,A.CALENDARDAYS,A.CIT_AGREEMENT,A.RECVERSION,A.RECID FROM PRICEDISCTABLE A WHERE ((DATAAREAID=N'xxx') AND ((((((((((RELATION=@P1) AND (ITEMCODE=@P2)) AND (ITEMRELATION=@P3)) AND (ACCOUNTCODE=@P4)) AND (ACCOUNTRELATION=@P5)) AND (UNITID=@P6)) AND (CURRENCY=@P7)) AND (INVENTDIMID=@P8)) AND ((@P9>=FROMDATE) OR ((FROMDATE=@P10)))) AND ((@P11<=TODATE) OR ((TODATE=@P12))))) ORDER BY A.DATAAREAID,A.RELATION,A.ITEMCODE,A.ITEMRELATION,A.ACCOUNTCODE,A.ACCOUNTRELATION,A.CURRENCY,A.INVENTDIMID,A.UNITID,A.QUANTITYAMOUNT,A.RECID,A.FROMDATE

AX 2012 Technical Journal

Just a short message - future entries regarding AX 2012 will be published in a separate blog titled AX 2012 Technical Journal.

Stay tuned for new updates.

AX2009 SP1 secret statistics

I while ago (September 2010) I asked a question based on a simple graph. I thought it was time for an update leaving the question open.

Looking forward (read: AX 2012)

Back in November 2008 I blogged about AX 2009 after attending the Convergence conference in Copenhagen and I was pretty excited about the possibilities. After working with AX 2009 since it was released in 2008, I'm still excited but also more realistic about several aspects of implementing and operating AX. After gaining hands on experience, you discover all the rough edges and you get a feel for the quality of the software with regards to things not woring as expected for instance measured as the number of hotfixes released. All in all it seems like AX 2009 has succeeded in the market.

So whats next? AX 2012 was officially published as the name of the next version of AX (aka AX 6) at the Microsoft Dynamics Technical Conference taking place in Redmund in January. I wasn't able to participate, but based on the information published after the conference, it certainly seems like a huge (giant?) shift both with regards to added and extended functionalty, and technical with some architectural changes as well (like the possibility to replace the classic AOD files in the file system with storage in the database).

The post by Jake at AxaptaSource gives a good summary of what was presented at the conference and it should be pretty clear to everyone that we are talking about the biggest shift in the history of AX and a shift that will have an impact on everyone involved in the value chain. This is of cource in the category of un official information, but still quite trustworthy as far as I can understand.

Modelling is one example that will influence developers and people involved in deploying changes. Much tighter integration to Visual Studio is another. The security system is re designed with roles becoming a real part of the security system (a role in AX 2009 is the same as the Role Center assigned to a user at least within AX) and the requirement of defining a user in Active Directory, seems not to be a requirement anymore (replaced by Claims based security in SharePoint for non AD users). Setup is made easier and system management is prioritzed with a new add in for System Center Operation Manager. The BizTalk adapter is gone and replaced by the WCF adapter (Web Services). The (data) caching machanisms are re designed (much needed in my mind). Include columns are finally supported for SQL Server 2008 togheter with support for full text indexes. Huge performance improvements mainly in the area of CIL execution (Common Intermediate Language). Drag and drop transformation of forms to web pages in Enterprise Portal (indicates big changes in the area of forms in general). Workflow executing as part of the AOS service and shared workflow editor (designer) withVisual Studio 2010. Management through PowerShell. AIF service contracts have changed (possibly influencing the upgrade effort) and less complexity (hey, that sounds like a step in the right direction). Very thigh integration to Excel supporting data entry and synchronization from Excel (big time mental change for a lot of people I think). Big improvements in the area of debugging and batch execution in addition to AOS beeing able to host WCF processes (a real enabler).

Add a module for Public Sector (be aware Agresso and Visma :-)) and 1200 new features in addition to some major rewrites (rumors), and you probably start to see the big picture. I think the AX Community must start preparing for AX 2012 on the mental level since this release will require a shift in most of the ways we have been used to treat new releases of AX.

Virtualization of AOS - VMWare versus Hyper-V

A very interesting thread on the Dynamics AX Community forum regarding virtualization of AOS.

As stated, the only important information missing right now, is a description of the differences in the underlying infrastructure between the VMWare and Hyper-V implementations.

Update:
Te original poster has published information regarding the infrastructure and this information only increases the value of this subject - he has managed to test virtualization of the AOS role under VMWare and Hyper-V on the same (identical) infrastructure! This makes his findings extremely interesting (at least to me) partly because it leaves room for possible reasons/explanations/theories and maybe the biggest paradox - if his findings are true in general, a lot of customers running AOS under VMWare are possibly influenced by decreased performance caused by differences in how the AOS behaves on VMWare. Since most customers most likely are using VMWare, the scope of this subject with regards to the number of customers possibly affected, makes it one of the most interesting subjects in a long time.

It could of course be tightly coupled to the OS and how the OS performs under the two hypervisors (as mentioned as a possible reason in the thread) and I will be light on this until some more research has been done.

Comments are welcome as usual.

AX 2009 and edition of SQL Server Analysis Services

I recently came across an issue when trying to build a SSAS project containing modifications to the standard AX Cubes.

When building or rebuilding the project, I got 1 047 (!) errors with the description "Dimension [x] : Only dimension attributes and attribute relationships can have translations in Standard Edition."

The only solution to this is to (try to) modify the SSAS database in SQL Server Management Studio by some heavy scripting. Or of course upgrade SSAS to Enterprise Edition which I really don't know is legal without licensing Enterprise Edition (I'm trying to clarify this). Anyway it's a valid upgrade path regarding to the information given by Microsoft for SQL Server 2008 R2 (Version and Edition Upgrades) on MSDN.

The system requirements for AX 2009 doesn't contain any information saying SSAS Enterprise Edition is needed and neither does the general SQL Server reqiurements.Until further noticed and official information is given from Microsoft, it's worth having this in mind when deciding which edition of SQL Server to use together with AX 2009.

The only information from Microsoft I have found on this issue, is in a draft document titled "How to add financial dimension to Dynamics AX default cubes" (PDF) from the EMEA Dynamics Support blog on MSDN. On the bottom of page 3 and the beginning of page 4, the following information is given:

This is the only information from Microsoft I have found and despite this beeing a draft document, it clearly states that the system requirements should be updated alternatively that another soultion is provided. From this I understand that you have to backup the OLAP database and restore it to an instance of SQL Server 200x Developer Edition (equals Enterprise Edition in functionality), create a project in BIDS, build and deploy the changes to Developer Edition, and finish doing a final backup - restore sequence back to the production instance of SSAS.

In general, I think most customers will end up designing their own cubes and modifying the needed Role Centers accordingly, to avoid all the struggle with the default cubes.

Experience doing an AX 2009 Complete Install

A short update on my experience from doing a complete install of AX 2009 with dedicated servers for each server role.

First of all, the process went quite well and the only struggle was to get all the Service Principal Names (SPNs) correct including named instances and the SQL Server Browser service on the SSAS server.

Main take aways:

• Spend time on planning and account for some issues related to Kerberos authentication (hard to estimate)
• Follow the latest version of the AX Installation Guide
• Make sure you deploy the latest Cumulative Rollup package for AX (and other services)
• Bookmark the AX EMEA Support Team Blog (very valuable source of information)
• Make sure each component work as expected before you proceed the install and configuration sequence

After installing SharePoint 2010 for the first time, I also have a suggestion for Microsoft regarding AX setup - please have a look at the SharePoint 2010 Products Preparation Tool (shown below) and incorporate this idea into the next release of AX.