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

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.