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 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.

AX 2009 Rollup 6 and KB 2278963 (SharePoint 2010)

Finally I'm doing a complete install of Dynamics AX 2009 on a new plattform for a customer! While performing the installation of the core solution, my verified plan (verified by Microsoft) was to install in this sequence:

1. AX 2009 RTM patched with binaries from the KB
2. AX 2009 SP1
3. AX 2009 SP1 RU6
4. Initialize
5. Apply application fix for the KB

Everything went according to plan until step 5. This step failed with the following message:

This update is not applicable to your installation of Microsoft Dynamics AX because wrong version of application files is installed. This update is applicable to version 5.0.1000.52-5.0.1500.3560 of application files.

Setup could not complete the installation.

After checking my sources and discussing with Microsoft without any progress, I decided to install a second application in this sequence:

1. AX 2009 RTM
2. AX 2009 SP1
3. Initialize
4. KB 2278963
5. AX 2009 SP1 RU6

This time everything went according to plan and based on my experience, one of the following are true for this issue:

• KB2278963 application hotfix is forgotten with regards to RU6

or

• KB2278963 is included but not documented as part of RU6 (or RU5)

or

• Wrong version is used when the axupdate process verifies the application version (max is 5.0.1500.3760 - RU6 gives 3761)

The worst thing is that I had to spend extra time and effort doing a new install and 2 compilations in addition to installing a separate AOS and a Client to work on the same versionlevel.

Question

A little challenge to my readers:

Can You tell me what I'm trying to illustrate in the graph below?

A couple of tips along the way

• it's closely related to Microsoft and Dynamics AX 2009 SP1...
• the average is almost 69...

Please leave your answer as a comment.

AX 2009 - deadlocks

I recently conducted a system review at a customer site and discovered a message totally new to me beeing logged in on all AOS: "Object Server xx: Dialog issued for client-less session 1: Cannot edit a record in LastValue (SysLastValue). User ID: ,AdminUserSetup. Deadlock where one or more users have simultaneously locked the hole table or part of it."

The database at hand was not configured to use Read Committed Snapshot Isolation (RCSI) as recommended by the AX Performance Team. Enabling RCSI on the database solved the issue.

Short note on AX and database mirroring in SQL Server 2008

The last days, I have been working with a customer running AX 2009 and SQL Server 2008 SP1 Standard (x64) on Windows Server 2008 SP2. The solution suffered from general performance issues that possibly could have a lot of sources. As usual my attention was around SQL Server and this time I started looking at Wait Stats which showed a waste amount of waits related to mirroring. After some Googling, I was a little bit confused about this beeing normal or not. The nature of database mirroring could in fact result in high Wait Stat values since the processes involved, is mostly sleeping (suspended). But I choosed to consider this as a possible source since the nature of database mirroring in my oppinion, does'nt fit the nature of AX as a classic OLTP application. And since disabling database mirroring is an isolated and low risk operation, we choosed to stop mirroring for all database (around 10). After this, the overall performance increased and the customer reported this almost immediately. It's a little bit early to conclude, but the initial responce seems pretty promising.

Minor observation installing AX 2009

Some time back, I did a QA run on one installation suffering some performance issues. The consultant originally installing AX choosed to use the installer to create the database (shown in the picture below). Personally I prefer to create the database manually, both to get control over every aspect and it usually don't take more than 30 minutes to get everything in place manually. The solution at hand was running SQL Server 2008.

While looking into the database configuration (sp_helpdb), I noticed that the compatibility level was set to 90 which corresponds to SQL Server 2005. At this stage, I asked the consultant doing the installation why he had choosen to set the compatibility level to 90 and he then answered that he always used the AX installer to create the database.

Without testing this option when installing AX, it seems like Microsoft has provided a template database beeing attached to the specified SQL Server instance by the installer in the security context of the setup user. I guess the installer asks for some information like collation, file locations etc. but compatibility level keeps untouched.

Compatibility level is a database level configuration option and it is used by the database instance to decide which functionality the database instance should "activate" for each database. It's mostly used to provide backwards compatibility during migration of databases, but it could also impact the ability to utilize new features.

A good reference can be found at MSDN and compatibility level could possibly be a source for some time consuming investigations. It seems like compatibility level = 90 is OK even for an AX database running on SQL Server 2008, but I don't see any reason for not setting it to 100 to make sure all new features in SQL Server 2008 is enabled for the AX database.

Or always create the database manually using the system database model as the template (will in most situations match the version of SQL Server).