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 Context Menu Commands

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 General Query

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 Command Text Panel

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 Text Data Panel

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 XML Panel

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.

Plan Diagram Toolbar

SentryOne Plan Diagram Toolbar

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.

Plan Diagram Context Menu

SentryOne Plan Diagram Context Menu

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.

Plan Tree

SentryOne 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.

Top Operations

SentryOne 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.

Query Columns

SentryOne 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 makes it easy to identify bookmark lookups or index scans occurring because indexes are non-covering. 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 makes it easy to see how indexes can be modified to eliminate lookups.

Join Diagram

SentryOne 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

SentryOne 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

SentryOne 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.

Table I/O

SentryOne 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.