Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jul 31, 2001

SQL Server Performance Tuning: Pt. 1 - Page 3

By Aaron Goldman

Show Execution Plan

A decent adjunct to the Profiler is the graphical Execution Plan available in the query analyzer. Just select the Query Menu and near the bottom is "Show Execution Plan". Select it and run a query or stored procedure and the execution plan will be shown graphically. You can also show the execution plan without running the query by selecting "Display Estimated Execution Plan" under Query Menu (or Ctl-L).

The information shown is all you need to know to attack an optimization problem. It tells what the execution strategy is and the relative cost of each piece. Optimization will consist of either speeding up individual parts, most likely with indexes, or making the database engine choose a different execution strategy altogether by reformatting the query or rearchitecting the data.

If more than one statement is executed, as in a batch or procedure, or inside a trigger, they will all be graphed and the relative costs will be displayed:

Execution Plan Screen Shot
(Click image for full size)

Reading the output can be something of an art, especially for complex queries involving many tables where the picture is so large it's difficult to see many of the pieces at once. It's not truly necessary to know what all the icons and terminology mean, but only to understand how they relate to the query and point to the slow operations. A gut feel will suffice.

To optimize a query, you should check each piece for its relative cost. Find the one or two spots with a high cost. In the above picture there is one piece that takes 21% and everything else is negligible. When optimizing a query you can usually just concentrate on minimizing the most expensive parts, while ignoring the rest.

If you hover the mouse over an icon, you can see its details:

Execution Plan Details (from Mouseover)
(Click image for full size)

Common things to look for are using an unexpected index and getting a large rowcount when you believe the query should be easily restricted on this table.

There's also a text based execution plan you can use, but I prefer the graphical output.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM