Some of you that attended Convergence may have heard about a significant SQL Server change that can result in substantial performance improvements for Dynamics AX customers. You can find details on the change and how to acquire the associated SQL Server Cumulative Update (CU) in the following KB article:
Parameter sniffing is the default behavior that SQL Server uses when compiling parameterized SQL statements. All SQL emitted from AX is parameterized unless we explicitly request otherwise using ForceLiterals. Parameter sniffing will use the set of parameter replacement values supplied at compile time to determine the selectivity of the query for optimization. The execution plan is then cached for reuse.
If when the query plan is compiled and the parameter values are atypically small (for example, a small DataAreaId or and ItemId with few inventory dimension combinations) the query plan created can very likely be suboptimal when reused for more representative data values.
The change described in KB980653 is implemented via a SQL Server trace flag: 4136. When implemented, SQL Server will use an "average" estimate of selectivity derived from the All Density portion of optimizer statistics. This is identical to the behavior enabled through the new SQL2008 OPTION(OPTIMIZE FOR 'UNKNOWN') hint.
Although our testing and preliminary customer testing have shown dramatic improvements, it's important that customer complete a degree of testing with the trace flag 4136 enabled prior to deployment in their production environments.