Share via

Overcoming parameter sniffing issue in Microsoft Dynamics AX 2012-R2 – CU6

  • Article

Parameter sniffing is the default behavior that SQL Server uses when compiling parameterized SQL statements. When a stored procedure or a parameterized SQL statement is compiled or recompiled, the parameter values passed for that invocation are "sniffed" and used for cardinality estimation. The net effect is that the plan is optimized as if those specific parameter values were used as literals in the query. This plan is cached and will be reused. 

Some of the AX customers have uneven disparity in data distribution for different Companies/Les (typically one or two companies may have most of the data volume and all other companies have very less data). Under these conditions the cached plan for a specific company/LE may result in very poor performance for another company/LE. 

It is possible to change how SQL Server is optimizing queries using trace flag 4136 and this may have a positive effect if parameter sniffing is an issue in your system. More often than not the overall performance degraded when trace flag 4136 has been used. Customers should be careful about using the trace flag 4136, as it can result in poor performance in undesired areas as it is trying to ‘Optimize for UNKNOWN’. Please evaluate using below described feature instead.

Many customers/partners brought this to Microsoft Dynamics AX team’s attention. The latest cumulative update for R2 (CU6) addresses this by passing 2 kernel parameters PARTITION, DATAAREAID as literals. If you have R2- CU6 installed and if you know you are facing parameter sniffing issues due to uneven data distribution, you could try enabling this feature.

Note: This feature is turned OFF by default.

When you installed AX2012 – R2 CU6, it would have created 2 new records in SYSGLOBALCONFIGURATION. With names ( 'DATAAREAIDLITERAL', 'PARTITIONLITERAL')

To turn the feature ON you need to update these records and set the value to 1. Following update will enable the feature on all the AOS.

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

To turn the feature OFF you need to update these records and set the value to 0. 

Note: There is no UI available for this and this needs to be turned ON/OFF from SQL.

After updating the value to 1, when you restart the AOS this feature gets enabled.

Here is a sample parameterized SQL when this feature is OFF. (Captured thru the trace/traceparser. The field list of the select clause bellow is shown truncated)

SELECT TOP 1 T1.SALESID,.......,T1.RECID

FROM SALESTABLE T1

WHERE (((PARTITION=?)

      AND (DATAAREAID=?))

      AND (SALESID=?))

Here is a sample parameterized SQL when this feature is ON. (Captured thru the trace/traceparser. The field list of the select clause bellow is shown truncated)

SELECT TOP 1 T1.SALESID,.......,T1.RECID

FROM SALESTABLE T1

WHERE (((PARTITION=5637144576)

      AND (DATAAREAID=N'ceu'))

      AND (SALESID=?))

The internal performance testing of different scenarios did not show any performance regression when the feature is turned on. But before you enable this feature in production, you should test this feature in your test system thoroughly and ensure it works for you.

Note: AX32Serv binary (AX32Serv.exe) of CU6 contain the fix. If you are just taking the binary alone, you may have to insert two records into SYSGLOBALCONFIGURATION with names 'DATAAREAIDLITERAL', 'PARTITIONLITERAL' and set the values to 1 to enable this feature.

Comments

  • Anonymous
    October 29, 2013
    Was this ever backported to 2012 R1?
  • Anonymous
    October 29, 2013
    Not yet. But will be available soon.
  • Anonymous
    December 25, 2013
    Was experiencing the same in  href="www.evinciblesolutions.com/index.aspx">Microsoft Dynamics AX</a> issue till this article helped me out. Thanks for taking the time!
  • Anonymous
    March 30, 2014
    Hi ,Is this available in Ax 2012 RTM now ?
  • Anonymous
    April 02, 2014
    I tried it with AX 2012 build from February and it worked!
  • Anonymous
    January 21, 2015
    This fix has been backported to 2012 RTM and AX 2009.AX 2012 R2From here:blogs.msdn.com/.../sql-server-parameter-sniffing-with-dynamics-ax-just-plain-evil.aspxThe 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.7877After 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 effectAX 2012The 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'All AOS servers must be restarted for the fix to take effect///Updated 10/17/2014AX 2009 SP1The fix was released under KB 3000700  Build 5.0.1600.3084After 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
  • Anonymous
    May 24, 2015
    I'd like to turn the feature on (set value to 1 for dataareaid and partition) and am on 2012/CU7. Wondering if I need to install the post CU6 (KB 2920058) or if I should be fine, as is.