Query Plan Tree Structure

The query plan tree view shows the high-level query plan structure.

In the tree view, each node represents a query operator, and the link between nodes shows the connection between parent and child operators.
Operators of the same type use the same color, which indicates how data flows through the query plan nodes; from the leaf nodes to the top node of the plan.
The links between operators are tagged with the actual number of rows exchanged at runtime between the child and parent node for that particular link.
Hovering your cursor over the actual number of rows tag shows the number of estimated rows, which is calculated by the optimizer before the query is executed.
The text that executes the query is included in the HTML output and can be used to perform analysis and comparison between the optimizer expected values and the real values at execution.

The following query uses the set statistics plan_html command to show the query plan tree in HTML format. The query is based on this example:

> set statistics plan_html on
> update titles 
  set price = price * 2 
  from titles, publishers 
  where titles.pub_id = publishers.pub_id 
  and publishers.state = "CA" 
> go

The information provided for each operator includes:

Operator name, such as Emit, Join, TableScan, Group By, and so on. Operator names are the same as those from the output of show_plan_in_xml, but might be different from the output of set showplan on.
Operator internal ID number.
Table name in the case of operators scanning or accessing tables.
Index name for operators using indexes to access the tables.

For queries executed in parallel by multiple threads, hovering your cursor over the operator shows the number of threads involved in the execution of the operator. The degree of parallelism is shown by the size of the shadow box around that represents the operator: The larger the shadow, the higher the parallel degree.

In comparison, the same query is shown using the set showplan on command to view the query plan output in text format:

 > set showplan on
> update titles 
  set price = price * 2 
  from titles, publishers 
  where titles.pub_id = publishers.pub_id 
  and publishers.state = "CA" 
> go     

STEP 1
  The type of query is UPDATE.

  5 operator(s) under root

  |ROOT:EMIT Operator (VA = 5)
  |
  | |UPDATE Operator (VA = 4)
  | |  The update mode is deferred_varcol.
  | |
  | | |NESTED LOOP JOIN Operator (VA = 3) (Join Type: Inner Join)
  | | |
  | | | |GROUP SORTED Operator (VA = 1)
  | | | |Distinct
  | | | |
  | | | |  |SCAN Operator (VA = 0)
  | | | | |  FROM TABLE
  | | | | |  publishers
  | | | | |  Table Scan.
  | | | | |  Forward Scan.
  | | | | |  Positioning at start of table.
  | | | | |  Using I/O Size 2 Kbytes for data pages.
  | | | | |  With LRU Buffer Replacement Strategy for data pages.
  | | |
  | | | |SCAN Operator (VA = 2)
  | | | |  FROM TABLE
  | | | |  titles
  | | | |  Table Scan.
  | | | |  Forward Scan.
  | | | |  Positioning at start of table.
  | | | |  Using I/O Size 2 Kbytes for data pages.
  | | | |  With LRU Buffer Replacement Strategy for data pages.
  | |
  | |  TO TABLE
  | |  titles
  | |  Using I/O Size 2 Kbytes for data pages.