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
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:
|/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.
c:\Users\mconnors\Downloads>PlanExplorerInstaller.exe /repair /quiet
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.
It also checks each time you launch the application. Disable this automatic check through User Preferences (Tools > User Preferences).
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.
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.
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.
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 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).
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.
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.
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.
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.
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.sentryone.com