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


Recent Posts

See All

Deadlock Trace Flags

Detecting and resolving deadlocks in SQL Server can be difficult. By default deadlock detection is not enabled. Deadlocks can cause data inconsistencies and performance problems. To detect deadlocks,