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.
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 thevalues 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.
@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 ofthat statement regardless of the new parameter values supplied.
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 AXdeployment.
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
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.6720 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'
AX 2009 SP1
The fix was released under KB 2934938 Build 5.0.1600.2553
After applying the fix you can enable literals for DATAAREAID using the following SQL statement.
UPDATE SQLSYSTEMVARIABLES SET VALUE = '1' WHERE PARM = 'DATAAREAIDLITERAL'
please update the hotfix url.
A fix for Ax 2009! Great! I'll be happy if we can stop running DBCC FREEPROCCACHE from time to time.
Karabela, I will update the Blog this afternoon with the correct link and build info. I was in a hurry and used the internal hot fix link. :-(
Steeve, Haha, you and many other customers.
I know there is a trace flag within SQL that can globally turn off parameter sniffing. What are your thoughts on turning this off globally?
Great post, and nice to hear there's a fix for this. Just based on your experience; what would constitute a "VERY uneven" distribution? The 0.01% in your example would obviously be very uneven, but what would likely be an upper limit for this issue? One percent, ten, twenty?
@David marks - You are referring to Trace Flag 4136 that uses the "optimize for unknown" functionality is SQL server which ignores the parameter value and uses a statistical average. I have found that at 9 out of 10 customers this causes are many problems as it fixes. Very rarely dos it provide better overall performance. We only used it on a very small group if customers. Using literals is a far superior way of dealing with parameter sniffing on the DATAAREAID column.
@Johan Sandqvist - I would define "uneven" as anything that causes values for PARTITION or DATAAREAID to be left off the histogram for of the statistics. Lets say you have 5 different DATAAREAID's and you do a DBCC SHOWSTATISTICS on a table and only 3 of the 5 DATAAREAID's show up. This is because when the statistics are built they use a sample size. If the sample size does not include a specific DATAAREAID because it represents such a small amount of the overall data then that would be uneven.
Thanks Michael for getting to root cause of the issue and creating solution for rest of the AX community!
I am dealing with 3 companies, 1 company is big with approx. 98% of the data, and the other 2 are very small with multiple empty tables.
Is it better in my case to enable the DATAAREAIDLITERAL, and what would be the expected drawbacks?
@Mossad, with that type of data distribution it is possible that when the statistics are computed the sample size will not be large enough to pull all three DATAAREAIDs so one or two could be left of the histogram causing problems with parameter sniffing so to answer your question, "Yes" I would enable DATAAREAIDLITERAL in that case.
We are using SQL Server 2008 under AX 4.0 and i can answer most of the questions with yes: Can i get the same effect using trace flag: 4136? I found an article here: blogs.msdn.com/.../important-sql-server-change-parameter-sniffing-and-plan-caching.aspx
Thanks in advance,
Awesome post! Thanks Michael!
Hot Fix has been released for Dynamics AX 2009 Service Pack 1.
Look for KB2934938.