SQL Server Trace Flags

With the release of SQL Server 2016 there will be a wave of upgrades happening over the next twelve months. I’ve written before about upgrading SQL Server and today I wanted to talk about something special to consider when upgrading.

Trace flags.

Trace flags change the default behavior for queries inside of SQL Server at the server, session, or query level. I’m not a fan of setting trace flags for the entire instance unless there is a good reason to do so. I prefer more granular settings, such as the session or query level. And I only want to use trace flags when I know exactly what it will do. (With SQL 2016 I can also configure a database scoped option to mimic the behavior of trace flags such as for cardinality estimates, but that is a post for a different day).

You can find a partial list of documented trace flags available at MSDN. I say partial because you can find definitions on TechNet or outside of MSDN. Check with Microsoft Support about whether a trace flag is supported before using it.

How To Set a Trace Flag in SQL Server

Setting a trace flag is easy enough. One option is to enable the trace flag when the instance is started. This is done by adding a startup parameter inside SQL Server Configuration Manager (SSCM):

You can then verify the setting after the restart by looking in the error log:

Or, you can enable the trace flag for your connection using the DBCC TRACEON statement:

DBCC TRACEON (1222)
GO

Or, if you want to set the trace flag globally for all connections, you can use this syntax:

DBCC TRACEON (1222, -1)
GO

Another option is to use the QUERYTRACEON hint. This is the technique I use for my cardinality talks, where I toggle the behavior of the cardinality estimator using query trace flags. For example, if I am running a SQL Server 2016 instance and want to revert to the legacy cardinality estimator I would use the following syntax at the end of the query:

OPTION (QUERYTRACEON 9481)

Take note that this query hint option is only supported for a handful of trace flags.

SQL Server Upgrades and Trace Flags

When it comes to SQL Server upgrades I find the use of trace flags to be overlooked. Trace flags get set and remain in place for long periods of time, and in that time people forget that the trace flags are running. As a result, they take a database backup from an older server, restore it to the newer instance, and then scratch their head when something has changed.

In my experience dozens of configuration options get made to database servers over time. Despite all best efforts at documenting each and every option used it seems inevitable that something gets missed.

This is why I put together checklists for upgrading SQL Server. With so many moving parts it is difficult to remember everything you need to check. If you want a wonderful free tool to capture details of your SQL instances (including trace flags and OS details) you should check out SQL Power Doc over on Codeplex.

How To Find What SQL Server Trace Flags Are Running

In addition to what is listed above (examine the properties in SSCM or read the SQL Server errorlog) other ways exist for you to find what trace flags are running.

The easiest way to find SQL Server trace flags is to run the DBCC TRACESTATUS command:

DBCC TRACESTATUS WITH NO_INFOMSGS 
GO

This returns a result set that shows you the trace flag, the status, and if the flag is global or for the current session only.

Another way to get these details would be to query the registry, but that always seems messy to me. You could use Powershell and the EnumActiveGlobalTraceFlags() function to also capture the currently running trace flags. Bear in mind that you only get back information on trace flags configured to run globally.

Nobody likes surprises when it comes to upgrading or migrating SQL Server instances. Trace flags get set and forgotten. I’m hoping that this post will help people to remember.