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

Did You Know? Lost Without a Trace

This is not a post about Jim Gray, much as I wish I had some news to share.

It's about one of the reasons you should be really careful about using undocumented trace flags. I always try to warn people, whether I'm writing or speaking, about the dangers inherent when using any undocumented feature. Undocumented means unsupported! Microsoft retains the right to change the behavior of any undocumented feature, or to remove it completely from the product at any time, with no warning. 

One of the flags I used to talk about in the SQL Server 2000 version of my SQL Server Internals class  is a flag that will disable all index hints. Why might you want this traceflag?

Although hints are documented, they still should be used with caution, much like traceflags. In general, hints should be used only as a last resort, when nothing else you can do will get a query to perform satisfactorily. Index hints allow you to specify which index (or indexes) SQL Server should use when accessing a particular table in a particular query. Appropriate use of indexes depends heavily on the distribution of your data, which the optimizer inspects via the index statistics when deciding which index to use. If you use an index hint, you are telling the optimizer not to inspect the statistics, that you know better than the optimizer, and that you will tell it which index to use. You might end up with better performance than the optimizer could come up with on its own, for today. Tomorrow, or next week, your data may change, so that the hint to use a particular index is no longer the best option.

I usually recommend that if people use hints in their code, they retest that code periodically to make sure that the hint is still giving better performance than not using the hint. But how do you do that? One way is to actually change your code to remove the hints and see if your queries now run faster. But if you find out that the hints are still needed, you have to go change your code again to put the hints back. As an alternative, I used to mention traceflag 8602, which will disable all index hints. However, that was for SQL Server 2000. When I first upgraded to SQL Server 2005, and started researching hints for my books and my course, it looked like traceflag 8602 no longer worked. Such is the nature of trace flags, so I stopped talking about it.

In fact, I came up with an even better solution for SQL Server 2005. One of the problems with the traceflag was that it disabled ALL index hints, and made it hard to tell if some hints on some queries were still good and others weren't. So I realized that the new SQL 2005 feature of Plan Guides could be used instead. You can create a plan guide to force the use of a hint for a particular query, and then disable the plan guide to revalidate the hint. If the performance is worse after disabling the plan guide, you could just re-enable it, and this allows you to test hints one query at a time.

I already knew that there had been some changes to traceflags in SQL 2005. Although traceflags always could be set either locally or globally, in older versions it was hard to tell the difference. In SQL 2005,  the command DBCC TRACESTATUS() now tells us whether a traceflag was set locally (just for the session) or globally (for all sessions). It turns out that all my testing was done by setting the traceflag locally, and in SQL Server 2005, this traceflag only works when set GLOBALLY! Now of course, if you really were validating hints on your system, you would set the traceflag globally, but my quick test just to see if the hint still worked in SQL 2005 set the hint locally. And then I concluded that the traceflag had stopped working completely.  But I was wrong...

You can set traceflag 8602 globally in a couple of different ways. One is to use a second parameter of -1:

DBCC TRACEON(8602, -1)

Now when you run the following, you should see that 8602 is set globally:

DBCC TRACESTATUS(-1)

(The -1 for this command means to show the status of all enabled traceflags.)

You can also set a traceflag globally through the SQL Server Configuration Manager. Right click your SQL Server service and choose Properties. Then go to the Advanced tab. There will be a text box for startup parameters, and you can add a semicolon to the end of what's already there, and then add -T8602. Do not leave any spaces between the semicolon and the dash. You'll then have to restart your SQL Server service.

But the normal caveats and warnings still apply. Although I was wrong, and 8602 does still work (when set globally), there is no guarantee that it will continue to work in the next version, or even in the next service pack. Plan Guides are a supported feature, and they are not going away any time soon, so take a look at them as an alternative to controlling hints with traceflags.

Have fun!

~Kalen

Published Tuesday, February 26, 2008 4:09 PM by Kalen Delaney

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

 

Jeff Moden said:

Heh... I got news for you... Microsoft has the right to change or remove even documented features.  Look what they did to permissions on sp_MakeWebTask, a fully documented feature, between sp3A and sp4 of SQL Server 2000.  Sure, there's a bit more of a risk to using undocument features... but using only documented features does NOT reduce the risk of a change that will break code or maybe even have the feature go away.

March 1, 2008 2:02 PM
 

Kalen Delaney said:

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

June 3, 2013 12:32 PM

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