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.

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.

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.

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.

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.

You can view detailed information about nodes in the graphical execution plan by right-clicking the node, and then clicking Properties.

Other Resources

Help and Information