Trace flags in SQL Server

Category: Performance
Item:  Trace flags

What is a Trace Flag in the SQL server?

SQL Server trace flags are configuration handles that can enable or disable a specific SQL Server characteristic or to change a particular SQL Server behavior.

Trace flags turn on/off certain hidden SQL Server features that have no User Interface components.

Trace flags may also be recommended by Microsoft Support to address behavior that is negatively impacting a specific workload.

It is possible to make SQL perform better by enabling or disabling some of them.

How to check for enabled trace flags?

The command below will show you a table with all active traces.

DBCC TRACESTATUS


Figure 1- DBCC TRACESTATUS outuput.

How do I enable trace flags in SQL Server?

There are two ways to enable or disable them:

Using TSQL, with the DBCC TRACE command, you have the option to set the trace flag at the session or global level.

Usually, you enable the trace flag to the entire instance (global level). For testing purposes, you may just let it at the session-level.

For example, to enable trace flag 1118 globally, you would run:

DBCC TRACEON (1118, -1);

To disable it, run:

DBCC TRACEOFF (1118, -1);

The use of parameter -1 turns on the flag globally.

Note: The advantage of using DBCC TRACEON and DBCC TRACEOFF is that you don’t have to restart the instance to use the trace flag.   The drawback, it will not persist through a restart.

At a minimum consider using these:

  • TF3226 – Which stops successful DB backup from SQL ErrorLog.
  • TF1221 – Capture deadlock info to SQL ErrorLog.
  • TF2389 – Manage increasing key statistics better.
  • TF2390 – Manage unknown key statistics better.
  • TF2371 – Change how AUTO_UPDATE_STATISTICS starts for large tables. Good for VLDBs. (for versions before SQL Server 2016).
  • TF1118 – Provides benefits for customers that make heavy use of the tempDB (for versions before SQL Server 2016).
  • TF3023 – Validate checksums during a backup, and generate a checksum for the entire backup. (for versions before SQL Server 2014)

More information

Microsoft – DBCC TRACEON (Trace Flags)

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

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

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.