One of the most important take-aways from David Dewitt’s presentation at the PASS Summit was the level of fear within the Query Processing team at Microsoft caused by regressions. If you missed Dr. Dewitt’s presentation, I tried to capture as much of it as I could here, and it will be available within the DVDs from PASS.
Regression is when something moves backwards to a less perfect state. When talking about the optimizer in SQL Server, a regression is when you see a query that used to run fast in SQL Server 2000 or 2005 and suddenly after upgrading to 2005 or 2008, the exact same query now generates a different execution plan and runs slowly. Now do you know why the Query Processing team fears these things? Yeah, you’re angry. You just went through the process of upgrading, with the expectation that everything would get better, not worse. Instead, here you are with your previously functional query and it’s a steaming pile. Do you think a substantial percentage of you call Microsoft and vent?
Why does this occur? Because building a query optimizer that takes in the hundreds and thousands and tens of thousands of possible plans and finds a plan that is good enough in under 50ms is really, really difficult. Almost any time they touch the optimizer it must be an experiment in terror. Yet, they continue to work to try to come up with ways that the optimizer runs faster. They continue to work to incorporate all the new T-SQL functionality that gets introduced with each new release into the optimizer. If you’re the unlucky person whose query gets swatted because of a regression, you might not care, but you ought to understand.
For what it’s worth, more often than not, when I’ve seen a regression occur, it has usually been in a query that shouldn’t have worked fast in 2000. Instead, people got lucky and found a small hole in the optimizer that actually let bad or questionable code not only run, but run well. Most of the time, but not all the time, examining the query and attempting to rewrite it in a more optimal fashion fixes the issue.
If you do think you’ve hit a regression, before you start lifting scalps at Microsoft, take a peek again at the query. Would you say it’s been written in as optimal a fashion as possible? If not, try tuning it. If so, I’d start with reporting the issue on Connect instead of a scalping expedition through the halls of Redmond.
I’ve been asked about this before. I’d never really considered the possibility they’d found some flaw in the optimizer. I’d always just assumed it could be written better. I’ve always done the same thing I always do, look at the query plan, look for bad joins, bad logic, missing indexes, etc.
I’ll have to check out the video on this so I can bring it up as a possibility. Thanks for the heads up!
I also heard the fear of regressions cited as holding back progress several times at the PASS Summit including once or twice in Conor Cunningham’s session on Update statements.
I don’t know that I’d call it a flaw. In fact, the one’s I’ve seen personally, it’s usually some horrific flaw that existed in 2000 that not only allowed bad TSQL bug enabled bad TSQL to run well. But, regardless, if you’ve got a query that is suddenly slow, however bad that query might be, in your eyes the optimizer is now broke.
Good analogy re Hannah Dustin,although there are a different ‘kind’ of Indians roaming the halls at Redmond. 🙂
I know…Please:no hate-mail guys.
[…] 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 […]
[…] Not any more. There’s a possibility for really complex, edge-case queries, that you may run into a regression from […]