THE SQL Server Blog Spot on the Web

Welcome to - 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:


(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!


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



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

king said:



January 16, 2018 11:36 PM

aaaa said:

mt0417 mt0417

April 16, 2018 8:38 PM

rkcdoqx said:

valentino sale ,

moncler women coats"> ,

golden goose superstar"> ,

nike air huarache women ,

golden goose ,

air jordan 3 ,

adidas harden vol 2 ,

off white accessories"> ,

valentino slingbacks ,

nike free run men ,

nike vapormax women ,

golden goose starter"> ,

golden goose ,

nike air max 87 men ,

golden goose ball star"> ,

golden goose ball star"> ,

moncler women vests"> ,

nike zoom speed trainer 3 ,

adidas yeezy boost 750"> ,

air jordan 13 ,

golden goose superstar"> ,

golden goose ball star ,

nike lunar men ,

nike sb men ,

nike air force 1 women ,

golden goose tennis"> ,

supreme tshirt"> ,

supreme outlet ,

nike flyknit racer ,

supreme crewnecks"> ,

air jordan 6 ,

valentino sneakers"> ,

adidas nmd shoes ,

moncler outlet ,

air jordan womens ,

nike x fragment men ,

valentino boots ,

supreme clothing outlet ,

valentino shoes ,

balenciaga clutch bags"> ,

adidas yeezy boost infant"> ,

golden goose may"> ,

golden goose francy"> ,

valentino pumps"> ,

golden goose starter"> ,

supreme hoodies ,

golden goose sneakers ,

supreme t shirts ,

golden goose mid star"> ,

nike free 5.0 ,

balenciaga shoulder bags"> ,

golden goose v star"> ,

off white x nike"> ,

golden goose mid star ,

adidas dame 4 ,

supreme pant ,

adidas yeezy boost 750 ,

golden goose francy ,

supreme crewneck ,

golden goose 2.12"> ,

golden goose ,

moncler scarves hats ,

nike air max thea print men ,

golden goose starter"> ,

golden goose outlet ,

air jordan 1 ,

supreme jacket ,

golden goose v star ,

nike air max 87 women ,

golden goose francy"> ,

golden goose slide ,

air jordan spizike ,

nike roshe run men ,

golden goose ,

air jordan dmp ,

nike ,

nike flyknit air max men ,

valentino ballerina flats"> ,

moncler kids vests ,

golden goose superstar ,

air jordan cdp ,

nike air max 95 men ,

nike air zoom men ,

balenciaga sale ,

golden goose slide ,

air jordan ,

nike air huarache men ,

golden goose may ,

off white shoes"> ,

air jordan 10 ,

air jordan 6 rings ,

nike air max 90 men ,

nike free 4.0 ,

golden goose may ,

golden goose slide"> ,

nike air max tn men ,

adidas eqt support ,

golden goose 2.12 ,

golden goose slide"> ,

air jordan 7 ,

nike air max dynasty women ,

supreme scarf ,

valentino sandals"> ,

golden goose ,

moncler outlet online ,

world cup jersey ,

nike air max dynasty men ,

argentina world cup jerseys ,

golden goose v star ,

supreme clothing ,

golden goose super star ,

france world cup jerseys ,

adidas iniki runner boost ,

air jordan fusion ,

nike air force 1 men ,

nike air max excellerate men ,

golden goose ball star ,

nike free run women ,

golden goose slide ,

off white x adidas"> ,

golden goose francy ,

nike air max 95 women ,

nike flyknit air max women ,

golden goose mid star ,

moncler kids coats ,

valentino ballerinas ,

air jordan 12 ,

adidas crazy explosive ,

spain world cup jerseys ,

germany world cup jerseys ,

air jordan 4 ,

golden goose sale ,

valentino sandals ,

moncler men jackets ,

golden goose superstar"> ,

golden goose superstar ,

golden goose starter ,

golden goose sneakers ,

valentino outlet ,

supreme shirt ,

nike sb women ,

golden goose ball star"> ,

golden goose mid star"> ,

adidas yeezy boost 700 ,

air jordan 11 ,

air jordan 9 ,

nike air max 90 sneakerboot men ,

adidas yeezy boost 350"> ,

valentino pumps ,

golden goose francy ,

nike air zoom women ,

supreme outlet online ,

supreme bag ,

balenciaga ,

nike vapormax men ,

supreme sweatshirts ,

adidas harden vol 1 ,

air jordan 8 ,

golden goose starter ,

adidas pure boost dpr ,

nike x fragment women ,

air jordan 5 ,

balenciaga caps ,

yeezy boost sale ,

air jordan 2 ,

nike air max women ,

supreme shoes ,

brazil world cup jerseys ,

balenciaga crossbody bags ,

adidas ultra boost ,

nike roshe run women ,

golden goose may ,

golden goose mid star ,

nike air max 2017 women ,

supreme package deals ,

golden goose starter ,

golden goose may ,

balenciaga backpacks ,

nike lunar women ,

moncler women jackets ,

moncler kids jackets ,

valentino shoes ,

off white x vans"> ,

supreme jackets ,

adidas ,

supreme package deals ,

golden goose sale ,

supreme pants ,

adidas y3 pure boost ,

nike air max 90 women ,

golden goose ,

nike air max thea print women ,

golden goose ball star ,

golden goose v star ,

balenciaga tote bags ,

nike free 3.0 ,

golden goose woman ,

adidas nmd human race ,

golden goose sneakers ,

moncler men coats ,

adidas yeezy boost 350 ,

adidas yeezy boost ,

golden goose mid star ,

nike air max 2017 men ,

adidas yeezy boost 350 v2 ,

supreme long tshirts ,

nike air max 1 men ,

off white ,

portugal world cup jerseys ,

moncler men vests ,

valentino sneakers ,

world cup jerseys ,

valentino flats ,

golden goose may ,

nike basketball ,

valentino slides ,

nike air max 1 women ,

england world cup jerseys ,

golden goose man ,

supreme hoodie ,

golden goose francy ,

balenciaga city bags ,

golden goose shoes ,

nike air max men ,

golden goose v star ,

supreme shorts ,

golden goose slide ,

golden goose ,

golden goose v star ,

July 13, 2018 7:42 AM

dongdong8 said:



July 23, 2018 11:29 PM

cara meningkatkan nafsu makan said:

August 3, 2018 11:45 PM

obat bronkitis anak secara alami said:

August 24, 2018 7:17 PM

obat herbal kolesterol terbaik said:

August 24, 2018 11:06 PM

obat benjolan said:

September 4, 2018 12:40 AM

obat wasir said:

September 4, 2018 8:35 PM

obat katarak said:

September 17, 2018 1:57 AM

obat maag said:

September 18, 2018 7:38 PM

xiaojun said:

20180928 junda

September 27, 2018 10:46 PM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement