Plan Explorer Results

Video

Statement Section

The top section of the Results tab contains the Statement section or Statements tree. The Statement section represents one of the biggest differences between Plan Explorer and SSMS. Its function is critical when navigating multi-statement plans. It shows a logical breakdown of the entire plan tree, including all control structures, nested procedure calls, and estimated and/or actual metrics for each statement, including operation counts.

Common Operations

• Select any statement in the grid to show the associated plan for that statement.
• Select any control structure (If or While), or Exec higher up the tree to see a filtered view of the plan starting from that point.
• Sort the statements list and the highest cost trees, and statements immediately bubble up to the top.

Context Menu Commands

Command	Description
Show Captured Statements Only	Note:  Applicable to only .queryanalysis files that originate in the SQL Sentry Performance Analysis integrated version of Plan Explorer. In the integrated version of Plan Explorer only statements that exceed the trace filters display by default. If a statement is part of the query call stack, but doesn't exceed the filters it's hidden. This helps you in quickly identifying the costliest parts of the query. Unchecking this option shows the entire query call stack.
Show Hidden Captured Statements	Note:  Applicable to only .queryanalysis files that originated in the SQL Sentry Performance Analysis integrated version of Plan Explorer. In the integrated version of Plan Explorer, you're able to hide statements in the Statements tree. If checked, those statements are no longer hidden.
Expand Subtree	Expands all the child nodes of the selected statement.
Expand All	Expands the entire Statement tree.
Collapse All	Collapses the entire Statement tree.
Edit Connection	Allows you to edit the SQL Server connection.
Copy >	Copies the cell, row, or all query information to the clipboard.
Copy Statement to Command Text	Copies the selected statement to the Command Text tab, overwriting any text currently in the tab.
Copy All Statements to Command Text	Copies all statements belonging to the query call stack over to the Command Text tab.

General Query and Plan Info Section

The General Query and Plan Information section is in the center panel by default. It contains the Text Data tab and the Plan XML tab.

Common Operations

• If you modify the query in the Command Text tab, the query text and XML synchronize when you generate a new Estimated or Actual Plan from the toolbar.
• The T-SQL statements synchronize with the Statements tree, meaning that if you select a row on the Tree view it auto-selects the associated T-SQL statement and its execution plan, and vice versa.

Context Menu Options

Command Text Panel

Command	Description
Undo	Allows you the undo pasting the recent query.
Cut	Cuts the selected text to the clipboard.
Copy	Copies the selected text to the clipboard.
Paste	Pastes the selected text from the clipboard.
Select All	Selects the text in the active tab.
Save SQL	Saves the text data as a .sql file.
Word Wrap	Wraps text to the current width of the panel.
Edit Connection	Allows you to edit the SQL Server connection.

Text Data Panel

Command	Description
Copy	Copies the selected text to the clipboard.
Select All	Selects the text in the active tab.
Copy to Command Text	Copies the selected information to the Command Text tab, overwriting any text currently in the tab.
Save SQL	Saves the text data as a .sql file.
Word Wrap	Wraps text to the current width of the panel.
Open	Allows you to open a new Plan Explorer session.

Plan XML Panel

Command	Description
Copy	Copies the selected text to the clipboard.
Paste Plan XML	Allows you to paste a new plan.
Select All	Selects the text in the active tab.
Save Execution Plan	Saves the current execution plan.
Word Wrap	Wraps text to the current width of the panel.

Plan Details

Plan Explorer offers several options and styles that can be applied to the Plan Diagram. When a plan is saved, these visual plan properties are maintained as part of the Plan Explorer session. Create a very specific view of the Plan Diagram for sharing and troubleshooting purposes. For a complete rundown of this feature, see the Plan Explorer PRO 2.5: Query plans your way blog post.

Plan Diagram Toolbar

Option	Description
Zoom	Scales the Plan Diagram between six percent and 400 percent of its original size. Use CTRL + Mouse Wheel to perform this operation.
Filter	Filter nodes from the Plan Diagram based on cost.
Stretch	Stretches the links between Plan Diagram nodes.
Flatten	Flattens the space between node levels in the Plan Diagram.
Mode	There are several modes available that change the layout of the entire Plan Diagram.
Link Style	The Link Style used between nodes in the Plan Diagram.
Rotation	Rotates the entire Plan Diagram 90 degrees.
Auto-Fit	Automatically fits the plan in the Plan Diagram tab.
Defaults	All Plan Diagram options are reset.

Plan Diagram Context Menu

Command	Description
Actual Costs	Shows metrics captured during Actual Plan retrieval.
Estimated Costs	Shows metrics associated with the Estimated Plan.
Show Per Node Costs	The default view, costs are per node, representing each individual nodes contribution to the total cost.
Show Cumulative Costs	Costs are cumulative, representing each individual nodes contribution plus the contribution of any of its children.
Costs By	I/O—Show costs by I/O CPU—Show costs by CPU CPU + I/O—Show costs by CPU +I/O
Line Widths By	Rows Data Size (MB)
Show Color Scale	Node costs are highlighted with an appropriate color scale, ranging from a light yellow to red.
Show Nested Procedures	Shows nested procedures.
Show Full Object Names	Objects are represented with full object names.
Expand Expressions	Expands all expressions.
Copy Output List	Copies the output list to the clipboard.
Missing Index Details	Displays the index details.
Expand All Subnodes	Expands all the subnodes.
Show Plan Layout Control	Shows the layout controls.
Zoom	Various options for zooming in and out. Alternatively, use CTRL + Mouse Wheel.

Plan Tree

The Plan Tree tab contains a tree representation of the plan's operations and associated metrics. Use the arrows to expand and collapse sections of the Plan Tree. Right-click any column header, and use the Column Chooser command to access additional plan metrics. Any significant differences between estimates and actuals are highlighted, as are possible problematic operations like scans and bookmark lookups.

Context Menu Options

Option	Description
Copy > Cell	Copies the selected cell.
Copy > Row	Copies the selected row.
Copy > All	Copies all column data.

Top Operations

The Top Operations tab contains a sortable list view of all plan operations. The List view is sorted by total cost descending by default so you can immediately see which operations are the highest cost.

Context Menu Options

Option	Description
Copy > Cell	Copies the selected cell.
Copy > Row	Copies the selected row.
Copy > All	Copies all column data.

Query Columns

The Query Columns tab shows exactly how data is accessed for each column used by the query, including the associated operation and estimated rows. This view helps you identify bookmark lookups or index scans occurring due to non-covering indexes. Columns for related operations are grouped together (indicated by a thick separator bar), with the indexes used and bookmark (key / rid) lookup columns highlighted in orange. This helps you see how indexes can be modified to eliminate lookups.

Join Diagram

If there are joins present in the query, the Join Diagram tab is present, showing a visual representation of the joins involved. This is like the database diagram or Query Designer features within the Management Studio, but it only shows the columns that actively participate in joins. Plan Explorer looks past any views and shows the base tables and base columns, making it quicker to analyze queries that involve, for example, nested views.

Parameters

The Parameters tab shows compiled and runtime parameters referenced in the plan, if any exist (otherwise the tab isn't visible). An Estimated Plan only shows the compiled parameter value. An Actual Plan compares compiled values with runtime that can in turn help point out potential parameter sniffing issues.

Expressions

If there are any expressions in the plan the Expressions tab is present. You can see the operator type, the shorthand expression name, and the expanded version of the expression, as well as any other expressions that reference it or are referenced by it, allowing you to quickly see if you're performing the same operations repeatedly.

Context Menu Options

Option	Description
Copy > Cell	Copies the selected cell.
Copy > Row	Copies the selected row.
Copy > All	Copies all column data.

Table I/O

The Table I/O tab is present for Actual Plans. This tab breaks down all the reads from a query by object, making it easy to see where most of your I/O is coming from.

Plan Diagram Icons

The following is a list of the icons, and what they signify in Plan Explorer :

A

Icon	Description
	Adaptive join
	Aggregate
	Arithmetic Expression
	Assert
	Assign

B

Icon	Description
	Build Batch Hatch table
	Bitmap
	Bookmark Lookup

C

Icon	Description
	Clustered index delete
	Clustered index insert
	Clustered index merge
	Clustered index scan
	Clustered index seek
	Clustered index update
	Collapse
	Column Store index delete
	Column Store index insert
	Column Store index merge
	Column Store index scan
	Column Store index update
	Compute Scalar
	Concatenation
	Condition
	Constant Scan
	Convert
	Cursor

D

Icon	Description
	Declare
	Delete
	Deleted Scan
	Distribute Stream
	Dynamic

F

Icon	Description
	Fast Forward
	Fetch Query
	Filter
	Foreign key reference

H

Icon	Description
	Hash Match / Hash Match Aggregation / Hash Match Union
	Hash Match Root
	Hash Match Team

I

Icon	Description
	Index Delete
	Index Insert
	Index Scan
	Index Seek
	Index Spool
	Index Update
	Insert
	Inserted Scan
	Intrinsic
	Iterator / Iterator Catch All

K

Icon	Description
	Key Lookup
	Key Set

L

Icon	Description
	Locate
	Logical Op Distribute Streams
	Logical Op Reparation Streams
	Log Row Scan

M

Icon	Description
	Merge
	Merge Interval
	Merge Join

N -O

Icon	Description
	Nested Loops
	Overlay Batch Mode
	Overlay Batch Mode Parallel
	Overlay Error
	Overlay Information
	Overlay Parallel
	Overlay Warning

P

Icon	Description
	Parallelism
	Parameter Table Scan
	Population Query
	Print
	Put

R

Icon	Description
	Rank
	Refresh Query
	Remote Delete
	Remote Index Scan
	Remote Index Seek
	Remote insert
	Remote Query
	Remote Scan
	Remote Update
	Repartition Stream
	Result
	RID Lookup
	Row Count Spool

S

Icon	Description
	Segment
	Select
	Select Into
	Sequence
	Sequence Project
	Set Function
	Snapshot
	Sort
	Split
	Spool
	Stream Aggregate
	Switch

T

Icon	Description
	Table Delete
	Table Insert
	Table Merge
	Table Scan
	Table Spool
	Table Update
	Table Value Function
	Top
	TSQL

U-W

Icon	Description
	UDX
	Unknown
	Update
	Window Aggregate
	Window Spool