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



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.

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 Jason Brimhall on 30 November 2010

Thanks Grant.  That helps to shed some light.

Leave a Comment

Please register or log in to leave a comment.