Dynamics AX in the Field

Microsoft Dynamics AX from the Premier Field Engineering team at Microsoft.

SQL Server Trace Flags for Dynamics AX

SQL Server Trace Flags for Dynamics AX

Rate This
  • Comments 3

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

Comments

1224

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:

http://blogs.msdn.com/b/axinthefield/archive/2011/06/25/sql-server-lock-escalation-and-blocking.aspx

More information about trace flag 1224 can be found in the following MSDN article: http://msdn.microsoft.com/en-us/library/ms188396.aspx

7646

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:

http://technet.microsoft.com/en-us/library/cc721269(SQL.100).aspx#_Toc202506243

This trace flag applies to SQL Server 2008 and 2008 R2 only.

4136

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:

http://blogs.msdn.com/b/axperf/archive/2010/05/07/important-sql-server-change-parameter-sniffing-and-plan-caching.aspx

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.

http://blogs.msdn.com/b/queryoptteam/archive/2006/03/31/565991.aspx

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:

  • SQL Server 2008 R2 Cumulative Update 2 and later
  • SQL Server 2008 Service Pack 2 (SP2) and later
  • SQL Server 2008 Service Pack 1 (SP1) Cumulative Update 7 and later
  • SQL Server 2005 Service Pack 3 (SP3) Cumulative Update 9 and later

4199

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:

  • SQL Server 2008 R2
  • SQL Server 2008 Service Pack 2 (SP2)
  • SQL Server 2008 Service Pack 1 (SP1) Cumulative Update 7
  • SQL Server 2008 Cumulative Update 7
  • SQL Server 2005 Service Pack 3 (SP3) Cumulative Update 6

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.

  • SQL Server 2008 R2 Cumulative Update 5 or later
  • SQL Server 2008 Service Pack 2 (SP2) Cumulative Update 1 or later
  • SQL Server 2008 Service Pack 1 (SP1) Cumulative Update 10 or later

 

 

  • Back in November 2010 I talked about four SQL Server trace flags that I found to be helpful in improving

  • Back in November 2010 I talked about four SQL Server trace flags that I found to be helpful in improving

  • Back in November 2010 I talked about four SQL Server trace flags that I found to be helpful in improving

Page 1 of 1 (3 items)