Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Tips to optimize your SQL statements Expand / Collapse
Author
Message
Posted Wednesday, July 28, 2010 9:14 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:53 PM
Points: 241, Visits: 539
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 -
Post #960448
Posted Thursday, July 29, 2010 6:47 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, May 19, 2014 11:22 AM
Points: 1,222, Visits: 142
Thanks for the list, it's a great starting place!

Think great, be great!
Post #960657
Posted Thursday, July 29, 2010 6:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:41 AM
Points: 2,818, Visits: 2,553
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.
Post #960668
Posted Thursday, July 29, 2010 9:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 27, 2014 7:43 AM
Points: 2, Visits: 61
What perfect timing, the sp_updatestats stored procedure took a query that was taking 3 minutes to run down to 1 second.
Post #960830
Posted Thursday, July 29, 2010 2:21 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:41 AM
Points: 861, Visits: 2,357
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?"
Post #961015
Posted Thursday, July 29, 2010 3:40 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 12:02 PM
Points: 42, Visits: 152
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
Post #961052
Posted Thursday, July 29, 2010 7:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 5:19 PM
Points: 20, Visits: 161
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
Post #961082
Posted Thursday, July 29, 2010 11:02 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, July 20, 2014 9:06 PM
Points: 2,693, Visits: 1,202
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?



Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Post #961122
Posted Friday, July 30, 2010 12:26 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:53 PM
Points: 241, Visits: 539
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 -
Post #961153
Posted Friday, July 30, 2010 12:33 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:53 PM
Points: 241, Visits: 539
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 -
Post #961155
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse