Plan Explorer Results

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

SentryOne Plan Explorer Results context menu options

CommandDescription
Show Captured Statements OnlyNote:  Applicable to only .queryanalysis files that originate in the SentryOne 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 StatementsNote:  Applicable to only .queryanalysis files that originated in the SentryOne 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 SubtreeExpands all the child nodes of the selected statement.
Expand AllExpands the entire Statement tree.
Collapse AllCollapses the entire Statement tree.
Edit ConnectionAllows you to edit the SQL Server connection. 
Copy >Copies the cell, row, or all query information to the clipboard. 
Copy Statement to Command TextCopies the selected statement to the Command Text tab, overwriting any text currently in the tab.
Copy All Statements to Command TextCopies all statements belonging to the query call stack over to the Command Text tab.

Note:  When using a column filter that uses a percent value, use a decimal value for the filter. For example, if you'd like the filter to be 50 percent, use 0.5.

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.

SentryOne Plan Explorer Text Data 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

SentryOne Plan Explorer Command Text tab context menu options

CommandDescription
UndoAllows you the undo pasting the recent query. 
CutCuts the selected text to the clipboard.
CopyCopies the selected text to the clipboard.
PastePastes the selected text from the clipboard. 
Select AllSelects the text in the active tab.
Save SQLSaves the text data as a .sql file.
Word WrapWraps text to the current width of the panel.
Edit Connection Allows you to edit the SQL Server connection.

Text Data Panel

SentryOne Plan Explorer Text Data tab context menu options

CommandDescription
CopyCopies the selected text to the clipboard.
Select AllSelects the text in the active tab.
Copy to Command TextCopies the selected information to the Command Text tab, overwriting any text currently in the tab.
Save SQLSaves the text data as a .sql file
Word WrapWraps text to the current width of the panel. 
OpenAllows you to open a new Plan Explorer session.

Plan XML Panel

SentryOne Plan Explorer Plan XML tab context menu options

CommandDescription
CopyCopies the selected text to the clipboard.
Paste Plan XMLAllows you to paste a new plan.
Select AllSelects the text in the active tab.
Save Execution PlanSaves the current execution plan.
Word WrapWraps 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.

Toolbar

Plan Diagram Toolbar

SentryOne Plan Explorer Plan Layout Control

OptionDescription
ZoomScales the Plan Diagram between six percent and 400 percent of its original size. Use CTRL + Mouse Wheel to perform this operation.
FilterFilter nodes from the Plan Diagram based on cost.
StretchStretches the links between Plan Diagram nodes.
FlattenFlattens the space between node levels in the Plan Diagram.
ModeThere are several modes available that change the layout of the entire Plan Diagram.
Link StyleThe Link Style used between nodes in the Plan Diagram.
RotationRotates the entire Plan Diagram 90 degrees.
Auto-FitAutomatically fits the plan in the Plan Diagram tab. 
DefaultsAll Plan Diagram options are reset.
Context Menu

Plan Diagram Context Menu

SentryOne Plan Explorer Plan Diagram context menu options

CommandDescription
Actual CostsShows metrics captured during Actual Plan retrieval.
Estimated CostsShows metrics associated with the Estimated Plan.
Show Per Node CostsThe default view, costs are per node, representing each individual nodes contribution to the total cost.
Show Cumulative CostsCosts are cumulative, representing each individual nodes contribution plus the contribution of any of its children.
Costs ByI/O—Show costs by I/O
CPU—Show costs by CPU
CPU + I/O—Show costs by CPU +I/O
Line Widths ByRows
Data Size (MB)
Show Color ScaleNode costs are highlighted with an appropriate color scale, ranging from a light yellow to red.
Show Nested ProceduresShows nested procedures.
Show Full Object NamesObjects are represented with full object names.
Expand ExpressionsExpands all expressions. 
Copy Output ListCopies the output list to the clipboard. 
Missing Index DetailsDisplays the index details.
Expand All SubnodesExpands all the subnodes.
Show Plan Layout ControlShows the layout controls. 
ZoomVarious options for zooming in and out. Alternatively, use CTRL + Mouse Wheel.
Icons

Plan Diagram Icons

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

A

IconDescription
SentryOne Plan Explorer Adaptive JoinAdaptive join
SentryOne Plan Explorer AggregateAggregate
SentryOne Plan Explorer Arithmetic ExpressionArithmetic Expression
SentryOne Plan Explorer AssertAssert
SentryOne Plan Explorer AssignAssign

B

IconDescription
SentryOne Plan Explorer Build Batch Hatch TableBuild Batch Hatch table
SentryOne Plan Explorer BitmapBitmap
SentryOne Plan Explorer Bookmark LookupBookmark Lookup

C

IconDescription
SentryOne Plan Explorer Clustered index deleteClustered index delete
SentryOne Plan Explorer Clustered index insertClustered index insert
SentryOne Plan Explorer Clustered index mergeClustered index merge
SentryOne Plan Explorer Clustered index scanClustered index scan
SentryOne Plan Explorer Clustered index seekClustered index seek
SentryOne Plan Explorer Clustered index updateClustered index update
SentryOne Plan Explorer CollapseCollapse
SentryOne Plan Explorer Column Store index deleteColumn Store index delete
SentryOne Plan Explorer Column Store index insertColumn Store index insert
SentryOne Plan Explorer Column Store index mergeColumn Store index merge
SentryOne Plan Explorer Column Store index scanColumn Store index scan
SentryOne Plan Explorer Column Store index updateColumn Store index update
SentryOne Plan Explorer Compute ScalarCompute Scalar
SentryOne Plan Explorer ConcatenationConcatenation
SentryOne Plan Explorer ConditionCondition
SentryOne Plan Explorer Constant ScanConstant Scan
SentryOne Plan Explorer ConvertConvert
SentryOne Plan Explorer CursorCursor

D

IconDescription
SentryOne Plan Explorer DeclareDeclare
SentryOne Plan Explorer DeleteDelete
SentryOne Plan Explorer Deleted ScanDeleted Scan
SentryOne Plan Explorer Distribute StreamDistribute Stream
SentryOne Plan Explorer DynamicDynamic

F

IconDescription
SentryOne Plan Explorer Fast ForwardFast Forward
SentryOne Plan Explorer Fetch QueryFetch Query
SentryOne Plan Explorer FilterFilter
SentryOne Plan Explorer Foreign key referenceForeign key reference

H

IconDescription
SentryOne Plan Explorer Hash MatchHash Match / Hash Match Aggregation / Hash Match Union
SentryOne Plan Explorer Hash Match RootHash Match Root
SentryOne Plan Explorer Hash Match TeamHash Match Team

I

IconDescription
SentryOne Plan Explorer Index deleteIndex Delete
SentryOne Plan Explorer Index insertIndex Insert
SentryOne Plan Explorer Index scanIndex Scan
SentryOne Plan Explorer Index seekIndex Seek
SentryOne Plan Explorer Index spoolIndex Spool
SentryOne Plan Explorer Index updateIndex Update
SentryOne Plan Explorer InsertInsert
SentryOne Plan Explorer Inserted ScanInserted Scan
SentryOne Plan Explorer IntrinsicIntrinsic
SentryOne Plan Explorer IteratorIterator / Iterator Catch All

K

IconDescription
SentryOne Plan Explorer Key LookupKey Lookup
SentryOne Plan Explorer Key SetKey Set

L

IconDescription
SentryOne Plan Explorer LocateLocate
SentryOne Plan Explorer Logical Op Distribute StreamsLogical Op Distribute Streams
SentryOne Plan Explorer Logical Op Reparation StreamsLogical Op Reparation Streams
SentryOne Plan Explorer Log Row ScanLog Row Scan

M

IconDescription
SentryOne Plan Explorer MergeMerge
SentryOne Plan Explorer Merge IntervalMerge Interval
SentryOne Plan Explorer Merge JoinMerge Join

N -O

IconDescription
SentryOne Plan Explorer Nested LoopsNested Loops
SentryOne Plan Explorer Overlay Batch ModeOverlay Batch Mode
SentryOne Plan Explorer Overlay Batch Mode ParallelOverlay Batch Mode Parallel
SentryOne Plan Explorer Overlay ErrorOverlay Error
SentryOne Plan Explorer Overlay InformationOverlay Information
SentryOne Plan Explorer Overlay ParallelOverlay Parallel
SentryOne Plan Explorer Overlay WarningOverlay Warning

P

IconDescription
SentryOne Plan Explorer ParallelismParallelism
SentryOne Plan Explorer Parameter Table ScanParameter Table Scan
SentryOne Plan Explorer Population QueryPopulation Query
SentryOne Plan Explorer PrintPrint
SentryOne Plan Explorer PutPut

R

IconDescription
SentryOne Plan Explorer RankRank
SentryOne Plan Explorer Refersh QueryRefresh Query
SentryOne Plan Explorer Remote DeleteRemote Delete
SentryOne Plan Explorer Remote Index ScanRemote Index Scan
SentryOne Plan Explorer Remote Index SeekRemote Index Seek
SentryOne Plan Explorer Remote InsertRemote insert
SentryOne Plan Explorer Remote QueryRemote Query
SentryOne Plan Explorer Remote ScanRemote Scan
SentryOne Plan Explorer Remote UpdateRemote Update
SentryOne Plan Explorer Repartition StreamRepartition Stream
SentryOne Plan Explorer ResultResult
SentryOne Plan Explorer RID LookupRID Lookup
SentryOne Plan Explorer Row Count SpoolRow Count Spool

S

IconDescription
SentryOne Plan Explorer SegmentSegment
SentryOne Plan Explorer SelectSelect
SentryOne Plan Explorer Select IntoSelect Into
SentryOne Plan Explorer SequenceSequence
SentryOne Plan Explorer Sequence ProjectSequence Project
SentryOne Plan Explorer Set FunctionSet Function
SentryOne Plan Explorer SnapshotSnapshot
SentryOne Plan Explorer SortSort
SentryOne Plan Explorer SplitSplit
SentryOne Plan Explorer SpoolSpool
SentryOne Plan Explorer Stream AggregateStream Aggregate
SentryOne Plan Explorer SwitchSwitch

T

IconDescription
SentryOne Plan Explorer Table DeleteTable Delete
SentryOne Plan Explorer Table insertTable Insert
SentryOne Plan Explorer Table MergeTable Merge
SentryOne Plan Explorer Table ScanTable Scan
SentryOne Plan Explorer Table SpoolTable Spool
SentryOne Plan Explorer Table UpdateTable Update
SentryOne Plan Explorer Table Value FunctionTable Value Function
SentryOne Plan Explorer TopTop
SentryOne Plan Explorer TSQLTSQL

U-W

IconDescription
SentryOne Plan Explorer UDXUDX
SentryOne Plan Explorer UnknownUnknown
SentryOne Plan Explorer UpdateUpdate
SentryOne Plan Explorer Window AggregateWindow Aggregate
SentryOne Plan Explorer Window SpoolWindow Spool
Plan Tree

Plan Tree

SentryOne Plan Explorer Plan Tree Tab

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

OptionDescription
Copy > CellCopies the selected cell.
Copy > RowCopies the selected row.
Copy > AllCopies all column data. 
Top Operations

Top Operations

SentryOne Plan Explorer Top Operations Tab

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

OptionDescription
Copy > CellCopies the selected cell.
Copy > RowCopies the selected row.
Copy > AllCopies all column data. 
Query Columns

Query Columns

SentryOne Plan Explorer Query Columns Tab

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

Join Diagram

SentryOne Plan Explorer Join Diagram Tab

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

Parameters

SentryOne Plan Explorer Parameters Tab

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

Expressions

SentryOne Plan Explorer Expressions Tab

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

OptionDescription
Copy > CellCopies the selected cell.
Copy > RowCopies the selected row.
Copy > AllCopies all column data. 
Table I/O

Table I/O

SentryOne Plan Explorer Table IO Tab

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.