SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Execution Plan Stability

Execution PlanI’ve talked before about one of the primary things that the Query Optimizer team at Microsoft tries to avoid, regressions. Basically, they want a plan that worked well in SQL Server 2005 to work well in SQL Server 2008 R2. What’s more, they want everything to work well between service packs, updates and cumulative updates. Ever wonder how they do it? Well, they cheat. OK, that’s mean and not entirely accurate, but it gets the idea across.

No, what they do is, identify when they have a breaking change, when they’ve got a special cumulative update or service pack that fixes some bad behavior, but that can cause plans to “break,” they wall it off. Note, in most cases, this “breakage” is actually a question of plans working correctly, but if it makes your query run slowly, you don’t care. They do this by putting it behind a bit of code (none that I 4could tell you about, not because I know and can’t tell you, but because I don’t have a clue) and putting a traceflag in front of it.

What’s this mean for you and me? It means, it’s possible, that you are not running the latest and (possibly) greatest version of the optimizer. By default, this traceflag is set off, and that’s a good thing. It means your plans are more stable, that the plan created prior to the latest CU is the same as the plan after the CU.

But, what if you want to try your system out on the latest & (potentially) greatest optimizer? In case all my weasel words have not been explicit enough, let me come right out and say this. Yes, you might have one of the problems that is fixed by the latest update…. and you might not. You may see improvements in your execution plans… or you might see them crash. In short, this is absolutely a moment where extensive and careful testing is called for. In fact, you might just be better off reading through the hotfix/CU/Service Pack documentation and determining if you even have a problem that might be fixed by this. If you don’t, you really might be better off not trying it. But hey, you know you want to see what happens, right?

Set TRACEFLAG 4199 to the On state when you start your server. That’s it. You’ll then be running the latest version of the optimizer, raw, with your chance to see if it’s the greatest, or abject evil. Microsoft has this well documented (with appropriate warnings and cautions) so you can try to understand for yourself whether or not this is applicable to your system.

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).


Posted by Glenn Berry on 7 March 2011

Good post, Grant.

Posted by Jason Brimhall on 8 March 2011

Thanks Grant.

Leave a Comment

Please register or log in to leave a comment.