How to proactively avoid parameter sniffing step-by-step
In the following blog post the so called „parameter sniffing“ is explained:
http://blogs.msdn.com/b/axinthefield/archive/2014/01/09/sql-server-parameter-sniffing-with-dynamics-ax-just-plain-evil.aspx
The purpose of this blog post is to explain the fix implementation steps a little more in detail:
Notes:
The kernel only hotfixes mentioned for MS Dynamics AX in the blog post above are minimum prerequisites. Because all kernels are cumulative I’d therefore recommend to install the latest kernel which you can always find here:
http://blogs.msdn.com/b/axsupport/archive/2012/03/29/overview-of-ax-build-numbers.aspx
If you like you may find more useful information about kernel updates in general here:
http://blogs.msdn.com/b/axsupport/archive/2014/12/10/faq-ax-kernel-hotfixes.aspx
Like for every kernel installation please be aware of this:
– The following steps should be tested in a test environment first before doing that in the production environment in order to make sure that the updated kernel works as expected in your specific, customized environments.
– If you update the kernel then all AX clients and all AOS servers have to be updated in a unique manner per environment.
1) Make sure you downloaded the latest kernel
2) Install the kernel via running axupdate.exe that comes with the kernel hotfix itself. (Note: Or take the latest axupdate.exe from LCS (https://lcs.dynamics.com )if you run AX 2012 R3 CU 8 or higher)
Please also make sure to take the following hints into consideration here in general and depending on your MS Dynamics AX version:
https://technet.microsoft.com/en-us/library/hh538446.aspx
3) After the kernel installation please make sure that all of you AOS service(s) are stopped.
4) Run the following T-SQL query against your MS Dynamics AX transaction database using SQL server management studio depending on your MS Dynamics AX version:
AX 2009 SP 1:
UPDATE SQLSYSTEMVARIABLES SET VALUE = ‘1’ WHERE PARM = ‘DATAAREALITERAL’
AX 2012 R1 (=RTM)
UPDATE SYSGLOBALCONFIGURATION SET [VALUE] = 1 WHERE NAME = ‘DATAAREAIDLITERAL’
AX 2012 R2 and AX 2012 R3
UPDATE SYSGLOBALCONFIGURATION SET [VALUE] = 1 WHERE NAME IN ( ‘DATAAREAIDLITERAL’,’PARTITIONLITERAL’)
5) Restart your corresponding SQL server service
6) Restart your all AOS services
Now your AX clients are ready to login again and test planned.
