Tips to optimize your SQL statements

Question

Post reply

Tips to optimize your SQL statements

brian118

Hall of Fame

Points: 3506
More actions
July 28, 2010 at 9:14 pm

#225527

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 -

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

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

bbop1322 SSCommitted Points: 1694 More actions · Answer 1

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

Think great, be great!

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

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: 460 More actions · Answer 6

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 More actions · Answer 7

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 More actions · Answer 8

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 More actions · Answer 9

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 More actions · Answer 10

dfortier

SSC-Addicted

Points: 411

July 30, 2010 at 7:35 am

#1200320

Very good information, thanks for sharing.

Don

Nadrek SSC-Insane Points: 20039 More actions · Answer 11

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 More actions · Answer 12

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 More actions · Answer 13

@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 More actions · Answer 14

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?