Documentation forSQL Sentry

Plan Explorer Azure Data Studio Extension

Overview

Plan Explorer Azure Data Studio Extension

The SentryOne Plan Explorer Extension for Azure Data Studio (ADS) is a FREE extension that provides you with enhanced query plan diagrams for batches run in ADS. Optimized layout algorithms and intuitive color-coding help you quickly identify operators in your execution plans that are slowing database performance. With the SentryOne Plan Explorer ADS extension, you can:

  • View operator costs (per node) by CPU or I/O
  • Control the graphical plan display with plan diagram context menus
  • Analyze runtime metrics such as duration and CPU
  • Toggle between actual and estimated plans
  • Save ShowPlan XML to send to other users, or open in Plan Explorer
Additional Information: See Aaron Bertrand's blog post, SentryOne Plan Explorer Extension for Azure Data Studio, for release notes and details about the extension, including some known issues and how to provide feedback.

System Requirements

The following is required to install the SentryOne Plan Explorer ADS Extension:

  • Azure Data Studio 1.9.0 or higher
  • .NET Core Runtime 2.1 or higher

Recommended System Configuration

  • 2 or more cores 
  • 8 GB or more of memory

Compatible Operating Systems

  • Windows (8.1 / Server 2012 R2 or higher)
  • macOS (10.13 or higher)
  • RedHat (7.6 or higher)
  • SuSE (12.0 or higher)
  • Ubuntu (18.04 or higher)

Installing the Plan Explorer Extension

Important:  You must create a free SentryOne Cloud account and agree to the End User License Agreement to access the SentryOne Plan Explorer ADS Extension.

Once you've created your account, complete the following steps to Install the Plan Explorer ADS Extension: 

  1. Download the Plan Explorer Azure Data Studio Extension from extensions.sentryone.com.
  2. Open Azure Data Studio, and then select File > Install Extension from VSIX Package.Plan Explorer ADS Extension select File > Install Extension from VSIX Package
  3. Select the Plan Explorer Azure Data Studio Extension VSIX file, and then select Install.Plan Explorer ADS Extension Install Plan Explorer VSIX file
  4. Select Yes on the warning prompt to install the Plan Explorer Extension.
    Plan Explorer ADS Extension select Yes to install
  5. The installation completes.
    Plan Explorer ADS Extension Installed successfully

    Success: The Plan Explorer Azure Data Studio Extension has installed successfully!

Important:  After you have downloaded, and successfully installed the Plan Explorer ADS Extension, the Extension will be enabled in Azure Data Studio by default.

Enabling the Plan Explorer Extension

Important:  After you have downloaded, and successfully installed the Plan Explorer ADS Extension, the Extension will be enabled in Azure Data Studio by default.

You can also enable or disable the Plan Explorer ADS Extension for any desired plan by doing one of the following:

  • Selecting SentryOne Plan Explorer in the status bar
    Plan Explorer ADS Extension select SentryOne Plan Explorer

Open the Command Palette by selecting CTRL+Shift+P and then selecting Toggle SentryOne Plan ExplorerPlan Explorer ADS Extension Toggle SentryOne Plan Explorer Shortcut

 

  • Entering the Keyboard Command Shortcut CTRL+Shift+F5

Note:  After enabling the SentryOne Plan Explorer Extension, the Plan Explorer On notification displays in Azure Data Studio.Plan Explorer ADS Extension Plan Explorer On

Using the SentryOne Plan Explorer Extension

Use the SentryOne Plan Explorer extension to see a detailed query plan for your desired query. Complete the following steps to view an Actual Plan or an Estimated plan: 

Viewing an Actual Plan

  1. Enable the SentryOne Plan Explorer Extension for ADS.  
  2. Enter a new, or load an existing query, and then select Run to collect an Actual Plan and open a Plan Explorer Statement tab.  
    Plan Explorer ADS Extension Run query

3. Select View Plan to open the Actual Plan for the selected query statement.
Plan Explorer ADS Extension Statement tab View Plan

Viewing an Estimated Plan

  1. Enable the SentryOne Plan Explorer Extension for ADS.  
  2. Enter a new, or load an existing query, and then select Explain to collect an Estimated Plan and open a Plan Explorer Statement tab.  
    Plan Explorer ADS Extension select Explain

3. Select View Plan to open the Estimated Plan for the selected query statement.
Plan Explorer ADS Extension Statements tab View Plan

Plan Explorer Statement Tab

The Plan Explorer Statements tab opens automatically when a query is run and the Plan Explorer extension is enabled. The Statements tab separates the query into statements, and provides Duration and CPU information about those statements.

Plan Explorer ADS Extension Plan Explorer Statements tab

Feature Description
Statement An individual statement within the query.
Duration The amount of time in milliseconds it took for the query statement to complete.
CPU The amount of CPU used to process the query statement.
View Plan Select View Plan to open a detailed query plan of the selected statement.
View XML Select View XML to open the ShowPlan XML for the selected statement.

Plan Explorer Query Plan Diagram

The Plan Diagram uses an optimized layout algorithm that renders plans in a much more condensed view, so more of the plan fits on the screen without having to zoom out. You can zoom in and out by selecting CTRL + Mouse Wheel. Optimized plan node labels prevent truncation of object names in most cases. 

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. 

Hover over a Plan Diagram operator to display a truncated tooltip that provides details about the operator. Plan Explorer ADS Extension Plan Diagram tooltips

Select the operator to display the operator's detail window.
Plan Explorer ADS Extension Plan Diagram Operator detail window

Note:  Return to the Plan Explorer Statements tab by selecting the left arrow button.
Note:  The SentryOne Plan Explorer ADS Extension is compatible with any theme in ADS.

Plan Explorer ADS Extension Solarized Dark theme

Plan Diagram Context Menu

Plan Explorer ADS Extension Plan Diagram Context menu

The following context menu options are available:

Option Description
Actual Shows metrics captured during Actual Plan retrieval.
Estimated Shows metrics associated with the Estimated Plan.
Show per Node costs The default view, costs are per node, representing each individual nodes contribution to the total cost.
Show Cumulative costs Costs are cumulative, representing each individual nodes contribution plus the contribution of any of its children.
Costs By I/O—Show costs by I/O CPU—Show costs by CPU CPU + I/O—Show costs by CPU +I/O
Line Widths By Rows — Show line widths by the number of rows created. Data Size — Show line widths by the volume of data used.
Show Color Scale When Show Per Node costs is selected, node costs are highlighted with an appropriate color scale, ranging from a light yellow to red. When Show Cumulative costs is selected, node costs are highlighted with an appropriate color scale, ranging from light blue to dark blue.
Missing Index Details Displays any missing indexes in an untitled document window.
Zoom Various options for zooming in and out. Alternatively, use CTRL + Mouse Wheel.

Showplan XML

Select View XML to open the ShowPlan XML tab for the selected statement.  

Plan Explorer ADS Extension Plan Explorer Statements tab View XML

You can copy and save the Showplan XML, and then open it in Plan Explorer. 

Plan Explorer ADS Extension copy Showplan XML

Missing Index Details

The Missing Index details option is selectable from the Plan Explorer context menu if you query has any missing indexes. Open the Missing Index details for your query in an untitled document window by selecting Missing Index details.

Plan Explorer ADS Extension Missing Index Details

Api Port Settings

The default API port for the SentryOne Plan Explorer ADS Extension has been updated to 5042 to avoid potential conflicts. To change the API port, complete the following steps:

1. Select Plan Explorer ADS Extension Settings Iconand then select Settings.
Plan Explorer ADS Extension Open Settings

2. Select Extensions, and then select SentryOne Plan Explorer.Plan Explorer ADS Extension SentryOne Plan Explorer Settings

3. Enter your new Port Value.
 Plan Explorer ADS Extension Api Port

Note:  Any changes you make to the Plan Explorer Api Port are saved automatically.