Examining Query Execution Plans

Several ways exist to generate an execution plan in SQL Server. First, and most frequently used, are the graphical execution plans. These are followed by XML execution plans and plain text execution plans.

[hide]

Graphical Execution Plans

Graphical execution plans are accessed through the query window inside Management Studio in SQL Server 2005/2008 or through Query Analyzer in SQL Server 2000. To a large degree, the functionality of graphical plans is the same in SQL Server 2000 as it is in SQL Server 2008. However, there are some fundamental differences. For this reason, the different versions will be discussed individually in order to be more specific to the differences between the versions of SQL Server. Differences between SQL Server 2005 and 2008 will be highlighted within the section.

In all versions there are two fundamental types of execution plan, the estimated execution plan and the actual plan. The estimated plan does not require the query to be run while the actual plan is an output from the query engine showing the plan used to execute the query. Most of the time these two plans are identical, but there are circumstances where they will be different.

All graphical plans are read from the right to the left and from the top to the bottom. That's important to know so that you can understand other concepts such as understanding how a hash join works. Each icon represents an operation. Some operations are the same between the Estimated and Actual plans and some vary between the two plans. Each operator is connected by an arrow that represents a data feed. The data feed is the output from one operator and the input for the next. The thickness of the data feed varies per the amount of the data it represents. Thinner arrows represent fewer rows and thicker arrows represent more rows.

Operators represent various objects and actions within the execution plan. A full listing of operators is available in the Books Online.

SQL Server 2005/2008

Estimated Execution Plan

There are several ways to generate an estimated execution plan:

Select the "Display Estimated Execution Plan" from the tool bar
Right click within the query window and select "Display Estimated Execution Plan"
Select the Query menu and then the "Display Estimated Execution Plan" menu choice
Press CTL-L

When any of these actions is performed, an estimated, graphical, execution plan is created for the query in the query window. The query is not executed. That is an important point. The query is merely run against the query optimizer within the SQL Server system and the output from the optimizer is displayed as a graphical execution plan. If objects that don't exist, such as temporary tables, are part of the query, the estimated plan will fail.

Actual Execution Plan

An actual execution plan requires the query to be executed. To enable the generation of the actual execution plan:

Select the "Include Actual Execution Plan" button from the tool bar
Right click within the query window and select "Include Actual Execution Plan"
Select the Query menu and then the "Include Actual Execution Plan" menu choice
Press CTL-M

After the query executes, the actual execution plan will be available in a different tab in the results pane of the query window.

Execution Plan Example

Working through an execution plan to understand what is happening in the query and what needs to get fixed is the primary reason for generating execution plans. Take the following query for example:

SELECT  soh.[SalesOrderID] 
    ,soh.[OrderDate] 
    ,soh.[ShipDate] 
    ,sod.[ProductID] 
    ,sod.[OrderQty] 
    ,sod.[UnitPrice] 
    ,soh.[CustomerID] 
FROM    [Sales].[SalesOrderHeader] AS soh 
        JOIN [Sales].[SalesOrderDetail] AS sod 
            ON soh.[SalesOrderID] = sod.[SalesOrderID] 
WHERE soh.[CustomerID] = 29559;

This generates the following execution plan:

Starting at the right and the top you see an Index Seek (NonClustered) against the index named [SalesOrderHeader].[IX_SalesOrderHeader_CustomerId]. This feeds data out to a Nested Loop (Inner Join). Working down you can see a Key Lookup (Clustered) operation against the PK_SalesOrderHeader_SalesOrderID. This is a classic key lookup, or what used to be called, a bookmark lookup. More on that later. You can see that the data feeds back up to the Nested Loop and then that feeds on down to another Nested Loop operator. Below that is a Clustered Index Seek (Clustered) against the [PK_SalesOrderDetail_SalesOrderId] primary key. Finally the data flow goes out to the SELECT operator. That's the basic information available within the execution plan. Lots more detail is also available.

Hover with the mouse over one of the operators and you will get a tool tip, different for each operation type, showing some of the detail behind the operator. Displayed below is the tool tip for the Key Lookup operator:

At the very top of the tool tip is a description of the operator. In this case, "Uses a supplied clustering key to lookup on a table that has a clustered index." Most operators will include this description, telling you what the operator does within the execution plan. After that, most operations will have a varying number, and type, of fields within the tool tip, supplying different kind of information. An example of one of the common fields is Estimated Operator Cost. You'll see this in most tool tips for most operators. A piece of information that is specific to this operator (although not unique to this operator) is the Seek Predicate information at the bottom of the tool tip.

But the most interesting piece of information for the Key Lookup operator is that it exists within this execution plan. It exists because, while the index on CustomerID is sufficient to get a specific set of rows returned to the application, all the columns needed are not contained on the index. Because the data is stored on the clustered index, and additional set of seeks are required to retrieve the data, which is joined with the information retrieved from the index on CustomerID through the Nested Loop join operation.

To see even more information about the operators in the execution plan, right click an operator and select "Properties" from the drop down menu. This will open a complete properties sheet. Much of the data on the properties sheet is the same as that available in the Tool Tip, but even more is on display in the property sheet.

SQL Server 2000

All you have to do is choose Query, Display Execution Plan. This option is equivalent to setting NOEXEC and SHOWPLAN_ALL on, but displaying the execution plan in a graphical format. The query will not be executed, however, SQL Server will display the execution plan chosen by the optimizer. If you wish to execute the query and see the execution plan choose Query, Show Execution Plan.

The graphical output in query analyzer is extremely helpful. Moving your mouse pointer over one of the icons will give you details about the type of operation performed. This utility also lets you create and update statistics, and, create, modify or drop existing indexes. If the statistics are missing or out of date the graphical output will show the table or index in red.

Getting used to various icons might take you a little while, however, the good news is that each of the icons provides a tool tip giving you a brief explanation of what it is for when you hold your mouse pointer over it. It is not recommended that you memorize the meaning of each icon, after looking at this graphical plan you will be able to tell if your query has a problem. The icon that you rarely want to see is a table scan - it looks like a table with a blue arrow in the middle of it.

Text Execution Plans

There are a few SET commands that can help you examine the query optimizer's decisions and decide whether they produce desired results. Just like other commands these SET commands can be turned ON or OFF. They stay in-force for the duration of the connection, or until you explicitly change the setting.

SET STATISTICS IO ON will provide the number of physical reads (reads from the disk), the number of logical reads (reads from the memory cache), scan count and the number of read-ahead reads (number of data or index pages placed in cache for the query). For example, the following query will retrieve such statistics.

SET STATISTICS IO ON  
GO  
SELECT * FROM authors

Resulting message:

1.Table 'authors'. Scan count 1, logical reads 138, physical reads 0, read-ahead reads 0.

SET NOEXEC ON will compile the query but will not execute it. This is helpful if you are testing a query that might take a long time. Instead of running a query each time you make changes to it, you might wish to examine the execution plan first.

SET STATISTICS TIME ON will inform you about the CPU time used and SQL Server time used to execute a particular query, for example:

SET STATISTICS time ON  
GO  
SELECT * FROM authors

Resulting messages:

SQL Server Execution Times:      
CPU time = 0 ms, elapsed time = 0 ms.   
SQL Server parse and compile time:       
CPU time = 0 ms, elapsed time = 0 ms.   
SQL Server parse and compile time:       
CPU time = 0 ms, elapsed time = 0 ms. 
SQL Server Execution Times:      
CPU time = 70 ms, elapsed time = 444 ms.

This output might be somewhat confusing in the beginning. The first statement refers to the time it took to execute the SET STATISTICS TIME ON statement - which is too small to measure. The second and third statement inform us of parse and compilation time for two statements: GO and SELECT * FROM authors. The statement in bold is the one we are most interested in: that is the actual time spent executing SELECT * FROM authors command.

SET SHOWPLAN_ALL ON will give you detailed information about the execution plan. The output of SHOWPLAN_ALL is not straightforward, however, understanding it give you the opportunity to know what is going on "behind the scenes". The following table describes the output of SHOWPLAN:

Column Name	Description
StmtText	This column either repeats the submitted query (in which case it's not very useful) or contains the physical and logical operations included in the query execution plan
StmtID	Number of the statements issued before the current statement in the current connection
NodeID	Node ID in the query
Parent Node	ID for the parent step of the current node
PhysicalOP	This is the physical implementation of the algorithm chosen by the query optimizer. If the row type is not plan_rows then this column is NULL
LogicalOp	Logical implementation of the algorithm chosen by the query optimizer. If the row type is not plan_rows then this column is NULL
Argument	Provides additional information about the physical operation. For instance if a clustered index is being scanned this column will show the name of the index as well as index keys.
DefinedValues	This column contains a comma-separated list of columns defined in the query, or the list of internal values examined by the query optimizer
EstimateRows	The number of rows affected by the query
EstimateIO	Estimated IO for the operation mentioned in this row
EstimateCPU	Estimated CPU usage for the operation mentioned in this row
AvgRowSize	Average row size in bytes passed by this operation
TotalSubtreeCost	Estimated cost of this operation as well as all child operations
OutputList	List of the columns in the result set
Warnings	This column contains a coma-separated list of warnings that pertain to the current operation. For instance it might warn you that the statistics on a particular index being queried are out of date
Type	For the statements referenced in the query this column will contain the appropriate Transact-SQL command type (such as SELECT or UPDATE). For the rows that show the actual execution plan this column contains plan_row.
Parallel	If this column contains 1 than an operation is running in parallel
EstimateExecutions	Estimated number this operation will have to be executed for satisfying the current query

Perhaps the most useful column out of the entire SHOWPLAN_ALL output is the StmtText, which tells you about the type of operation performed, whether it is a table scan, clustered or non-clustered index scan, etc. Most of this information is repeated again in PhysicalOp, LogicalOp and Argument columns, whichever is appropriate. Another column to watch is the Warnings - it might give you a clue to why your query isn't performing up to your expectations. Usually a warning might state that statistics are out of date or a join predicate is missing.

Author Credits

This article was originally written by Grant Fritchey.

Grant Fritchey

Grant Fritchey works for FM Global, an industry-leading engineering and insurance company, as a principal DBA. He's done development of large-scale applications in languages such as VB, C#, and Java. He has worked with SQL Server since version 6.0. He has worked in finance and consulting and for three failed dot coms. He is the author of Dissecting SQL Server Execution Plans (Simple Talk Publishing, 2008) and SQL Server 2008 Performance Tuning Distilled (Apress, 2009).

His online presences include:

Blog - http://scarydba.wordpress.com/
Twitter - http://twitter.com/GFritchey