THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: Document more trace flags?

 

It’s been over 5 years since I last blogged about trace flags, so it seems a post on the topic is in order.

If a trace flag is undocumented, it means it is unsupported and not guaranteed. It can go away at any time, even from one service pack to the next. If a trace flag doesn’t work the way the person who told you about said it should work, there is no help for that. It is undocumented.

Trace flags change SQL Server’s behavior. They are toggles, that can be turned on or off. When they are on, they force SQL Server to do things differently. Sometimes just a little bit differently, sometimes a LOT differently. Some are very harmless, such as 3604 which allows SQL Server to return output from certain undocumented DBCC commands to return output to the client. You need to use this flag if you’re going to use the DBCC PAGE command, which I talk about a lot in my writings and in my SQL Server Internals classes.

However, there are other trace flags that change SQL Server behavior a LOT. The SQL Server developers at Microsoft create trace flags for lots of reasons, usually to force some sort of non-standard behavior. This might be so the developers can test the feature during development, or so that the support engineers can turn a feature off – either to confirm that the feature was causing a problem. These are usually not changes that you would want to make to your own SQL Server in a normal environment.

But with so many undocumented trace flags, the question frequently arises “Why aren’t MORE trace flags documented?”

Here are some of the reasons:

-- Once a trace flag is official publicly documented, people will start to use it. And people will also start to misuse it. Microsoft needs to consider whether dealing with the misuse will be worse than the problems due to the lack of that particular trace flag.

-- Before a trace flag is documented, it must be thoroughly tested. FULLY. Across all SKUs, all security environments, all performance conditions. And that is not easy. A trace flag added for one particular situation or one rare bug is usually not tested to the full extent that documented features are. So it will stay undocumented.

-- Once a trace flag is added, it must be supported, into the future. And can’t be removed without officially deprecating it, at least one full version in advance. Some trace flags are known to have a limited potential usefulness, documenting them will force them to be maintained even when they’re no longer useful.

-- Because most of the undocumented trace flags are created for a limited set of circumstances, they are not tested in conjunction with other trace flags. In order to be fully tested, each trace flag would have to be tested in combination with every other trace flag. And if a particular combination of trace flags was not pretty, which of the trace flags involved should be left undocumented? It would be really hard to tell, even after spending all the time on all the testing.

The bar for Microsoft actually documenting a trace flag is therefore quite high. And sometimes if a feature introduced by trace flag is really needed, it will just become part of the product, or added using a different interface, such as a configuration option or database property.

But Microsoft does document new trace flags. If you look at the history of Books Online to see the number of documented trace flags, you can see the increase:

--  SQL Server 2005:               10 trace flags

-- SQL Server 2008:                13 trace flags

-- SQL Server 2008 R2:           15 trace flags

-- SQL Server 2012:                19 trace flags

 

So if you read about an undocumented trace flag, and you think it might be useful for you, be very careful when testing it. And if your careful testing shows it IS useful, let Microsoft know.

But if your testing shows it isn’t useful, or is actually harmful, you don’t need to let Microsoft know. They probably already know. After all, it’s undocumented.

 

~Kalen

Published Monday, June 03, 2013 9:33 AM by Kalen Delaney
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement