The SQL Server Execution Plan

If you want to analyse why your queries or stored procedures are performing poorly, the first place to look is the execution plan. You can visualise the plan in sql server query analyzer, or other products, and of course in DBTuna.

From within Query Analyzer you have the option of seeing the Estimated Execution Plan or the Actual Execution Plan. If you want to see an estimation of how SQL Server will execute your query you can simply select the "Display Estimated Execution Plan" button on the toolbar, this will show you the plan without actually executing the query (which should be very similar if not identical to the real plan anyway). For long running queries, or those involving DML i.e. inserts/updates/deletes etc. this is the best option. The downside to the Estimate Plan is if you have a stored procedure, or other batch Transact-SQL code that uses temp tables, you cannot use the "Display Estimated Execution Plan". The reason for this is that the Optimizer, which is what is used to generate the estimated plan, doesn't execute the T-SQL, so since the query has not been executed the temporary table does not exist. An error message of this type will be displayed if you do try to display an estimated plan for a query containing temp tables:

Msg 208, Level 16, State 1, Line 107

Invalid object name '#temp_table'

What to look for in the Execution Plan?

If you have a poorly performing piece of T-SQL that you are trying to tune, the obvious place to start it to look at the most costly step of the plan. The Screenshot below (taken from DBTuna) displays the plan for a stored procedure in the MSPetShop database. We can see the step of the plan with the greatest cost, and therefore the step which we can try and optimize. Tuning T-SQL is of course a vast topic, but a couple of things to look out for could include:

*Index or table scans: May indicate a need for better or additional indexes.
*Bookmark Lookups: Consider changing the current clustered index, consider using a covering index, limit the number of columns in the SELECT statement.
*Filter: Remove any functions in the WHERE clause, don't include views in your Transact-SQL code, may need additional indexes.
*Sort: Does the data really need to be sorted? Can an index be used to avoid sorting? Can sorting be done at the client more efficiently?

(*taken from sql-server-performance.com)

Copyright ©2010 DBTuna. All rights reserved.