Performance Tuning - Execution Plan

The plan should be read from right to left.

Check the Graphical execution plan of a stored procedure / Query

·         Table Scan – Index is missing

·         Index Scan – Proper indexes are not using

·         Bookmark Lookup – Limit the number of columns in the select list

·         Filter – Remove any functions from where clause, May require 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?

·         Dataflow Arrow – High density: Sometimes you find few rows as outcome but the arrow line density indicates the query/proc processing huge number of rows

·          Cost – Can easily find out which table / operation taking much time

From the execution plan we can find out the bottleneck and give the possible solution to avoid the latency. Either the user must be mapped to sysadmin, db_owner, db_creator or he/she will be granted the permission “Show Plan”.

GRANT SHOWPLAN TO [username]