DaveWentzel.com            All Things Data

How to Read a Graphical Plan

 

 
  • Each node in the tree structure is an “icon”
  • Each node has a parent.  All nodes with the same parent are in the same column.
  • Arrows connect nodes to parents
 
Queries are broken down into 5 components:
  • SQL statements and stored procedures
    • Become the root of the tree
    • SPs can have multiple children for each statement called
  • DML
    • Become the root of the tree
    • Can have two children, first is the execution plan, second is any trigger used
  • Conditionals (if else/while):  also becomes the root of the tree
  • Relational operators (table scans, joins, etc)
  • Cursor Declarations:  also becomes the root of the tree

 

JOIN Orders

Each JOIN icon will have two arrows pointing to it.  The uppermost arrow represents the outer table in the JOIN.  The lower arrow represents the inner table in the JOIN.  Following the arrows back will show you either the name of the tables being referenced or another JOIN.  Here you want to look at how many rows are being sent to JOIN for processing.  The upper arrow should always have fewer rows than the lower arrow.  If it doesn't then the JOIN order may not be optimal.  This is especially true (if you think about it) for nested loop JOINs.  Same with Hash Joins.  The only way you can reliably override this is using JOIN hints. 

 

After the plan is displayed each node will have a tooltip.  It includes:

--Physical/Logical Operation

--Estimated Row Count

--Estimated Row Size

--Estimated I/O Cost

--Estimated CPU cost

--Estimated number of executes:  the # of times the operation was executed during the query

--Estimated Cost:  cost of the operation as a percentage of the total cost of the query

--Estimated subtree cost:  total cost in executing this operation and all preceding operations in the same subtree

--Argument:  the predicates used by the node

Logical and Physical  Operators

--These “icons” in the execution plan describe how the sql text is executed. 

--Physical operators describe the physical algorithm used to process the statement, ie, Clustered Index Scan.

--Logical operators describe the relational algebra used to process the statement, ie, performing aggregation.

     --Logical operators not always used. 

Most Common Icons, Their Meanings, and Performance Implications

Other Visual Indicators

JOIN Operators

Execution Plans Home

Add new comment