Displaying Graphical Execution Plans (SQL Server Management Studio)
SQL Server Management Studio is an interactive, graphical tool that enables a database administrator or developer to write queries, execute multiple queries simultaneously, view results, analyze the query plan, and receive assistance to improve the query performance. The Execution Plan options graphically display the data retrieval methods chosen by the SQL Server query optimizer. The graphical execution plan uses icons to represent the execution of specific statements and queries in SQL Server rather than the tabular representation produced by the Transact-SQL SET statement options SET SHOWPLAN_ALL or SET SHOWPLAN_TEXT, or the XML for representation produced by SET SHOWPLAN_XML. The graphical display is very useful for understanding the performance characteristics of a query. SQL Server Management Studio shows which statistics are missing, thereby forcing the query optimizer to make estimates about predicate selectivity, and then permits those missing statistics to be easily created.
Note
Execution plans are not displayed for encrypted stored procedures or for triggers.
Using the Execution Plan Options
Open or type a Transact-SQL script that contains the queries you want to analyze into the Management Studio query editor. After the script has been loaded into the Management Studio query editor, you can choose to either display an estimated execution plan or the actual execution plan by clicking the Display Estimated Execution Plan or the Include Actual Execution Plan button on the query editor toolbar. If you click Display Estimated Execution Plan, the script is parsed and an estimated execution plan is generated. If you click Include Actual Execution Plan, you must execute the script before the execution plan is generated. After the script is parsed or executed, click the Execution plan tab to see a graphical representation of execution plan output.
To use the graphical execution plan feature in Management Studio, and to use the Showplan Transact-SQL SET statement options, users must have sufficient permissions to execute the Transact-SQL statements and queries. Users must also be granted the SHOWPLAN permission for all databases containing referenced objects. For more information, see Showplan Security.
Reading the Graphical Execution Plan Output
To view the execution plan, click the Execution plan tab in the results pane. The graphical execution plan output in SQL Server Management Studio is read from right to left and from top to bottom. Each query in the batch that is analyzed is displayed, including the cost of each query as a percentage of the total cost of the batch. For more information about the icons used to display execution plans in Management Studio, see Graphical Execution Plan Icons (SQL Server Management Studio).
The following descriptions provide guidelines for interpreting the graphical execution output in Management Studio:
Each node in the tree structure is represented as an icon that specifies the logical and physical operator used to execute that part of the query or statement.
Each node is related to a parent node. Child nodes of the same parents are drawn in the same column. However, all nodes in the same column do not necessarily have the same parent. Rules with arrowheads connect each node to its parent.
Operators are shown as symbols related to a specific parent.
Arrow width is proportional to the number of rows. Actual number of rows is used when it is available. If not, then the estimated number of rows is used.
When the query contains multiple statements, multiple query execution plans are drawn.
The parts of the tree structures are determined by the type of statement executed.
For parallel queries, which involve multiple CPUs, the Properties for each node in the graphical execution plan displays information about the operating system threads used. To view the properties for a node, right-click the node, and then click Properties. For more information about parallel queries, see Parallel Query Processing.
Type of statement Tree structure element Transact-SQL and stored procedures
If the statement is a stored procedure or Transact-SQL statement, it becomes the root of the graphical execution plan tree structure. The stored procedure can have multiple children that represent statements called by the stored procedure. Each child is a node or branch of the tree.
Data manipulation language (DML)
If the statement analyzed by the SQL Server query optimizer is a DML statement, such as SELECT, INSERT, DELETE, or UPDATE, the DML statement is the root of the tree. DML statements can have up to two children. The first child is the execution plan for the DML statement. The second child represents a trigger, if used in or by the statement.
Conditional
The graphical execution plan divides conditional statements such as IF...ELSE statements (if condition exists, then do the following, else do this statement instead) into three children. The IF...ELSE statement is the root of the tree. The IF condition becomes a subtree node. The THEN and ELSE conditions are represented as statement blocks. WHILE and DO-UNTIL statements are represented using a similar plan. IF and WHILE have their own icons.
Relational operators
Operations performed by the query engine, such as table scans, joins, and aggregations, are represented as nodes on the tree.
DECLARE CURSOR
The DECLARE CURSOR statement is the root of the graphical execution plan tree, with its related statement as a child or node.
In SQL Server 2005, individual colors have been associated with each of the three icon types: iterator (logical and physical operators) icons are blue, cursors icons are yellow, and language elements are green.
Graphical Execution Plan Node ToolTips
Each node displays ToolTip information when the cursor is pointed at it as described in the following table. Not all nodes in a graphical execution plan contain all ToolTips items described here.
| ToolTip item | Description |
|---|---|
Physical Operation |
The physical operator used, such as Hash Join or Nested Loops. Physical operators displayed in red indicate that the query optimizer has issued a warning, such as missing column statistics or missing join predicates. This can cause the query optimizer to choose a less-efficient query plan than otherwise expected. For more information about column statistics, see Index Statistics. When the graphical execution plan suggests creating or updating statistics, or creating an index, the missing column statistics and indexes can be immediately created or updated using the shortcut menus in SQL Server Management Studio Object Explorer. For more information, see Indexes How-to Topics. |
Logical Operation |
The logical operator that matches the physical operator, such as the Inner Join operator. The logical operator is listed after the physical operator at the top of the ToolTip. |
Estimated Row Size |
The estimated size of the row produced by the operator (bytes). |
Estimated I/O Cost |
The estimated cost of all I/O activity for the operation. This value should be as low as possible. |
Estimated CPU Cost |
The estimated cost of all CPU activity for the operation. |
Estimated Operator Cost |
The cost to the query optimizer for executing this operation. The cost of this operation as a percentage of the total cost of the query is displayed in parentheses. Because the query engine selects the most efficient operation to perform the query or execute the statement, this value should be as low as possible. |
Estimated Subtree Cost |
The total cost to the query optimizer for executing this operation and all operations preceding it in the same subtree. |
Estimated Number of Rows1 |
The number of rows produced by the operator. |
1 This ToolTip item displays as Number of Rows in an Actual Execution Plan.
Note
The information that was available in the Argument fields of the graphical execution plans in SQL Server 2000 now appears separately in the ToolTips of the graphical execution plans in SQL Server 2005.
Graphical Execution Plan Node Properties
You can view detailed information about nodes in the graphical execution plan by right-clicking the node, and then clicking Properties.
See Also
Tasks
How to: Delete an Index (SQL Server Management Studio)
How to: Rebuild an Index (SQL Server Management Studio)
How to: Reorganize an Index (SQL Server Management Studio)
How to: View All Indexes in a Table (SQL Server Management Studio)
Concepts
How to: Modify an Index (SQL Server Management Studio)
Other Resources
SET SHOWPLAN_ALL (Transact-SQL)
SET SHOWPLAN_TEXT (Transact-SQL)
SET STATISTICS PROFILE (Transact-SQL)
SET SHOWPLAN_XML (Transact-SQL)
SET STATISTICS XML (Transact-SQL)
Working with Indexes (Visual Database Tools)
Execution Plan How-to Topics