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

Published On: 2013-05-22By:

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.


Contact the Author | Contact DCAC

3 responses to “The Optimizer Isn’t As Smart As You Might Want It To Be”

  1. ziyaziya says:

    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.

  2. ryanjadams says:

    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.

  3. […] 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. […]


Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link