Integrated Plan Explorer Overview
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.
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.
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.
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.
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.
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.
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.
The Plan/Query Info tab provides detailed information about the selected query and query plan.
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.
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 |
|---|---|
|
The check mark indicates a value of Yes. |
|
The empty box indicates a value of No. |
|
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.
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.
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.
The Parameter tab shows all statement parameters with runtime and compiled values.
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.
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.
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.