Execution Plan Stability

I’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.