Database Administration flags-of-the-world-with-names-capital

Published on December 13th, 2014 | by Warner Chaves

4

The Most Important Trace Flags for SQL Server

Here at SQLTurbo we’re aiming for the goal of being a reliable and up to date reference for all SQL DBA’s out there. I’ve often had issues finding a good reference and practical details on many of the trace flags that you can use with the different versions of SQL Server (some are documented, some undocumented but with many resources, some neither!). It is my objective to turn this post into a live document where I will be documenting the trace flags that I personally consider the most important to use with SQL Server. As always, I will provide my own practical advice on when to use them and any other observations or notes as necessary.

Note that this is NOT an EXHAUSTIVE list of ALL SQL Server flags, for that the TECHNET Wiki page on Trace Flags is a good reference, this page is for the ones I personally consider the most important ones that every DBA should know and most importantly, ones I have field tested myself.

Remember global level trace flags can be activated with DBCC TRACEON or with the -t startup parameter. Session level trace flags can be activated with DBCC TRACEON. Query level trace flags can also be activated with the QUERYTRACEON hint.

Let’s dive in, here are the most important trace flags for SQL Server:

Trace Flag 3604 and 3605
What does it do?
These two flags output trace activity from SQL Server into the current session output (3604) or into the Errorlog (3605).
When should you use it?
The use of these depends on what you’re trying to do and if it’s necessary or not. For example, the command DBCC PAGE will require one or the other to be able to see the output.

Trace Flag 3226
What does it do?
Eliminates backup success messages from going to the Errorlog.
When should you use it?
This trace flag is a must-flag. There’s nothing more cumbersome than doing some quick troubleshooting browsing the Errorlog and you have to scroll through hundreds(thousands!) of lines telling you your backups are working OK. OK, tell me when they’re NOT OK and let’s keep the Errorlog clean. Anyway, huge fan of this trace flag, definitely recommended everywhere.

Trace Flag 1117
What does it do?
Changes the way autogrowth behaves so ALL FILES in a filegroup grow when autogrowth is triggered.
When should you use it?
This trace flag is a must-flag. SQL Server uses a proportional fill algorithm so if all files are always kept with sizes in sync then the SQL engine will direct the same amount of IO to each one. Beware that it is an instance level config so if you activate it, it will apply to ALL your databases. Make sure you have instant file initialization setup so the autogrowth events happen instantly.

Trace Flag 1118
What does it do?
Eliminates the allocation of mixed extents on the instance and instead will do uniform extents for any new object.
When should you use it?
This trace flag is a must-flag. It is commonly associated with tempdb contention, however, if you turn it on, it will change the behavior on all user databases. Usually user databases don’t create and destroy tables all the time like TEMPDB so the effect doesn’t have as much of a big impact there. In more recent versions of SQL Server (2008+) optimizations have been made to tempdb that help with the allocation contention (like the temp table cache) but I still recommend to turn this ON o all of your SQL Servers.

Trace Flag 610
What does it do?
Enables SQL Server to use minimal logging when inserting into a btree.
When should you use it?
First of all, the database needs to be in Simple or Bulk-Logged recovery mode to do any minimal logging. In this case, the amount written to the log can be smaller than fully logged, however the reserved space inside the log file might be bigger. In some cases the decrease in log writing will also translate into better performance of the data load. In any case, definitely one to test for potential benefits before moving to production.

Trace Flag 2371
What does it do?
Changes the auto update stats threshold from 500+(20%*SizeOfTable) to a dynamic range where the % needed to trigger auto update stats gets smaller as the table gets larger.
When should you use it?
When you have large tables and you have detected issues with estimation related to out-of-date statistics. The catch here is that the auto update stats will be triggered more often so you’ll be making a tradeoff between SQL doing more work to keep your stats up to date and the quality of the stats.

Trace Flag 4199
What does it do?
It activates multiple different query optimizer adjustments and fixes that can possibly generate a different query plan than the one you’re getting without the flag.
When should you use it?
I recommend this flag be used on a case-by-case basis by using QUERYTRACEON with a particular query. I’ve seen cases where using the flag generated better estimates and better plans for particularly complex queries where the stats were up to date but the complexity of the predicates made it hard for SQL to come up with a good estimate. If you do get a different plan by using this flag then you definitely need to TEST and make sure that it’s better before you decide to put it on a live environment.

Trace Flag 2861
What does it do?
It makes SQL Server cache zero cost – trivial plans.
When should you use it?
I’m mentioning this trace flag for reference purposes, I don’t have a proper use case for it. Some 3rd party tools might enable this flag to allow their tools to collect the plans generated by these zero cost queries. If you need to collect those plans though, I recommend you simply setup an Extended Events session, capture the plans you want and turn the session off instead of having SQL use memory to cache them.

Trace Flag 1222 and 1204
What does it do?
These two flags print deadlock information to the Errorlog. The difference is in the format of the information printed. The exact differences between each one are documented right here.
When should you use it?
I’m not a big fan of capturing deadlock information through the Errorlog to be honest. The reason for this is that it clogs up the Errorlog with the deadlock information and it’s not the easiest format to go back to query and refer to later. So, my recommendation is that instead of using these flags, you can either look for deadlocks in the system_health Extended Events session, create a custom Extended Events session for your own tracking or use Profiler if you’re still on SQL 2000 or 2005.

Trace Flag 3213
What does it do?
This flag outputs internal configuration of the buffers and memory size that SQL will use during a backup and restore operation. You will need to combine with 3604 or 3605 to see the information.
When should you use it?
Very useful when you’re trying to do some tuning to get faster backup or restore times. The size of the buffer is configurable and different size buffers can have a big impact in the performance of your backup and restore operations. You could use this trace flag to see how your system is configured, play around with the buffer sizes and get to an optimal configuration for your specific system.

Trace Flag 9481
What does it do?
If you’re running a database on SQL 2014 in compatibility level 120, this trace flag lets you use the cardinality estimator of the previous version.
When should you use it?
Useful if you experience plan regression where some queries are getting worse estimates when using the new cardinality estimator. This way you can leave most of your workload on the latest compatibility mode and only target specific queries if/when they experience this issue.

Trace Flag 2313
What does it do?
If you’re running a database on SQL 2014 in compatibility level 110 (SQL 2012), this trace flag lets you use the new cardinality estimator that comes in 2014.
When should you use it?
This trace is useful if you need to leave a database in compatibility mode 110 (because of application compatibility for example) but still want to take advantage of the new cardinality estimator. You could run tests to find what queries run better with the new estimator and then target them specifically using QUERYTRACEON.

As I mentioned before, bookmark this post because I will be continually updating it with references to more flags as I run into them or discover new ones.

Tags: , ,


About the Author

SQL Server Certified Master and MVP. DBA for MS technologies for over 10 years.



4 Responses to The Most Important Trace Flags for SQL Server

  1. smithd938@gmail.com' Smithd789 says:

    Wow that was odd. I just wrote an extremely long comment but after I clicked submit my comment didn’t show up. Grrrr well I’m not writing all that over again. Anyway, just wanted to say superb blog!

    • Warner Chaves says:

      Sorry for the tech glitch but we’re glad you found the content useful! I’m updating it every now and then when I run into some good trace flag I haven’t mentioned.

  2. smithd293@gmail.com' Smithd295 says:

    Very informative blog post.Really thank you! Keep writing.

  3. magstarscott@outlook.com' Maggie says:

    A very informative piece :) thanks a lot for the help in one of my projects on Trace flags! :D

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Back to Top ↑
  • Sign Up For Our Newsletter

    Stay up to date on the latest from SQL Server products and Features.

    Sign Up Now

    Sign up for mailing list and receive new posts directly to your inbox.

    We promise never to share or sell any of your personal information.

  • Recent Posts

  • Categories

  • Archives

  • Tags

  • Topics

  • Subscribe to our RSS Feed


    sqlturbo.com A SQL Server Web Blog
    Canada, Ontario, Ottawa.
    http://sqlturbo.com

    The Authors