I searched through this BLOG to find any articles that list and explain all the SQL Server Trace Flags that we Field Engineers may have used or recommended for Dynamics AX. I found a few recommending and explaining 1 or 2 or the generic Best Practice Trace Flags but no summaries of all of them that we as field engineers may use in a Dynamics AX environment and explanation of what it does and the pros and cons of using it. So below is quick and dirty list of the Trace Flags we may recommend in a Risk Assessment Program as a Service or a Performance Review or any service for that matter and explanation of each and the pros and cons.
1117 - This Trace Flag forces SQL Server to Auto-Grow all data files at the same time for a database. In the Dynamics AX world the only database that normally have more than one data file is TempDB as this is a best practice due to way TempDB allocates new data pages. TempDB allocates pages differently than a user data database like Dynamics AX so this optimization DOES NOT apply to user databases. The pro's is that your data will be as evenly spread across all available data files as possible which is a good thing and the cons are that depending on your Auto-Grow settings for TempDB it could grow very quickly and you need to be aware of available drive space. This Trace Flag would be classified as a general Best Practice.
1118 - This Trace Flag changes the way SQL Server allocates data pages in TempDB. Normally pages are allocated one 8k page at a time when building new Temp objects. When TempDB is set up correctly this is normally sufficient, but there are cases where a customer may be utilizing TempDB more heavily than others and we can begin to see contention at the page allocation level in TempDB. In sys.dm_os_wait_stats DMV you might begin to see PAGELATCH_** creep up the waitstats this could point to contention in TempDB. Trace Flag 1118 can sometimes help alleviate this by having SQL Server allocation eight 8k pages at a time (a complete extent) instead of one decreasing the number of allocation requests as more pages are allocated at a time. The pros are that this can help alleviate TempDB page allocation the cons are that TempDB will grow faster than normal as it is allocating more data pages than normal. This Trace Flag is usually only recommended when we notice contention in TempDB.
1204 - This Trace Flag is used to capture the Deadlock Graph information to the SQL Server log when a deadlock is encountered. This trace flag is only used for diagnostics and when trouble shooting issues. We do not recommend enabling this Trace Flag unless advised by a Microsoft Support Engineer.
1224 - This Trace Flag changes the default locking behavior of SQL Server. SQL Server by default tries to conserve resources such as memory whenever possible where it thinks it makes sense and can help improve overall performance. In the case of record locking it does this by lock escalation. One Row lock takes as much memory as one Page lock or one Table lock so when a certain percentage of Rows or Pages in a Table are locked (Let's call the Lock Escalation Threshold) it will escalate the lock. Example if say 40% of the rows in a table are locked by process SQL Server by default could escalate this to a single table lock to free up lock memory resources, but this also reduces concurrency. This was absolutely necessary in the 32bit days as SQL Server regardless of the amount of memory only had 1.7 GB of memory for locking, sorting, optimizing and that could be eaten up quickly with 1000's of individual row locks. In the 64bit world SQL now has much more memory space available for locking, sorting, and optimizing (up to 40% of the memory available to SQL Server) so in high volume OLTP system like Dynamics AX we want optimal concurrency so we want to limit lock escalation whenever possible. Trace Flag 1224 disables lock escalation and will only escalate locks under extreme memory pressure. The pros are increased concurrency and the cons are increased use of lock memory, which on todays larger 64bit SQL Server are really not an issue and even with this flag enabled if SQL comes under extreme pressure it will go back to its default behavior. There is another Trace Flag 1211 that does essentially the same thing but has no safety valve and will never escalate locks regardless of memory. If both 1224 and 1211 are used 1211 will ALWAYS take precedence. We recommend 1224 as a Best Practice for Dynamics AX but we do not recommend 1211 due to its lack of a "safety" mechanism.
2371* - There is already a Blog article available about this Trace Flag that is much more detailed. This Trace Flag changes the behavior of when "Auto Update Statistics" is triggered for an object. With this Trace Flag is enabled the more rows an object has the smaller the percent change in data triggers an Automatic Statistics update. By default SQL Server triggers an Auto Statistics update when 20% of the data has changed when you are working with tables 10's of millions of rows which is common in Dynamics AX this just is not efficient and can cause bad execution plans and poor performance due to the "stale" nature of the statistics. With this Trace Flag enabled the threshold that triggers an Auto Stats update will drop as the number of increase keeping the statistics on that object much more accurate and execution plans more optimal. The pros are better performance and more accurate statistics and the cons are more statistics updates being trigged. This is considered an Best Practice Trace Flag for Dynamics AX.
4136** - This Trace Flag changes the way SQL Server compiles parameratized TSQL commands such as those that are sent by Dynamics AX. With this Trace Flag enabled SQL Server will ignore all the parameter values when compiling the execution plan for a TSQL commend and use a statistical average value for each parameter. The only reason we ever recommended this Flag was to help customers who had bad performance issues caused by parameter sniffing (There is a Blog explaining parameter sniffing) because it would ignore the parameter values supplied by AX and use OPTIMIZE FOR UNKNOW when compiling the query. Even those this sounds like a good idea in 9 out 10 cases where I had a customer implement it actually caused more performance issues than it solved. We almost NEVER recommend using this Trace Flag anymore and it is definitely not a Best Practice. Even if you are having parameter sniffing issues I would not recommend enabling this trace flag but to install the new updates for AX 2009, AX 2012, and AX 2012 R2 which allow the use of literals for DATAAREAID and in R2 PARTITION and DATAAREAID which will prevent parameter sniffing on these two values which is 90% of the problem. Below is the link to the Blog article describing Parameter Sniffing and the recently released fixes.
This Trace Flag was added in Cumulative Update 6 for SQL Server 2005 Service Pack 3 (SP3), Cumulative Update package 7 for SQL Server 2008, Cumulative Update 7 for SQL Server 2008 Service Pack 1 (SP1), and SQL Server 2008 R2. This one trace flag can be used to enable all the all non-default Query Processor Enhancements fixes that were previously made under many trace flags. In addition, all future query processor fixes will be controlled by using this trace flag.
7646**** - This Trace Flag is only relevant if you are using Full Text Indexing in Dynamics AX. The Trace Flag avoids blocking when using full text indexing. An issue we experienced that full text can be slow when there is a high number of updates to the index and is caused by blocking on the docidfilter internal table. If you are using Full Text Indexing with Dynamics AX we would recommend that you enable this Trace Flag to help prevent locking and deadlocking that can happen with Full Text Indexing. The pros are less blocking and deadlocking associated with Full Text indexing and I am not currently aware of any cons of enabling this Trace Flag. As stated this is only recommended if you are using SQL Server Full Text Indexing with Dynamics AX.
* This Trace Flag is only available on SQL Server 2008 R2 SP1 and all later versions. There was a small update to this Trace Flag in SQL Server 2008 R2 SP2 and SQL Server 2012 SP1 that makes it more precise.
** This Trace Flag is only available on SQL Server 2008 R2 Cumulative Update 2, SQL Server 2008 SP1 Cumulative Update 7 and SQL Server 2005 SP3 Cumulative Update 9 and later.
*** This Trace Flag is only available on Cumulative update package 6 for SQL Server 2005 Service Pack 3, Cumulative update package 7 for SQL Server 2008, and Cumulative update package 7 for SQL Server 2008 Service Pack 1 and all later versions
**** This is only valid and necessary for SQL Server versions earlier than SQL Server 2012.