Documentation forSQL Sentry

Integrated Plan Explorer Overview

 

Note:  The functionality between the standalone and integrated Plan Explorer is nearly the same. See the following information for more details about Plan Explorer.
Note:  The default value for the Query Collection Maximum Text Length setting is truncated at 10,000 characters. It may be set to a value between 10,000 and 50,000 characters. For more information, see the Adjusting Captured Text Data topic.

Introduction

Plan Explorer is designed to make query plan analysis fast and intuitive. There are several ways to start a new Plan Explorer Session within SQL Sentry. On the Top SQL tab, the View button in the Plan column opens a Plan Explorer Session for the associated completed or running query. Start a new Plan Explorer Session with the New Plan Explorer Session toolbar button or through the File menu.

SQL Sentry Plan Explorer New Plan Explorer Session button

Plan Explorer Sessions (.pesession) are designed to help you manage a historical record as you fine tune queries. By default, a historical entry is generated as part of the Plan Explorer Session during Estimated and Actual Plan retrieval. For more information, see the Plan Explorer Sessions topic. 

Note:  Several of our team members have written about the available features and benefits of using SQL Sentry Plan Explorer. Find all of our Plan Explorer related posts on our team blog.

General Layout Information

The screen and window layout of Plan Explorer is persisted by default, and can be saved to and loaded from a disk. Default horizontal and vertical layout styles are included; the horizontal layout is better suited for shorter queries with wide plans, while the vertical layout is better suited for longer queries. Change the layout by selecting the desired layout button on the toolbar or through the context menu of any section's header.

Entire plans can be saved to and loaded from a file, complete with all statement and plan information. Save a plan by selecting Save on the toolbar or through the File menu. A Save As command is also available from the File menu, allowing you to keep different versions of similar plans.

SQL Sentry Plan Explorer File Save Buttons

Customizable Tab Layout

The Plan Explorer tab layout is completely customizable. Each tab fully undocks from its parent window, allowing you to simultaneously view the Plan Diagram alongside any other chosen query detail tabs. To undock a tab from its parent window, select and drag the tab's title bar. Choose to either re-dock the tab with an existing tab, or float the tab in a standalone window. Use the push-pin to unpin the tab from the immediate docked view, and it becomes auto-hidden when not in use.

Statements Section

The Statements Tree tab shows a tree list representation of the entire query call stack, including all statements, conditional logic structures, looping structures, nested procedure calls, and dynamic SQL calls. Estimated costs (Total, CPU, and IO) and estimated rows are displayed for each statement, and actual costs and rows are shown side-by-side for any statements captured from the Top SQL collection or for Actual Plans generated within the Plan Explorer Session. Any significant differences between estimated and actual rows for captured statements are highlighted, making it easy to spot cases where statistics may be stale.

SQL Sentry Plan Explorer Statement Tree Tab

Each of the three sections are automatically synchronized when you select a statement. This includes all the tabs in the Plan Details section. Use the Statements Tree to quickly find the highest cost statements. Selecting any statement shows the graphical diagram, top operations list, columns list, and tree list view for the statement’s execution plan.

The CommandText tab allows editing of the SQL for Estimated and Actual Plan retrieval.

SQL Sentry Plan Explorer Command Text Pane

Query Information Section

SQL Sentry Plan Explorer Query Information Section

Query Information Tabs

The Text Data tab displays the color-coded T-SQL batch or procedure definition of the plan.

Save the text with the Save SQL context menu command, or copy the text data to the Command Text tab with the Copy To Command Text context menu command.

SQL Sentry Plan Explorer Text Data tab

The Plan XML tab displays the execution plan in XML format.

Save the plan in .sqlplan format, with the Save Execution Plan context menu command.

SQL Sentry Plan Explorer Plan XML tab

The Plan/Query Info tab provides detailed information about the selected query and query plan.

Note:  Information displayed in the Plan/Query Info tab is only available for queries captured by the SQL Sentry Monitoring Service.

SQL Sentry Plan Explorer Plan/Query Info tab

Plan Details Section

When you select a statement in the Statements Section, all the tabs in the Plan Details section are synchronized with the selected statement. To inspect or make modifications to an index, open the index properties window by right-clicking any index operation node in the Plan Diagram tab, or any grid row within the other Plan Details tabs; likewise, create new indexes from a separate context item. When you select an operation in any of the Plan Details tabs it's automatically synchronized. Select Copy, available through the right-click context menu, to copy any of the grid view data in the Plan Details section.

SQL Sentry Plan Explorer Plan Details

Plan Diagram Context Menu

SQL Sentry Plan Explorer Plan Diagram Context Menu

The Plan Diagram tab has an optimized layout algorithm that renders plans in a much more condensed view than SSMS, so more of the plan fits on the screen without having to zoom out. If needed, zoom in and out by selecting CTRL + Mouse Wheel. Optimized plan node labels prevent truncation of object names in most cases. To disable truncation completely so full object names are always visible, select Show Full Object Names from the right-click context menu.

The estimated cost of the operation is displayed above each node for maximum readability. These cost labels use color scaling by CPU, IO, or CPU+IO so highest cost operations are instantly obvious, even on larger plans. CPU + IO is used by default; change this through the Costs By context menu. All costs in the Plan Diagram are shown to the first decimal place.

Through the context menu of the Plan Diagram, choose to show cumulative costs in lieu of per node costs; when combined with color scaling, this feature makes it easy to see which subtrees are contributing most to the plan cost. 

Scale the connector line width by either row or data size with the Line Widths By context menu command. The metric you choose to scale by is displayed above all connector lines within the Plan Diagram.

Plan Detail Tabs

Plan Details Tabs Grid Legend

Icon Description

SQL Sentry Plan Explorer Check grid value
The check mark indicates a value of Yes.

SQL Sentry Plan Explorer Empty grid value
The empty box indicates a value of No.

SQL Sentry Plan Explorer Filled grid value
The filled box indicates a value of Not Applicable. 

The Plan Tree tab is a tree representation of the plan that shows all 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.

SQL Sentry Plan Explorer Plan Tree

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

SQL Sentry Plan Explorer Top Operations

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.

SQL Sentry Plan Explorer Query Columns

The Parameter tab shows all statement parameters with runtime and compiled values.

SQL Sentry Plan Explorer Parameter Tab

When an Actual Plan is captured or generated, the Table I/O tab is shown. 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.

SQL Sentry Plan Explorer Table IO Tab

The Wait Stats tab was introduced as part of the feature set of SQL Sentry 7.2. When an Actual Plan is retrieved with related wait stats, the Wait Stats tab displays next to the Results tab. The wait type is shown along with any associated wait time or signal time. The Wait Stats tab helps you in identifying potential resource bottlenecks, including those related to memory pressure, CPU pressure, disk I/O, and networking.

SQL Sentry Plan Explorer Wait Stats

Note:  Wait Stats are available for queries executed against servers running SQL Server 2008 or newer.

Plan History

SQL Sentry tracks all plan versions for a query over time, so you can easily determine when a plan has changed and caused query performance problems. The plans are the Estimated Plans collected at the time of query execution. For more information, see the Query Plans topic.