- Adam Thurgar
Review your trace flags
Recently I was working on a SQL Server that had the following trace flags enabled.
1204 and 1222 for deadlocks.
1117 uniform tempdb allocation
1118 uniform extent allocation instead of mixed
661 disable ghost record removal process
834 use Microsoft Windows large-page allocations for the memory that is allocated for the buffer pool
835 Minimally logged inserts to indexed tables
8048 NUMA CPU based partitioning (soft NUMA)
There are also other trace flags that some consider 'best practice" like.
2371 so statistics are recomputed less rarely as tables grow larger
4199 to get new query optimizer hotfixes that produce different query plans
The question becomes what trace flags are really required and what have been carried over from past upgrades and are now redundant. SQL Server 2016 removes the requirement for a lot of these trace flags.
When you are upgrading you should review all trace flags and see if they are still needed, remembering that trace flags affect the whole SQL Server. I usually like to keep the use of trace flags to a minimum.