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

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

Share

3 Responses

  1. 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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?