Hannah Dustin, Upset about Regression

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.