Trace flags are switches that adminstrators or developers can use to change the behavior of SQL Server. These are added by the development team, often to help debug or diagnose performance issues, according to BOL, but they are increasingly being used as feature flags to enable behavior that some customers may want, but Microsoft doesn't want to make the default.

This article will look at various ways that you can enable trace flag behavior in SQL Server. We have three ways of enabling trace flags: at startup, with the QUERYTRACEON query hint, and with the DBCC TRACEON/TRACEOFF commands. These different methods will determine whether the trace flag scope applies globally to all users, exists for a session, or is limited to a query.

If you want a complete list of all known trace flags, we have one here at SQLServerCentral.

Enable Trace Flags at Startup

To enable a trace flag on startup, or ensure the trace flag stays enabled through restarts, you will want to set a trace flag as a startup parameter for SQL Server. These are the command line options you would use if you were running sqlsrvr.exe from a command prompt. To do this is current versions of SQL Server, use the SQL Server Configuration Manager. Select your instance, right click and choose properties. In the dialog, there is a "Startup Parameters" tab. In here, you can add a new parameter.

I add the trace flag using a -T and the trace flag number (no spaces). This should be a capital T. A lowercase t is accepted by SQL Server, but this can also enable internal trace flags that Microsoft support engineers can use. I would avoid this as unexpected behavior can result.

Once I click "Add" this parameter will appear in the list and be enabled when I restart SQL Server. I can add more than one, using the same format, and when I add them, I can see them in my list.

Now when I restart the instance, I can see that each of these is enabled.

To disable a trace flag at startup, remove the parameter and restart the instance.

Enable Trace Flags with DBCC TRACEON/TRACEOFF

Trace Flags can be enabled with a DBCC command. The TRACEON option will turn trace flags on for either a session, or globally, depending on the parameters. The format for this command is:

DBCC TRACEON( n, [-1], [WITH_NO_INFOMSGS])

In this list, the n is the trace flag that you wish to enable. If you include the -1 parameter, the trace flag is turned on globally for the server. Otherwise, the traceflag exists only for this session. The last parameter suppresses any messages that are informational from the command. Any errors will still be reported back to the user.

As an example, I can enable trace flag 6530 (a spatial index hotfix) like this:

DBCC TRACEON( 6530)

DBCC TRACEON( 6530, -1)

In both of these cases, I don't get any message back from the instance other than that the DBCC execution completed.

If I wish to turn off the trace flag, I would use the corresponding DBCC TRACEOFF command, with the same format as TRACEON.

DBCC TRACEOFF( 6530, -1)

If I need to enable or disable multiple trace flags, I would need to execute DBCC TRACEON/TRACEOFF multiple times, once for each flag, as shown here:

Note that I get the information message for each of the commands I run.

Enable Trace Flags for a Query

I can use traceflags in a query with the QUERYTRACEON query hint. Not all trace flags are supported, but there is a kb article that lists the ones that are. This query hint is used like many others, inline in your code. 

As an example, if I want to use the old query optimizer's cardinality from SQL Server 2012, I can run a query with trace flag 9481, I can use it as a part of the OPTION clause in a query, like this: 

SELECT
    BusinessEntityID,
    COUNT(PersonType)
FROM Person.Person
WHERE EmailPromotion = 1
GROUP BY BusinessEntityID
OPTION (QUERYTRACEON 9481);

This allows me to change the behavior of the server for an individual query if I need to enforce this behavior.

Checking Which Trace Flags are Enabled

While the changing of trace flags is logged, searching the error log is cumbersome and difficult. A better way to check the status of trace flags is with DBCC TRACESTATUS. You can use this to check on all traceflags or particular ones. Here is the syntax:

DBCC TRACESTATUS [(n)]

where n is the trace flag number. That means I can check the status of a trace flag like this:

DBCC TRACESTATUS(6530)

I could also check the status of a couple flags like this:

DBCC TRACESTATUS(6530, 6531)

The output from this command shows a result set of whether the trace flag is enabled as on (either global or for a session), and then the individual statuses for my session and the instance globally. Also, the n is optional. I can check all trace flags enabled without including anything, like this:

Here I see my instance has 4 flags enabled, all for my session, one of which is also enabled globally for the instance.

If there are no global trace flags, and no session trace flags for my session, this command with no parameters will return nothing.

Showing the Effects of Trace Flags

Let's see this in action. Trace flag 3226 suppresses backup messages in the error log. This is useful as most instances have lots of backups running, and since the data is written to msdb, these messages clog the error log for SQL Server. Turning these off is something many DBAs do. First, let's run a backup.

Now we can see the message in the error log:

Next, I'll turn on the trace flag, which suppresses messages. I do that and get the acknowledgement.

I'll not run another backup, the same command, and when I look at the error log (overlayed below), there is no backup message. This is because the trace flag is active for my session.

Summary

Trace flags do change behavior and there is a risk in using these for your instance. Any changes should be tested thoroughly in your environment, with ongoing montioring to ensure they do not disrupt your server instance. Be aware that if you call Microsoft support, they may ask you to enable or disable trace flags to help solve any issues you may experience. There are a few trace flags that are useful, such as the three recommended by SQLskills.

Being aware of the ways in which you can enable ro disable trace flags will help ensure you can respond to the needs of your environment and your particular situation.