A Topical Collection of SQL Server Flags

[Edit 2016-04-27: Updated through SQL 2014 RTM CU13 and 2014 SP1 CU6, along with some flags first seen in posts from major bloggers.]

[Edit 2015-06-23: Updated through SQL 2014 RTM CU8 and 2014 SP1 CU1, along with some cleanup.]

[Edit 2015-01-21: A number of new flags have been added to the document due to finding new TFs in several PASS 2014 sessions and sweeping through the CU KB articles from SQL 2005 to the present. The document is still in need of much cleanup and reorganization, however.]

[Edit 2014-11-04: I had wanted the third release of this document to essentially be “complete” (only needing the ongoing maintenance of finding new flags and referencing KB articles/blog posts). However, I still have quite a bit more to do and there are enough new flags & updates that publishing an interim revision “only” 6 months after the last one seems worthwhile.]

[Edit 2014-04-27: The second release of this document features improved categorization, consistent formatting, a more formal introduction, and the inclusion of a large number of flags from Paul White (both his SQLBlog.com and SQLPerformance.com blogs), Ben Nevarez, Dima Piliugin, and KB articles.]

I hate forgetting things. I also love exploring the deepest corners of whatever I’m involved in, which currently includes SQL Server. These 2 personality quirks do NOT go together unless one has a good system for organizing notes, and so about a year ago I bit the bullet and finally started a system for organizing the technical content that I devour. One of my collections is a “Trace Flag Repository”, organized by topic (or “area of the Engine”, if you prefer).

Trace Flags are admittedly one of the geekier areas of SQL Server, especially as so many of them are undocumented by Microsoft and control obscure, little-understood areas of the product. I generally find value from perusing trace flag information in one of the following ways:

• Informational flags give me tools to “trace” a specific area of the product to a level of depth not covered by Profiler, or maybe even Extended Events.
• Obscure, behavioral trace flags often illustrate a design choice that Microsoft faced, and help me to understand where the default behavior of the product might run into problems.
• (Very rarely) I find a trace flag that actually promises to alleviate some annoyance I’ve encountered with the product, though is not yet documented by Microsoft (I’m thinking of TF 3226, suppressing BACKUP messages to the SQL Log, which was not documented until SQL 2008)

At any rate, whether I’m looking through trace flags from geeky interest or searching for something helpful, the existing repositories of undocumented trace flags online have been mostly large, unorganized lists. Thus, I decided to start putting my own together by starting with the SQL Server Central list and categorizing by areas of the DB engine (e.g. SQLOS, Disk IO, Query Optimizer, etc). I’ve since been adding flags that I discover in blogs, whitepapers, or even print, and will continue to incorporate flags as I stumble onto them in KB articles, blog posts, Connect items, and such.

My ultimate goal is to have an intuitive categorization of flags (as many as is possible/reasonable), each with relevant links to trustworthy whitepapers/blogs/etc. If you see flags that are not present in the document, or have links to informative content or useful application for a specific flag, please post below in the comments and I’ll incorporate into the document. The primary audience for the attached PDF was originally “me”, but I certainly want this to be helpful to the SQL Server community at large, and welcome your feedback!

Enjoy…

[Edit 2014-11-04: The immediately-previous version is retained so that readers can compare the additions/changes between the current & previous documents]

SQLCrossJoin_TraceFlagRepository_v6 (pdf)

SQLCrossJoin_TraceFlagRepository_v5 (pdf)

Advertisement