Plan Explorer Installation & Overview

Introduction

Plan Explorer is a FREE lightweight .NET-based tool that builds upon and supplements the capabilities of SSMS for execution plan analysis. The following high value features are 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

Download: Plan Explorer is available for free here.

SQL Sentry Plan Explorer is a single installation file. Double-click the PlanExplorerInstaller.exe file to start the installation.

Command Line Install

If you want to install Plan Explorer through the command line, enter the prompt PlanExplorerInstaller.exe /? to display the following switches:

SQL Sentry Plan Explorer Setup Help

Switches	Description
/install \| /repair \| /uninstall \| /layout	Installs, repairs, uninstalls, or creates a complete local copy of the Plan Explorer bundle in the directory. Note: Install is the default option.
/passive \| /quiet	Passive displays minimal with no UI, while quiet displays no UI and no prompts. Note: The UI and all prompts display by default.
/norestart	Suppresses any attempts to restart. Note: The UI prompts you before restarting by default.
/log log.txt	Logs to a specific file. Note: A log file is created in %TEMP% by default.

Note: Windows may warn you about User Account Control (UAC) permissions being required for SQL Sentry Plan Explorer Setup. This popup will happen in passive and quiet modes. Open Command Prompt with the Run as administrator option to avoid this popup when running Plan Explorer commands during your session.

Syntax example:

c:\Users\jesse.sindelar\Downloads>PlanExplorerInstaller.exe /repair /quiet

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.

SQL Sentry Plan Explorer Check for Updates

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

SQL Sentry Plan Explorer Preferences Check for updates on startup

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. Plan Explorer Session files are completely portable, and contain the entire Plan Explorer session (the individual .queryanalysis files), including versioning. 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 single 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 SQL Sentry 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 SQL Sentry Plan Explorer. This opens the plan(s) in Plan Explorer, providing the correct server/database context for further operations.
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.
Right-click an existing .sqlplan file, and then select Open with Plan Explorer, or drag and drop the plan file onto the application.
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.

Note: Plan Explorer currently supports Windows and SQL Server authentication modes. AD authentication methods are not available.

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.

SQL Sentry Plan Explorer Get Estimated Plan

Note: Get Estimated Plan requests the estimated plan for that query from SQL Server. A valid version of the plan will be retrieved from cache or it will be generated. It does not use the Query Store.

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. Color-scaling and per node costs are enabled by default.

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.

Generating an Actual Plan

Warning: Get Actual Plan executes the T-SQL commands. If they include any DML (i.e. updates, inserts, or deletes), then it will update the database.

To generate an actual plan, use the GetActual 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).

SQL Sentry Plan Explorer Disable Warn On Actual Execution

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 SQL Sentry for some time, and was also made available as part of the Plan Explorer feature set.

SQL Sentry Plan Explorer 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.

SQL Sentry Plan Explorer 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 contact us at support.solarwinds.com.

Search SolarWinds Support