Graphically Displaying the Execution Plan Using SQL Server Query Analyzer

SQL Server Query Analyzer 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 Microsoft® 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 SET SHOWPLAN_ALL or SET SHOWPLAN_TEXT statements. This is very useful for understanding the performance characteristics of a query. Additionally, SQL Server Query Analyzer shows suggestions for additional indexes and statistics on nonindexed columns that would improve the query optimizer’s ability to process a query efficiently. In particular, SQL Server Query Analyzer 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.

The following icons displayed in the graphical execution plan represent the physical operators used by SQL Server to execute statements. For more information, see Logical and Physical Operators.

Icon	Physical operator
	Assert
	Bookmark Lookup
	Clustered Index Delete
	Clustered Index Insert
	Clustered Index Scan
	Clustered Index Seek
	Clustered Index Update
	Collapse
	Compute Scalar
	Concatenation
	Constant Scan
	Deleted Scan
	Filter
	Hash Match
	Hash Match Root
	Hash Match Team
	Index Delete
	Index Insert
	Index Scan
	Index Seek
	Index Spool
	Index Update
	Inserted Scan
	Log Row Scan
	Merge Join
	Nested Loops
	Parallelism
	Parameter Table Scan
	Remote Delete
	Remote Insert
	Remote Query
	Remote Scan
	Remote Update
	Row Count Spool
	Sequence
	Sort
	Stream Aggregate
	Table Delete
	Table Insert
	Table Scan
	Table Spool
	Table Update
	Top

The following icons displayed in the graphical execution plan represent the cursor physical operators used by SQL Server to execute statements.

Icon	Cursor physical operator
	Dynamic
	Fetch Query
	Keyset
	Population Query
	Refresh Query
	Snapshot

Reading the Graphical Execution Plan Output

The graphical execution plan output in SQL Server Query Analyzer 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.

Each node in the tree structure is represented as an icon that specifies the logical and physical operator used to execute part of the query or statement.
Each node is related to a parent node. All nodes with the same parent are drawn in the same column. Rules with arrowheads connect each node to its parent.
Recursive operations are shown with an iteration symbol.
Operators are shown as symbols related to a specific parent.
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.

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.
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.

Each node displays ToolTip information when the cursor is pointed at it. The ToolTip information can include:

The physical operator (Physical Operation) 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 Statistical Information. The graphical execution plan suggests remedial action, such as creating or updating statistics, or creating an index. The missing column statistics and indexes can be immediately created or updated using the context menus of SQL Server Query Analyzer.
The logical operator (Logical Operation) that matches the physical operator, such as the Join operator. The logical operator, if different from the physical operator, is listed after the physical operator at the top of the ToolTip and separated by a forward slash ( / ).
The number of rows (Row Count) output by the operator.
The estimated size of the row (Estimated Row Size) output by the operator.
The estimated cost (I/O Cost) of all I/O activity for the operation. This value should be as low as possible.
The estimated cost for all CPU activity (CPU Cost) for the operation.
The number of times the operation was executed (Number of executes) during the query.
The cost to the query optimizer (Cost) in executing this operation, including cost of this operation as a percentage of the total cost of the query. 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.
The total cost to the query optimizer (Subtree cost) in executing this operation and all operations preceding it in the same subtree.
The predicates and parameters (Argument) used by the query.

To create statistics

To update statistics

To delete statistics

To create a new index

To modify an index

To delete an index

Graphically Displaying the Execution Plan Using SQL Server Query Analyzer

Reading the Graphical Execution Plan Output

See Also