axinthefield: SQL Server Trace Flags for Dynamics AX

[Blog bot]

Источник: http://blogs.msdn.com/b/axinthefield...namics-ax.aspx
==============

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 which is 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.

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/l...#_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 such 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/archi...n-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...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 was made available to improve the performance of certain types of queries that surface in Dynamics AX when you implement the cross company optimizations detailed in the following posts on the Dynamics AX Performance Team Blog.

• http://blogs.msdn.com/b/axperf/archi...ny-part-1.aspx
• http://blogs.msdn.com/b/axperf/archi...b-article.aspx

At the time of this post, this trace flag was only available in the following versions of SQL Server:

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

Trace Flag KB Article: http://support.microsoft.com/kb/2260502

Unfortunately this KB article doesn't really explain the scenario that applies to Dynamics AX.








Источник: http://blogs.msdn.com/b/axinthefield...namics-ax.aspx