Plan Explorer Installation & Overview

Introduction

Plan Explorer is a lightweight .NET-based tool that builds upon and supplements the capabilities of SSMS for execution plan analysis. The following are high value features available:

  • Helpful color, costing, and layout options for plan diagrams
  • Many runtime metrics unavailable in SSMS plans
  • Index scoring and histogram analysis
  • Live query profile with resource charts
  • Wait statistics with links to the SQLskills wait types library
  • Full query call stack for dynamic SQL and iterative queries
  • Session history, versioning, and comments
  • Unique analysis and visualization of deadlocks

In addition to this guide, there are several blog posts and videos available that explain and demonstrate how and why you should use Plan Explorer.

Installation

SentryOne Plan Explorer is a single installation file. Download Plan Explorer here.

Overview

Updates

Plan Explorer checks our servers to ensure you're using the most recent version, and it offers to update your software if there's a newer version available. Do this manually by using Help > Check for Updates.

SentryOne Check for Updates

It also checks each time you launch the application. Disable this automatic check through User Preferences (Tools > User Preferences).

SentryOne User Preferences Tools

SentryOne Disable Updates

File Types

Plan Explorer opens several different file types, including raw plan XML files, SQL plan files, and .QueryAnalysis files, as well as deadlock files and Plan Explorer Session files.

Plan Explorer Sessions

Plan Explorer uses Plan Explorer Session files (.pesession) to manage changes and version history as you refine queries. For more information, see the PE Sessions topic.

Deadlocks

Plan Explorer supports opening deadlock files (.xdl). For more information, see the Deadlock Files topic.

Query Analysis File (.QueryAnalysis)

All information shown in Plan Explorer is saved to the .QueryAnalysis file format by selecting Save. This proprietary, portable format contains the plan XML along with additional information, including actual statements and actual metrics captured by extended events, server-side trace, and other sources.

DBAs can easily save all estimated and actual data for a query to file for later analysis or share it with other team members. For example, a DBA using the full version of SQL Sentry can send a .QueryAnalysis file to a developer for tuning, and all the developer needs is the Plan Explorer utility instead of the entire SentryOne client application.

SQL Plans and Execution Plan XML

Plan Explorer includes support for opening Execution Plan files (.sqlplan) and Execution Plan XML files (.xml).

Viewing Query Plans

There are multiple ways to view a query plan in Plan Explorer:

  • Using the management studio Add-In (bundled with the installer), save an execution plan from SSMS to a .sqlplan file, open the file using the Open toolbar button or File > Open. Raw plan XML files and .QueryAnalysis files are also supported. Right-click a plan in SSMS, and then select View with SentryOne Plan Explorer. This opens the plan(s) in Plan Explorer, providing the correct server/database context for further operations. SentryOne SSMS A
  • In SSMS, right-click a graphical plan, select Show Execution Plan XML, and then copy and paste the plan XML into Plan Explorer. It doesn’t matter which pane is active; the clipboard handler is global and auto-detects the plan XML. This makes it quick and easy to move plans from SSMS into Plan Explorer if you’re not using the SSMS Add-in. SentryOne SSMS B
  • Right-click an existing .sqlplan file, and then select Open with Plan Explorer, or drag and drop the plan file onto the application.
    SentryOne Open with Plan Explorer
  • Retrieve the estimated or actual plan from T-SQL in the Command Text pane using the Get Estimated Plan (CTRL + L) or Get Actual Plan (CTRL + E/F5) button on the toolbar. The connection dialog box opens to connect to a valid SQL Server instance, unless Plan Explorer launched using the Add-in, in which case the connection information is pre-populated. SentryOne Open with Command Text Pane

Retrieving the Estimated Plan

Copy and paste or type raw T-SQL into the Command Text tab, and then select Get Estimated Plan, or use the CTRL + L keyboard shortcut to connect to a server and get the estimated plan.

SentryOne Get Estimated Plan

SentryOne Estimated Plan

Plan Diagram Context Menus

Once you have a plan loaded, right-click anywhere in the diagram area to access context menus that control how the graphical plan is displayed. By default, color-scaling and per node costs are enabled.

SentryOne Context Menu

Actual Plans

If an actual plan loads, actual metrics are shown by default. Toggle the plan diagram back and forth between estimated and actual metrics using the Show Estimated Plan toolbar button. This button is only enabled when an actual plan is loaded.

SentryOne Show Estimated Plan

Generating an Actual Plan

To generate an actual plan, use the Get Actual Plan button on the toolbar, or use the CTRL-E / F5 keyboard shortcut. When generating an actual plan, the query is executed against the server. All the statements in the text data are executed. This allows you to collect actual runtime metrics, such as duration, CPU and reads, which you cannot get from an estimated plan. By design, the actual query results aren't returned to the Plan Explorer interface.

When you generate an actual plan, an execution warning message appears. Disable this warning message through User Preferences (Tools > User Preferences > Warn on actual execution).

SentryOne Get Actual Plan

SentryOne Disable Warn On Actual Execuation

Full Query Call Stack

When an actual plan is retrieved in Plan Explorer, the full query call stack is also assembled. This ability has been part of Performance Analysis in SentryOne for some time, and was also made available as part of the Plan Explorer feature set.

SentryOne Full Query Call Stack

Plan Explorer offers the complete call stack, including nested procedure calls and statements called with dynamic SQL. The complete query call stack is invaluable as it lets you see exactly how all the captured statements are related and where they fit in the query plan. Most importantly, it shows you which statements did the bulk of the work, and which are candidates for optimization.

Wait Stats

When an actual plan is retrieved with related wait stats, the Wait Stats tab is displayed next to the Results tab. The wait type is shown along with any associated wait time or signal time.

SentryOne Wait Stats

The Wait Stats tab helps you in identifying potential resource bottlenecks, including those related to memory pressure, CPU pressure, disk I/O, and networking. For more information about wait types, see the sys.dm_os_wait_stats (Transact-SQL) article.

Note:  For a complete list of the wait types and their locations in SQL Server 2005 through SQL Server 2017, see the SQLskills Wait Types Library.

Note:  Wait Stats Analysis is only supported for queries executed against servers running SQL Server 2008 or newer.

Exception Reporting

If you're working in Plan Explorer and see a small form pop-up, it means we’ve captured an unhandled exception. Enter your name and email address along with brief steps to reproduce the exception into the textbox, then submit it. If the exception seems to pertain to a particular plan, save it to a .QueryAnalysis file and email it to support@sentryone.com.