What are trace flags?

2

September 11, 2014 by Kenneth Fisher

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

TraceFlag
 

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.

2 thoughts on “What are trace flags?

  1. […] it’s own it will list out all documented DBCC commands. However you can also add in trace flag 2588 to get the undocumented ones as […]

  2. […] what is the fix? Trace flag 3042 changes the above behaviour. Per the trace flag […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,655 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: