Nov 30 2010

Regressions

http://www.flickr.com/photos/danar/223598560/sizes/s/in/photostream/

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.

6 Comments

  • By Shannon Lowder, November 30, 2010 @ 10:09 am

    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!

  • By Brian Garraty, November 30, 2010 @ 10:10 am

    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.

  • By Grant Fritchey, November 30, 2010 @ 10:20 am

    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.

  • By SQLSharma, December 1, 2010 @ 12:42 pm

    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.

Other Links to this Post

  1. Execution Plan Stability | Home Of The Scary DBA — March 7, 2011 @ 8:01 am

  2. SQL Server 2014 and the New Cardinality Estimator | Home Of The Scary DBA — February 12, 2014 @ 10:25 am

RSS feed for comments on this post. TrackBack URI

Leave a comment