AX 2009 SP1, Parameter Sniffing finally addressed by Microsoft

Most of you have probably struggeled with Parameter Sniffing issues in AX solutions and it finally seems like Microsoft has been able to address this design issue also for AX 2009 SP1! The fix introduced in AX 2012 R2 CU6 has been backported to AX 2012 R0/R1 and now also AX 2009 SP1.Please see AX 2012 R2 CU6, Feature to reduce the effect of Parameter Sniffing.

Please have a look at SQL Server Parameter Sniffing with Dynamics AX, just plain evil. (Redux 5-19-2014) and note that kernel builds after 5.0.1600.2553 (KB2934938) will include a fix that changes the way SQL statements are generated from the AOS by always sending the value for the column DATAAREAID as a literal instead of a parameter (the x-factor).

Also please note that the instructions for updating the value in the table SQLSYSTEMVARIABLES are wrong - the value for the column PARM in the row added to SQLSYSTEMVARIABLES as part of implementing this kernel build, is DATAAREALITERAL (NOT DATAAREAIDLITERAL).

The correct SQL Update Statement to enable literals for DATAAREAID is

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

It took me a couple of minutes to realize this when first enabling the DATAAREALITERAL in an AX 2009 SP1 solution for the first time, but a quick SELECT aginst SQLSYSTEMVARIABLES, returned a row containing the value DATAAREALITERAL.

This fix should be evaluated and implemented in every AX 2009 SP1 solution having one company contributing to most rows in shared tables to avoid "Plain Evil", frustration and ad hoc work.

Verification:

Actual value for DATAAREAID is replaced by 'xxx'.

SELECT SUM(A.QTY) FROM PROJINVOICEITEM A,PROJITEMTRANS B WHERE (A.DATAAREAID=N'xxx') AND ((B.DATAAREAID=N'xxx') AND ((B.PROJTRANSID=A.PROJTRANSID) AND (B.PROJTRANSIDREF=@P1)))

SELECT A.AGREEMENT,A.ITEMCODE,A.ACCOUNTCODE,A.ITEMRELATION,A.ACCOUNTRELATION,A.QUANTITYAMOUNT,A.FROMDATE,A.TODATE,A.AMOUNT,A.CURRENCY,A.PERCENT1,A.PERCENT2,A.DELIVERYTIME,A.SEARCHAGAIN,A.PRICEUNIT,A.RELATION,A.UNITID,A.MARKUP,A.ALLOCATEMARKUP,A.MODULE,A.INVENTDIMID,A.CALENDARDAYS,A.CIT_AGREEMENT,A.RECVERSION,A.RECID FROM PRICEDISCTABLE A WHERE ((DATAAREAID=N'xxx') AND ((((((((((RELATION=@P1) AND (ITEMCODE=@P2)) AND (ITEMRELATION=@P3)) AND (ACCOUNTCODE=@P4)) AND (ACCOUNTRELATION=@P5)) AND (UNITID=@P6)) AND (CURRENCY=@P7)) AND (INVENTDIMID=@P8)) AND ((@P9>=FROMDATE) OR ((FROMDATE=@P10)))) AND ((@P11<=TODATE) OR ((TODATE=@P12))))) ORDER BY A.DATAAREAID,A.RELATION,A.ITEMCODE,A.ITEMRELATION,A.ACCOUNTCODE,A.ACCOUNTRELATION,A.CURRENCY,A.INVENTDIMID,A.UNITID,A.QUANTITYAMOUNT,A.RECID,A.FROMDATE