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 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