SQL Server Execution Plan

If you want to analyze why your queries or stored procedures are performing poorly, the first place to look is the execution plan. You can visualize the plan in SQL Server query analyzer, or other products, and of course in AppDynamics for Databases.

In Query Analyzer you can see the Estimated Execution Plan or the Actual Execution Plan. If you want to see an estimation of how SQL Server will execute your query select the "Display Estimated Execution Plan" button on the toolbar; this shows 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 T-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 displays the plan for a stored procedure in the MSPetShop database. You can see the step of the plan with the greatest cost, and therefore the step which you can optimize.

Tuning T-SQL is of course a vast topic, but a couple of things to look out for include:

• Index or table scans: May indicate a need for better or additional indexes.

• Bookmark Lookups: Consider changing the current clustered index, using a covering index and limiting 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)