UA-44032151-3 page contents

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

 

 

 

 

 

Comments (21)
  1. Enabling the setting for using literals will for sure solve parameter sniffing issues but also consume more resources on the SQL server. At a current implementation we do have over 2000 legal entities. What is the consequence for the SQL server with this high number of legal entities if we enable the dataarea literals? Are there any experiences? Probably it will have effect on the compilation rate. Do we have to look for several settings to be optimized on the SQL server? What would be the recommendations in this case?

    1. Hello André,

      We do not have any official benchmark on this best practice but we are not aware of any negative impact from our customers. Some of them have from few hundreds to thousands of company in the same instance.
      The performance impact is very hard to estimate, hence our recommendation to test it broadly on UAT/Staging. Enabling the Dynamics AX Kernel Parameter Sniffing might increase the number of compilation so we can recommend you to watch out for Windows Performance Monitoring counters SQL Compilation/sec and SQL Batch Requests/sec.

      Regards,
      @BertrandCaillet

  2. BPall says:

    Hi Michael, tnx for sharing! As time moves i guess this is still relevant in AX 2012 R3.

  3. Logger says:

    Unfortunately, this fix have some bug

    stackoverflow.com/…/html-printing-generates-empty-zero-width-columns

    Are  there any hotfixes for this issue ?

  4. Christoper Songco says:

    I hope that some guidelines on how to test if this hotfix works can be published immediately. So customers of AX2009 will know if this hotfix really works.

  5. @Frank,  TF 4136 is a very tricky flag to work with.  My success rate with it is maybe 1 or 2 customers out of 10 achieve better performance with it enabled then not enabled.  Since it is easy to turn on and turn off trace flag and dump procedure caches I would Test in your TEST env. and if things look good try it during a slow afternoon in prod and be ready to turn it off and drop the proc cache if things go bad.

  6. Hot Fix has been released for Dynamics AX 2009 Service Pack 1.

    Look for KB2934938.

    Regards,

    Bertrand

  7. Jason says:

    Awesome post! Thanks Michael!

  8. Frank says:

    Hello Michael!

    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,

    Frank

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

  10. Johan Sandqvist says:

    Thanks Michael!

  11. Mosaad says:

    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?

  12. Yogesh Kasat says:

    Thanks Michael for getting to root cause of the issue and creating solution for rest of the AX community!

  13. Michael DeVoe says:

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

  14. Michael DeVoe says:

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

  15. Johan Sandqvist says:

    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?

  16. David Marks says:

    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?

  17. Michael DeVoe says:

    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.

  18. Steeve says:

    A fix for Ax 2009! Great!  I'll be happy if we can stop running DBCC FREEPROCCACHE  from time to time.

  19. Karabela says:

    please update the hotfix url.

Comments are closed.

Skip to main content