Plan Explorer Azure Data Studio Extension

Overview

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.

Requirements

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)
Installation

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 Extension

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
Select SentryOne Plan Explorer

Plan Explorer ADS Extension SentryOne Plan Explorer On
SentryOne Plan Explorer On
  • Open the Command Palette by selecting CTRL+Shift+P and then selecting Toggle SentryOne Plan ExplorerPlan Explorer ADS Extension Toggle SentryOne Plan Explorer Shortcut
    CTRL+Shift+P and select Toggle SentryOne Plan Explorer
  • 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 Extension

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
Run Query

Plan Explorer ADS Extension Plan Explorer Statements tab
Plan Explorer Statements tab

3. Select View Plan to open the Actual Plan for the selected query statement.

Plan Explorer ADS Extension Statement tab View Plan
Select View Plan

Plan Explorer ADS Extension Query Diagram for Actual Plan
Plan Explorer Query Diagram for Actual 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
Select Explain

Plan Explorer ADS Extension Statements tab
Plan Explorer Statements tab

3. Select View Plan to open the Estimated Plan for the selected query statement.

Plan Explorer ADS Extension Statements tab View Plan
Select View Plan

Plan Explorer ADS Extension Query Diagram for Estimated Plan
Plan Explorer Query Diagram for Estimated 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

FeatureDescription
StatementAn individual statement within the query.
DurationThe amount of time in milliseconds it took for the query statement to complete.
CPUThe amount of CPU used to process the query statement.
View PlanSelect View Plan to open a detailed query plan of the selected statement.
View XMLSelect 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 Plan Explorer ADS Extension Back button button.

Note:  The SentryOne Plan Explorer ADS Extension is compatible with any theme in ADS.

Plan Explorer ADS Extension Solarized Dark themePlan Explorer ADS Extension Dark themePlan Explorer ADS Extension Blue themePlan Explorer ADS Extension Red theme

Plan Diagram Context Menu

Plan Explorer ADS Extension Plan Diagram Context menu

The following context menu options are available:

OptionDescription
ActualShows metrics captured during Actual Plan retrieval.
EstimatedShows metrics associated with the Estimated Plan.
Show per Node costsThe default view, costs are per node, representing each individual nodes contribution to the total cost.
Show Cumulative costsCosts are cumulative, representing each individual nodes contribution plus the contribution of any of its children.
Costs ByI/O—Show costs by I/O CPU—Show costs by CPU CPU + I/O—Show costs by CPU +I/O
Line Widths ByRows — Show line widths by the number of rows created. Data Size — Show line widths by the volume of data used.
Show Color ScaleWhen 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 DetailsDisplays any missing indexes in an untitled document window.
ZoomVarious 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
Select View XML

Plan Explorer ADS Extension Showplan XML
Plan Explorer XML

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

Plan Explorer ADS Extension copy Showplan XML
Copy Show Plan XML

SentryOne Plan Explorer from copied Showplan XML
Open XML in Plan Explorer

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
Select Missing Index Details

Plan Explorer ADS Extension Missing Index Details window
Missing Index Details window

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.