Trace flags in SQL Server

April 3, 2015 at 15:56

Introduction

Trace flags in SQL Server allow you to change the behaviour of parts of SQL Server. Most information about trace flags can be found in several KB articles but usually this information does not provide too much detail.

Warning

Trace flags can lead to unexpected an undesired behaviour of SQL Server. When you think you need to use trace flags, setup a test environment and see if using the trace flag resolves your problem without creating new ones. Also look for more information about the trace flag on the internet, and not only look for the success stories but also for the unwanted side effects. When you decide to use a certain trace flag be sure to check if the usage of this trace flag is supported by Microsoft, If not you need to accept that if you run into problems because you use the trace flag, Microsoft will not assist you in fixing the problem. All warnings aside there are trace flags that are very useful and can help you resolve some strange and rare issues.

Types of trace flags

Trace flags can have different scopes and can be used for one or more scopes.. There are three scopes for using trace flags:

  • Global. When a trace flag is global the setting is used for all connections to the SQL Server instance.
  • Session. Trace flags enabled at session level are only active during a session. Other sessions are not aware of this trace flag being enabled.
  • Query. There are some trace flags that can be set for a certain query during a session.

Which trace flags are in use

To find out which trace flags are in use you can execute the following query

If there are trace flags enabled you will get a result that looks like this.
Enabled Trace flags

 

 

The column TraceFlag shows the trace flag. in this example 3226. the column Status tells us if the trace flag is enabled or not. the last two columns are indicators if the trace flag is set globally or only for the current session.

To find out if a specific trace flag is enabled you can also pass the trace flag as a parameter to dbcc tracestatus

Check Specific Trace flag

 

 

In this case trace flag 1118 is not enabled and the Status column returns 0. It is possible to specify multiple trace flags separated by commas.

Using trace flags

When you want to enable trace flags there are a few ways to do this depending on the trace flag and scope for which you want to use it.

Set as startup parameter

If you want to set a trace flag globally and want it to be available after a restart you need to add the trace flag as a startup parameter in de SQL Server Configuration Manager.  Select the service and select Properties

 

SSConfigMgr

On the properties screen select the tab Startup Parameters. In the field Specify a startup parameter type the trace flag you want to add as -Txxxx where xxxx represent the trace flag and press add

IMPORTANT: use an uppercase T, lowercase sets internal trace flags, which you don’t want to do.

AddTF as Startup

The trace flag will appear in the list of existing parameters.

AddTF as Startup2

When you click on OK or Apply, the following warning appears. Changing the startup parameters will not take effect until the service is restarted. If it is possible for you to restart the service at this point, you can go ahead and do so, otherwise you need to set the trace flag using DBCC TRACEON.

AddTFWarning

Setup trace flags for a session or globally through T-SQL

If you want to set a trace flag only for a current session, or globally until the service is restarted, you can do this by using the command dbcc traceon.

The syntax is not too difficult. To enable trace flag 3226 globally you need to execute:

the parameter -1 is used to specify that you want this trace flag to be global.

to enable a trace flag only for the current session you need to execute it without the -1

Disabling a trace flag, can be done using DBCC TRACEOFF

to disable trace flag 3226 type:

to disable the trace flag globally, type

NOTE: A trace flag set globally cannot be disabled for a session, but can only be disabled globally.

Set trace flag as query hint

The last method for setting a trace flag is for trace flags you only want to use for a specific query. Not all trace flags can be used like this, usually only trace flags that change the way queries are optimized can be set this way.

In the above query, the optimizer will use trace flag 2390, but only for this query, every other query in this session will run without it.