Blog

Working in the SQL Server emergency room, I see a lot of self-inflicted performance wounds. That query looks good, and it looks like it’s using an index – but why does it take so long? The query runs great half the time, but the rest of the time performance is cut off at the knees, and the only fix is to restart the server. Learn the top 3 T-SQL traumas I see in my practice:

 

↑ Back to top
  1. I have a question about OPTION (OPTIMIZE FOR xxxxx). In SQL Server 2008 you can add OPTION (OPTIMIZE FOR UNKNOWN) which will direct SQL Server to generate “Good Enough” plan based on the general statistics of the indexes available. In your states example it would not generate a plan for State = ‘Texas’ (BIG) or ‘Rhode Island’ (little) based on the first time the stored proc was called, but would generate one based on average values in the available indexes. It seems to me that in some cases it would be better than recompiling the query every time.
    Thanks!

  2. Hmmm … good video Brent …. however just looking at the screenshot from Stack Overflow about sargable statements … isn’t :

    Bad: Select … WHERE isNull(FullName,”) = ‘Ed Jones’
    Fixed: Select … WHERE ((FullName = ‘Ed Jones’) OR (FullName IS NULL))

    incorrect? surely they will give different results …. the first will bring back only Ed Jones whilst the second will bring back NULLs as well?

    • Pedro – thanks, glad you liked the video. Yep, those will give different results. Rather than commenting here, how about commenting on the StackOverflow page and contributing there where people will see it? Thanks again!

  3. Pingback: Something for the Weekend – SQL Server Links 13/04/12 - John Sansom SQL Server DBA in the UK

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php