Tips to optimize your SQL statements

  • brian118

    Hall of Fame

    Points: 3506

    Comments posted to this topic are about the item Tips to optimize your SQL statements

    Regards
    Brian Ellul
    ----------------------------------------------------------------------------------
    Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
    - Albert Einstein -

  • bbop1322

    SSCommitted

    Points: 1694

    Thanks for the list, it's a great starting place!

    Think great, be great!

  • Daniel Bowlin

    SSC-Dedicated

    Points: 34566

    Great article. Lots of good things to be on the look out for. I am going to have to come back to this article a bunch of times. I wrote a query yesterday that could probably use a spin through the last half of this article. Thanks.

  • ehinrichsen

    SSC Rookie

    Points: 38

    What perfect timing, the sp_updatestats stored procedure took a query that was taking 3 minutes to run down to 1 second.

  • Nadrek

    SSC-Insane

    Points: 20039

    I didn't see a very important one:

    Pull up SQL Server Profiler, and watch the SQL:BatchCompleted and/or RPC:Completed numbers for CPU, Reads, Writes, and Duration. Know which is most important in your environment.

    Then re-write the statement a few different ways against the full dataset (always more than once), and see, in your actual environment, how each works. Derived tables vs temp tables vs table variables vs CTE vs straight joins, one large statement vs smaller statements, etc. etc.

    Know how much resources a given query "should" take; check if hints help, or hurt, and by how much, and decide if they're worth it or not. This is a reasonable way to find missing indexes or SQL Server generating bizzarely suboptimal query plans, by going "Hey! That's too much... why is that too much?"

  • wbrianwhite

    SSC Eights!

    Points: 954

    Make sure your where clause is SARG-able or indexes won't help:

    http://weblogs.sqlteam.com/dang/archive/2009/03/07/Low-Hanging-Fruit-of-Sargable-Expressions.aspx

  • cameron.wells

    SSC-Addicted

    Points: 430

    Thanks for the great check list, but i also want to take some of your other advice that this doesn't substitute reading books and white papers on SQL Performance. Can you please recommend several of the books you've read that you believe would be most beneficial to read for another DBA?

    e-mail is cameron.g.wells@gmail.com

    Thanks,

    Cameron

  • philcart

    SSC-Forever

    Points: 47794

    Nadrek (7/29/2010)


    I didn't see a very important one:

    Pull up SQL Server Profiler, and watch the SQL:BatchCompleted and/or RPC:Completed numbers for CPU, Reads, Writes, and Duration. Know which is most important in your environment.

    Then re-write the statement a few different ways against the full dataset (always more than once), and see, in your actual environment, how each works. Derived tables vs temp tables vs table variables vs CTE vs straight joins, one large statement vs smaller statements, etc. etc.

    Know how much resources a given query "should" take; check if hints help, or hurt, and by how much, and decide if they're worth it or not. This is a reasonable way to find missing indexes or SQL Server generating bizzarely suboptimal query plans, by going "Hey! That's too much... why is that too much?"

    Would have thought that this is part of an investigation/monitoring phase. Once you've identified a query that needs attention, you run though the great list provided by Brian.

    It's amazing how often you don't need to worry about re-writing SQL when all that's needed is a new index, or updated statistics. If either of these two meet your needs, why go through the hassle of re-writing the query/procedure?

    --------------------
    Colt 45 - the original point and click interface

  • brian118

    Hall of Fame

    Points: 3506

    Thanks to all for your feedback!

    Regards
    Brian Ellul
    ----------------------------------------------------------------------------------
    Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
    - Albert Einstein -

  • brian118

    Hall of Fame

    Points: 3506

    cameron.wells (7/29/2010)


    Thanks for the great check list, but i also want to take some of your other advice that this doesn't substitute reading books and white papers on SQL Performance. Can you please recommend several of the books you've read that you believe would be most beneficial to read for another DBA?

    e-mail is cameron.g.wells@gmail.com

    Thanks,

    Cameron

    Dissecting SQL Server Execution Plans (by Grant Fritchey)

    SQL Server 2008 Query Performance Tuning Distilled (by Grant Fritchey & Sajal Dam)

    Regards
    Brian Ellul
    ----------------------------------------------------------------------------------
    Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
    - Albert Einstein -

  • dfortier

    SSC-Addicted

    Points: 411

    Very good information, thanks for sharing.

    Don

  • Nadrek

    SSC-Insane

    Points: 20039

    philcart (7/29/2010)


    Would have thought that this is part of an investigation/monitoring phase. Once you've identified a query that needs attention, you run though the great list provided by Brian.

    It's amazing how often you don't need to worry about re-writing SQL when all that's needed is a new index, or updated statistics. If either of these two meet your needs, why go through the hassle of re-writing the query/procedure?

    Updated statistics, certainly; that's a normal operational cost.

    However, adding an index adds to the operational costs of writes to that table for the lifetime of the index; perhaps a simple, or even not so simple, rewrite would allow the query to improve performance without adding any additional costs?

    Perhaps more importantly, how will you learn what types of query implementations work better in which circumstances on your particular environment without testing them? This is critically important in some cases, particularly for writing new SQL in an environment that's heavily loaded for whatever reason; if there's little headroom, then using as little as possible of what's left is vital, in addition to freeing up more headroom.

  • wbrianwhite

    SSC Eights!

    Points: 954

    It's amazing how often you don't need to worry about re-writing SQL when all that's needed is a new index, or updated statistics. If either of these two meet your needs, why go through the hassle of re-writing the query/procedure?

    Indexes add a marginal performance hit as each row insert/update has to then go and update all the affected indexes. They are an essential tool, but shouldn't be used in place of sane queries.

  • philcart

    SSC-Forever

    Points: 47794

    @wbrianwhite & @Nadrek

    The cost of adding and updating the indexes is actually a lot smaller than it's made out to be. Only under extreme change (meaning insert/update/delete) activity will you notice performance degradation from index updates. I think it would be safe to say that a vast majority of SQL Server databases don't fit into the "extreme" category.

    For most businesses, the cost of adding the index is much less than the cost of re-developing a query/procedure. Especially when it's vendor supplied code and they have to pay for code changes 😉

    Yes, in some cases a re-write is warranted, but why not give the end users some benefit straight away and put any re-writes on the work queue?

    --------------------
    Colt 45 - the original point and click interface

  • Nadrek

    SSC-Insane

    Points: 20039

    philcart (7/30/2010)


    @wbrianwhite & @Nadrek

    The cost of adding and updating the indexes is actually a lot smaller than it's made out to be. Only under extreme change (meaning insert/update/delete) activity will you notice performance degradation from index updates. I think it would be safe to say that a vast majority of SQL Server databases don't fit into the "extreme" category.

    For most businesses, the cost of adding the index is much less than the cost of re-developing a query/procedure. Especially when it's vendor supplied code and they have to pay for code changes 😉

    Yes, in some cases a re-write is warranted, but why not give the end users some benefit straight away and put any re-writes on the work queue?

    The operational cost includes disk space to house the index, disk space and time to back it up, time to correctly figure out the right fillfactor, and maintenance window time to defragment the index regularly (however often or rare that happens to be - the default fillfactor of 100 tends to be pretty bad about requiring maintenance).

    When writing new SQL, why not take a little more time and do it well, instead of randomly? For existing bad SQL, most often I see that a few minutes rewriting is extremely beneficial, and has no bad side effects.

    In one nontrivial case, 45 minutes of rewriting resulted in four orders of magnitude of performance improvement. In many cases, 5 to 10 minutes of rewriting results in anywhere between double and an order of magnitude more performance.

    Essentially: Why add cost in one place to reduce cost in another before you attempt to reduce cost without adding any?

Viewing 15 posts - 1 through 15 (of 42 total)

You must be logged in to reply to this topic. Login to reply