Graphically Displaying the Execution Plan Using SQL Query Analyzer

Optimizing SQL Database Performance

Optimizing Database Performance

Graphically Displaying the Execution Plan Using SQL Query Analyzer

SQL 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™ 2000 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 Query Analyzer shows suggestions for additional indexes and statistics on nonindexed columns that would improve the ability of the query optimizer to process a query efficiently. In particular, SQL 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 (clsColumn)
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 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 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