Trace Flags you probably ought to enable for SQL Server versions before 2016

If you are running a version of SQL Server earlier than SQL Server 2016 there are 4 trace flags that the majority of systems should have enabled. Trace Flag 4199 applies to SQL Server 2005 SP3 and later and Trace Flag 2371 applies to SQL Server 2008 R2 SP1 and later. As always you should test for untoward effects and if running vendor supplied software consult to check they are OK with these recommendations. Your letter might look like the below.

Hi

I am reaching out to get your input on the following important SQL Server trace flags which we would like to enable

as being generally beneficial to performance. Can you provide feedback as to whether you have a recommendation for your product

about any of these trace flags or have been notified by other customers of any negative effects.

thank you

Proposed SQL Server Trace Flags

Trace flags 1117, 1118, 2371, 4199

see

https://www.brentozar.com/blitz/trace-flags-enabled-globally/

1117

Causes files in a filegroup to grow at the same time. Most often used for tempdb, but affects all databases.

ensures that a database with multiple files in a filegroup all autogrow at the same time and thereby guarantees that database writes are distributed to all files equally as per the round robin algorithm.

1118

Removes the use of mixed extents. Most often used to help with tempdb contention.

what this means is that only rows from the same table can be allocated on any extent (8 x 8kb pages)

therefore every table has a minimum size of 64kb

advantage is that the SGAM semantics work differently (easier, simpler and more efficiently)

http://www.sqlskills.com/blogs/paul/misconceptions-around-tf-1118/

2371

SQL Server 2008 R2 SP1  & later

Lowers the threshold for automatic statistics updates to occur based on table size. Good for Very Large Databases (VLDBs).

First described on the SAP on SQL Server blog :-

https://blogs.msdn.microsoft.com/saponsqlserver/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371/

4199

SQL Server 2005 Service Pack 3 (SP3) Cumulative Update 6

SQL Server 2008 Service Pack 1 (SP1) Cumulative Update package 7

SQL Server 2008 R2 (RTM)

This is a bucket trace flag to enable optimizer fixes in SPs/CUs that are enabled by separate trace flags.

So without the trace flag you cannot take advantage of Query Optimiser improvements post SQL Server 2005 SP3

https://support.microsoft.com/en-us/kb/974006