The trace flags I enable by default

After installing a SQL Server Instance, a good DBA starts configuring the SQL Server Instance, or that’s what I hope 😉
But, I notice that a lot of people “forget” to configure trace flags.

Trace flags are often used for testing or diagnostic purposes.
However, there are a few interesting trace flags that could be implemented on most production environments.
Of course, don’t activate them blindly but think twice before you do so…

Trace Flag 1117    All the files in a filegroup will autogrow together by their specified autogrow increment
More information on SQL Server 2008 Trace Flag -T 1117 by Matthias Sessler

Trace Flag 1118    Forces uniform extent allocations instead of mixed page allocations
More information on SQL Server (2005 and 2008) Trace Flag 1118 (-T1118) Usage by Bob Dorr
More information on Misconceptions around TF 1118 by Paul Randal Twitter|Blog

Trace Flag 2371    To control when the query optimizer generates autostats on a table
More information on Controlling Autostat (AUTO_UPDATE_STATISTICS) behavior in SQL Server

Trace Flag 3226    Suppress the backup transaction log entries from the SQL Server Log
More information on The trace flags that are available in SQL Server

Trace Flag 4199    To control multiple query optimizer changes previously made under multiple trace flags
More information on Trace flag 4199 is added to control multiple query optimizer changes previously made under multiple trace flags

Trace Flag 2562 & 2549   To improve the speed of your DBCC CHECKDB.
More information on Improvements for the DBCC CHECKDB command may result in faster performance when you use the PHYSICAL_ONLY option
And on Faster DBCC CHECKDB Released in SQL 2008 R2 SP1 CU4 : Traceflag 2562 & 2549


If you want to enable one of those trace flags, have a look at How do I work with Trace Flags? by PankajAgarwal Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *