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.
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:
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:
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;
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
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
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:
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.
This article was originally written by 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:
For more information about how to read query execution plans, check out these articles and blog posts: