The execution plan is the method by which SQL Server accesses the data that resides in the underlying tables. It examines your query then determines the most efficient way to retrieve the data. Execution plans are compiled and stored with stored procedures, so SQL Server will know the best way to access data when a given stored procedure is called. SQL Server provides a GUI that displays how the execution plan is used to retrieve the data. When developing procedures, you should utilize the graphical execution plan to assist in writing the most efficient stored procedures. Please keep the following tips in mind when examining the graphical execution plan:
- If you would like to see the execution plan, but not execute the query, choose “Display Estimated Execution Plan”. This will show you an estimate of the actual execution plan. Note that this will not work if temp tables are used because the temp tables cannot be parsed until runtime. If you use a table variable, however, the estimated execution plan will return successfully.
- Graphical Execution plans are read from right to left, and the arrows on each step correspond to the number of rows returned for that step. The fatter the arrow, the more rows returned. The execution plan eventually merges into one last step on the left side of the plan. Thick arrows should appear on the right of the execution plan and get smaller as the plan moves left.
- Execution plans are broken down into smaller substeps. Each substep is displayed as a percentage of runtime versus the query as a whole. For a long running query, identify the substep with the highest percentage and begin tuning at that step.
- Each action within the substep is displayed as a percentage of runtime versus the substep as a whole. For long running queries, look to tune the long running actions.
- If you move your cursor over any of the displayed actions, a pop up window appears with more detailed information on that particular step.
- If you see the below in an execution plan, look to modify the query so that these actions are eliminated or reduced. It’s not always possible to avoid them, but the more they can be prevented, the better performance will be:
- 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, reduce 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 sorting be done at the client more efficiently?
- While I/O and CPU cost don’t directly refer to distinct measurements (they are internal SQL counters), higher I/O and CPU cost values in the execution plan utilize more resources than lower values
- Another cause of Bookmark lookups are the use of “SELECT *”
- Sometimes SQL Server will need to create a temporary worktable in the tempdb database. Anytime a worktable is used, performance is hurt because of the additional I/O for maintaining the worktable.
- The graphical execution plan will warn you that a worktable is being used when the following actions are displayed:
- Index Spool
- Row Count Spool
- Table Spool
