SQL Server Parameter Sniffing with Dynamics AX, just plain evil

I do not want to write a novel or a technical manual on Dynamics AX and SQL Server Parameter Sniffing and the problems it causes so I try and keep this as short as possible. When Dynamics AX X++ code in converted in TSQL cursors it is usually in the form of a SQL Statement usually with a WHERE clause to find/delete/update a certain record or records.  The WHERE clause can be thought of as a way to “FILTER” data.

Example

SELECT SUM(A.DEBITMST),SUM(A.CREDITMST) FROM LEDGERBALANCESDIM A WHERE ((DATAAREAID=@P1) AND ((TRANSDATE<@P2) AND (PERIODCODE=@P3)))

Notice the “@P1”, “@P2”, and “@P3”, these are parameters and represent literal values.  Parameters are used so the statement/plan can be reused by SQL Server no matter what the
values for those parameters are.  This saves resources such as plan cache memory and compile/recompile time which equals lower CPU utilization.  The first time a statement is sent to SQL Server from Dynamics AX it has the initial set of parameters sent with it and SQL complies and creates the execution plan on these parameter values.  Now every time this statement is used after that it will use the execution plan that was compiled from the initial values.

Example

SELECT SUM(A.DEBITMST),SUM(A.CREDITMST) FROM LEDGERBALANCESDIM A WHERE ((DATAAREAID=@P1) AND ((TRANSDATE<@P2) AND (PERIODCODE=@P3)))

@P1 = “IND”, @P2 = “12/12/12”, @P3 = “3”

Now let’s say that @P1 (DATAAREAID) “IND” is a very small company and only represents 0.01% of the overall data in the LEDGERBALANCESDIM table.  Depending on the statistical sampling done on this table DATAARAAID = “IND” may not even exist on the histogram for the statistics on the DATAAREAID column.  This will cause SQL Server to scan the table to find the records and possibly do millions of reads and take seconds.

Now another user comes in and does the same activity and ruses the plan created by the first user but this time the DATAAREAID = “UDS” which makes up 98.9% of the overall data in that table.  SQL Server is still going to scan because it is reusing the plan that was created for the “IND” value when normally this would be a very quick index seek.  This is a nutshell is parameter sniffing.

Now as you can see this can create very inconsistent performance as it depends on which parameter values are used the first time the statement is issued to SQL to dictate performance for every subsequent usage of
that statement regardless of the new parameter values supplied.

Possible Scenario

SQL Server is patched and rebooted (all plans are dropped). AX user logs in and does a task that issues our statement in the above example and DATAAREAID = ‘USD’ and that is what the plan is compiled with.
Performance is consistent and everybody is happy.  Our plan is aged out of cache and another users does the activity but this time DATAAREAID = ‘IND’ and now our plan is compiled with that value and performance tanks as each time this statement is used it does a scan regardless of the values and stays this way until UPDATE STATISTICS is run on the table (this will drop all plans for that table), indexes are rebuilt on that table (This will rebuild the stats which will drop all plans on that table), DBA runs DBCC FREEPROCACHE (this drops ALL plans on every table), or SQL Server is restarted.

Are you experiencing Parameter Sniffing on your Dynamics AX deployment?  This is not an easy question to answer as it even takes me a while of looking through SQL DMV data and taking with admins and users to be able to be relatively certain that it is happening and I am supposedly and expert on these types of things.  There are some questions you can ask that can give you a pretty good idea if Parameter Sniffing is effecting you Dynamics AX
deployment.

1. Do you have more than one Company and is the data distribution between those companies VERY uneven?
2. Is the overall performance of Dynamics AX very inconsistent, one day working normally the next performance is bad?  This could even be over a few hours.
3. Does Rebuilding Indexes seem to correct the performance issues?
4. Does Rebuilding Statistics seem to correct the performance issues?
5. Does running DBCC FREEPROCCACHE seem to correct the performance issues?
6. Does restarting/rebooting SQL seem to correct the performance issues?

If you answered “Yes” to the first 3 questions and “Yes” to any of the last 3 questions then chances are pretty good that you are being effected by Parameter Sniffing.

What to do?

AX 2012 R2

The fix was originally released as part of CU 6, but this caused a regression issue when enabled.  The fix for this has been released under KB 2969229 Build 6.2.1000.7877

After applying the fix you can re-enable literals for PARTITION and DATAAREAID using the following SQL statement.

UPDATE SYSGLOBALCONFIGURATION SET [VALUE] = 1 WHERE NAME IN ( 'DATAAREAIDLITERAL','PARTITIONLITERAL') 

All AOS servers must be restarted for the fix to take effect

 ///Updated 10/06/2016

AX 2012

The original fix build 6.0.1108.6634 was regressed due to problem with company switching the NEW corrected Hotfix KB 2920058 Build 6.0.1108.7185 is now available.

After applying the fix you can re-enable literals for DATAAREAID using the following SQL statement.

UPDATE SYSGLOBALCONFIGURATION SET [VALUE] = 1 WHERE NAME = 'DATAAREAIDLITERAL'

All AOS servers must be restarted for the fix to take effect

///Updated 10/17/2014

AX 2009 SP1

The fix was released under KB 3000700  Build 5.0.1600.3084

After applying the fix you can enable literals for DATAAREAID using the following SQL statement.

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

All AOS servers must be restarted for the fix to take effect