Trace flags are one of those things that I’ve heard about more and more over the last five or six years. But only in the past year or so have I started to understand what they are and how to use them. I want to start out by saying that they are a fairly advanced tool and they should only be used with great care, after much testing and only if you are sure you need them.

Per BOL

Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior

For example trace flag 7806 enables the DAC in SQL Server Express. While trace flags 1211 and 1224 disable lock escalation in different ways. You can even turn the new SQL 2014 cardinality estimator on or off with trace flags 2312 and 9481.

In the BOL entry for trace flags there are currently 20 different flags listed. This is by no means a complete list. Kendra Little for example has recently posted about two more that she feels should be added to the list and Paul Randal uses trace flag 3604 when working with the undocumented DBCC IND and DBCC PAGE commands.

I want to say again that before you use a trace flag you should be 100% certain you know what it does and carefully test it before implementing it on a production box.

That being said:

Using a trace flag

There are 3 different ways you can use a trace flag.

• As a query hint

  From lowest scope to highest we start by using a trace flag for just a single query. Some trace flags can be enabled at a query level using the QUERYTRACEON query hint. The list of trace flags available at a single query level are listed in the previous link.

  -- Trace flag 9481 turns off the new
  -- cardinality estimator in 2014
  SELECT *
  FROM TableName
  OPTION(QUERYTRACEON 9481);

• Turn it on for this session

  A trace flag can also be turned on for any query in the current session using DBCC TRACEON

  -- Same Trace Flag as before. Trace flag 9481 turns 
  -- off the new cardinality estimator in 2014
  DBCC TRACEON(9481)

• Turn it on for the instance

  Trace flags can also be turned on for the entire instance. This can be done using the DBCC TRACEON command with the optional -1 parameter, or by using the -T trace# command-line startup option. BOL recommends the -T option. If you enable traceflags using the startup options then it will remain enabled after any instance restart.

  -- Trace flag 7806 will turn on the DAC in SQL Express
  DBCC TRACEON(7806, -1)

Turning off a trace flag

Having turned on a trace flag you will probably want to know how to turn it back off again. DBCC TRACEOFF will turn off a trace flag that has been previously turned on. It also has the -1 optional parameter to turn off the flag at a session or instance level. Remember that parameter. If you have a trace flag turned on at the instance level and forget to include it then the trace flag will remain on. The same is true if you include the -1 parameter when trying to turn off a trace flag at the session level.

-- Turn the trace flag 7806 back off
-- to disable the DAC in SQL Express
DBCC TRACEOFF(7806, -1)

Check the status of a trace flag

Once you have started turning trace flags on and off it’s probably a good idea to be able to check their status. You can do this with DBCC TRACESTATUS. DBCC TRACESTATUS is a little different in that it doesn’t have to be passed a trace flag number. If you do pass one in then you get back the status of that flag only. If you do not pass a flag number in then you get all active trace flags. There is a -1 parameter on this one also but it doesn’t seem to do anything.

DBCC TRACESTATUS

A couple of final points:

• All of the above commands can be passed multiple trace flags at once.
• If you plan on getting either the MCSA or MCSE for SQL Server you should at least be familiar with trace flags. They will probably show up at some level.
• Because this can never been said to much, one more reminder, these things are dangerous. Don’t use them unless you are on a test box or know what you are doing.

Filed under: Microsoft SQL Server, Settings, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL Tagged: code language, language sql, microsoft sql server, system functions, T-SQL, trace flags