SQL Server with Mr. Denny

May 22 2013   2:00PM GMT

The Optimizer Isn’t As Smart As You Might Want It To Be

Denny Cherry Denny Cherry Profile: Denny Cherry

A little while back I got one of those phone calls.  You know the one, the lovely 6am phone call about random performance problems.  There were two problems that night.  One which I’ll talk about later in another post, the second one which I want to talk about today.

The query that was having problems is a dynamically generated query which comes from a stored procedure.  The basic query which was being run looked a lot like this.

SELECT /*A bunch of columns*/
FROM answer a
JOIN session s ON a.SessionID = s.SessionID
WHERE a.SessionID IN (4857385,5269932,5682479,6095026)

Most of the time that this query was being run everything was just fine, however there were a some times when it was timing out. Looking into the execution plan for a normal run of the query everything looked just fine. However when this was being run sometimes there were 1.2M rows being pulled from the session table even though there were 4 specific IDs being passed in.

Looking at the properties of the index scan which was being performed against the session table I could see that the SQL Server turned the query to WHERE s.SessionID >= 4857385 AND s.SessionID <= 6095026. This was a problem as for some of these queries as like with this query there were 1.2M rows being returned from the session table instead of the 4 rows that should have been returned.

The fix in this case was to simply change there where clause from “WHERE a.SessionID” to “WHERE s.SessionID”. Now I’m not sure why this worked from the internals point of view but I do know that it worked. The next time the stored procedure ran it run in milliseconds instead of timing out at 30 seconds.

In this case the server in question was SQL Server 2008 R2 (10.50.2796). This may or may not apply to other builds of SQL Server. I’m pretty sure this is going to be a your mileage may vary sort of thing.

This is officially the least amount of work that I’ve ever done tuning a query as I only made a single change to a single letter of the query.

Denny

3  Comments on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • ziyaziya
    I can see that you are are genuinely passionate about this! I am trying to build my own website and youve helped me with some great information.
    http://sqlservermasters.com/
    30 pointsBadges:
    report
  • RyanJAdams
    Assuming it was data typed as an INT I would take a guess that SQL saw that the numbers in the IN statement were ascending and decided to do a range scan.  I wonder if you could have also solved it by switching the numbers so they were "random" and not ascending or descending.  Might be an interesting test.
    10 pointsBadges:
    report
  • Denny Cherry
    [...] The Optimizer Isn’t As Smart As You Might Want It To Be - Denny Cherry (Blog|Twitter) shares the details of recent performance troubleshooting experience. [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: