The purpose of this post is to list and briefly explain 4 SQL Server trace flags that we've found to be helpful in certain circumstances when working with our Dynamics AX customers in the field. In general, I don't recommend enabling these trace flags unless you're experiencing the specific issues they're meant to address. Hopefully you'll find this to be a useful reference, and maybe you'll find a scenario below that helps you get more out of your Dynamics AX implementation.
Trace flag 1224 disables lock escalation based on number of locks. Enabling this trace flag can reduce the likelihood of blocking due to lock escalation- something I've seen with a number of AX implementations. The most common scenario where this becomes an issue is when there's a requirement for Master Planning to run during the day.
The Master Planning process often times acquires enough locks (5,000+) on the InventSumLogTTS table to escalate from row level locks to a table level lock. When a table lock is acquired by the Master Planning process, other activities such as sales order and inventory processing are blocked until the Master Planning work is complete.
For more information on how to identify if lock escalation is causing blocking in your environment, see my June 2011 post on the topic:
More information about trace flag 1224 can be found in the following MSDN article: http://msdn.microsoft.com/en-us/library/ms188396.aspx
The base Dynamics AX product doesn't use full text search, but there are some common AX vertical solutions that do leverage that functionality. If you do use full text search and you have heavy full text query and update traffic, blocking may occur in the system. Trace flag 7646 can be enabled to help reduce contention on the internal full text tables that cause this type of blocking.
This trace flag is briefly mentioned in the following article.
SQL Server 2008 Full-Text Search: Internals and Enhancements:
This trace flag applies to SQL Server 2008 and 2008 R2 only.
Trace flag 4136 can significantly improve the performance, or your Dynamics AX implementation if the composition of your AX data is prone to parameter sniffing issues.
When trace flag 4136 is enabled, the default "parameter sniffing" behavior is changed so that a more average estimate of selectivity (based on density statistics) is used by the optimizer instead of the default parameter-specific estimates (based on the histogram).
The Dynamics AX Performance Team Blog has a good explanation of how this trace flag works, and provides some common scenarios where it might improve performance:
Below is a link to a post from the SQL Server Optimization Team. They do nice job of explaining parameter sniffing, how it helps performance, and when it may cause issues.
Be sure to performance test with this trace flag enabled before using it in your production environment.
Trace Flag KB Article: http://support.microsoft.com/kb/980653/
This trace flag is available in the following versions of SQL Server:
Trace flag 4199 is a trace flag that activates query optimizer fixes from a number of different hotfixes. The SQL Server query processor team has policy that any hotfix that could potentially affect the execution plan of a query must be controlled by a trace flag. Because of this policy, even if you are running with the latest hotfix or cumulative update installed, you are not necessarily running SQL Server with all the latest query processor fixes enabled. Some of the fixes enabled by trace flag 4199 directly address issues that affect Dynamics AX performance.
For a list of optimizer fixes that were previously enabled with individual trace flags but are now controlled by trace flag 4199, go here http://support.microsoft.com/kb/974006. Many of these optimizer fixes may help improve Dynamics AX performance. Two that stand out are listed below.
In addition to the older optimizer fixes referenced above that were rolled into a single trace flag (4199), there is at least one more optimizer fix that trace flag 4199 enables that can substantially improve performance for certain versions of Dynamics AX 2009. If you're running Dynamics AX 2009 with a kernel version of 5.0.1500.3260 or later, you should definitely have this trace flag enabled. There are some specific queries generated by that version or newer of the AX kernel that benefit from a more efficient execution plan when 4199 is enabled. Dynamics AX 2012 will likely have similar scenarios when it is released, so this is a good trace flag to have enabled for that version as well. The KB article associated with this optimizer fix can be found here http://support.microsoft.com/kb/2260502.
Trace flag 4199 was implemented in the following versions of SQL Server:
However, in order to get the optimizer fix for the specific issue that occurs in Dynamics AX 2009 kernel version 5.0.1500.3260 and later, you will need one of the following versions.
Back in November 2010 I talked about four SQL Server trace flags that I found to be helpful in improving