PREVIEW : SentryOne Plan Explorer Extension for Azure Data Studio

 Updated August 15th, 2019 for 0.9.6.

Last year, I got together with one of my dev teams here – they call themselves the SQL Injectors – to talk about the possibility of replicating SentryOne Plan Explorer functionality inside of Azure Data Studio. After some discussion, we brought the concept to the leadership team, and set a plan in motion.

There were some complications, as you can imagine; Plan Explorer is written using desktop technologies, and ADS is a whole new world. We had to convert all our .NET Framework code to .NET Core, change our UI code to Node and React, and re-work the entire plan diagram implementation. I'll save all the backstory for another post, because many fun things happened along the way, and we got a lot of help from some great folks at Microsoft – Karl Burtram, Alan Yu, Alan Ren, Charles Gagnon, and Vicky Harp, to name a few. For now, I want to focus on how you get your hands on the preview, what's in it so far (including a few screenshots), and known issues.

How You Get It

In order to access the Plan Explorer extension, you need to create a free SentryOne Cloud account and agree to our EULA. The account gives you access to our support forum, and we can keep you up to date on upcoming releases and new features. Once you have created your account, go to extensions.sentryone.com, where you should see the following link to download the extension:

This will give you a VSIX file that you'll need to "side-load" into Azure Data Studio (Microsoft does not support full hosting for third party extensions in the ADS Marketplace, like they do with VS Code).

First, ensure you meet our requirements:

• Azure Data Studio 1.9.0 or newer (July announcement post)
• .NET Core Runtime 2.1 or newer (Windows | macOS | Linux)
• Connecting to SQL Server 2012 or better, or Azure SQL Database

And recommended system configuration:

• Two or more cores
• 8GB or more of memory
• Operating Systems:
  • Windows (8.1 / Server 2012 R2 or better)
  • macOS (10.13 or better)
  • RedHat (7.6 or better)
  • SuSE (12.0 or better)
  • Ubuntu (18.04 or better)

Then, to install the extension:

• Open Azure Data Studio.
• Select File > Install Extension from VSIX Package. You can't double-click the VSIX file.
• Locate the SentryOne Plan Explorer VSIX file you downloaded and click Install.
• Unless you've dismissed it before, you will have to say yes to a prompt about trusting third party extensions:

  

  If you don't have a new enough version of Azure Data Studio (or you're using an Insiders' build with changes we haven't tested yet), some functionality may not work as expected.

  If you don't have a new enough version of the .NET Core Runtime (or it isn't running), you will get this error message after install succeeds:

  

What's In It

Like the desktop application, the Plan Explorer extension is designed to provide you with richer graphical execution plans for your real-time queries against SQL Server. It is based on a modest subset of functionality; we've started with just the plan diagram, a basic statements grid, tooltips, and access to the XML (so you can open the plan in other tools). We will add more features to the extension over time to try to get you as close to full parity with the desktop client as possible.

To enable or disable the extension

• Click the SentryOne Plan Explorer element in the status bar:

  

• Or press Shift+Ctrl/⌘+P and find Toggle SentryOne Plan Explorer:

  

• Or press Shift+Ctrl/⌘+F5.

To collect an execution plan

• Make sure the extension is enabled.
• In a new query window, establish a connection to a server, and write or paste a query.
• For an Estimated plan, use the Explain button on the toolbar.
• For an Actual plan, use Ctrl/⌘+M.
• A new top-level document will open (titled Plan Explorer) including a grid of statements, one row for each plan-generating statement:

  Statements grid

  • To view the underlying plan XML, click View XML.
  • To view the plan diagram for any statement, click View Plan.

  Plan diagram with default light theme

  Plan diagram with Solarized Dark theme

  • To see the condensed tooltip for an operator, hover over the operator. For the expanded version, which includes more information and "sticks" to the right side of the panel, click on the operator:

  Condensed and expanded tooltips

  • To change any of the diagram properties, such as zoom level, switching between actual and estimated plans, showing costs by I/O or CPU only, or changing line widths to be rendered by number of rows or data size, right-click anywhere on the diagram:

  Diagram options context menu

  • When a missing index recommendation is present, a warning will appear on the tooltip for the root node operator, and a right-click context menu option will be available anywhere on the diagram (which will open the missing index(es) script in a new, disconnected, top-level query window):

    Missing index tooltip warning and context menu option

    Missing index script

New in 0.9.6 – August 15th, 2019

• Added context menu option to switch line widths between number of rows and data size
• Added context menu option to show missing index recommendations in a new query window
• Shifted default API port from 5000 to 5042 to reduce chance of conflict
• Added a setting to customize the local port number in case of conflict:

  

• Moved extension on/off toggle to status bar (thanks for the suggestion, Drew Skwiers-Koballa!)
• Additional diagnostics in the Output panel to aid troubleshooting:

  

• Updated several operator icons (old on left, new on right):
  Assert

  

  

  ClusteredIndexDelete

  

  

  ClusteredIndexMerge

  

  

  ClusteredIndexUpdate

  

  

  ColumnstoreIndexDelete

  

  

  Delete

  

  

  DeletedScan

  

  

  FetchQuery

  

  

  ForeignKeyRefCheck

  

  

  IndexDelete

  

  

  IndexUpdate

  

  

  InsertedScan

  

  

  KeyLookup

  

  

  PopulationQuery

  

  

  RemoteDelete

  

  

  RemoteQuery

  

  

  RIDLookup

  

  

  Sequence

  

  

  Switch

  

  

  WindowAggregate

  

  

  Note that the Key and RID Lookup icon changes already happened in Plan Explorer 19.0.1 (explanation here); the rest of these changes will appear in Plan Explorer soon.

Known issues

• When the Plan Explorer extension is enabled, you will also experience the native Explain functionality provided by Azure Data Studio (Dave Bland explains that functionality here).
• For actual plans, the only runtime metrics available in grids are Duration and CPU (given a modern enough version of SQL Server). Other data will appear in future releases, starting with Reads and Cost %.
• Azure Data Studio currently truncates plan XML output at 2MB (see Issue #6299). So for larger plans and/or larger batches, we might not be able to show all plans. This is more likely to happen when using Explain to generate estimated plans (so just say no to estimated plans <G>).
• We don't have access to the File menu, so we can't open or save plans, and we can only open a single document window named Plan Explorer – which means that, at least for now, you can only show one plan diagram at a time.
• Missing indexes, line width options, and subtree collapse functionality are unavailable in the preview. There are also no layout options, but you can zoom with your mouse wheel or trackpad pinch, and you can pan with two-finger trackpad swipe.
• When Azure Data Studio opens with documents recovered from a previous session, enabling the Plan Explorer extension may not apply to those windows, due to an issue enumerating documents in that state. Until Microsoft releases a fix, toggling Plan Explorer off and then on again should do the trick.
• Theme support is experimental at this point:
  • Not all background colors will render beautifully, and all links are blue no matter what.
  • Alternate icons designed for dark themes are in the works.
  • If you change color theme after rendering a plan, we currently can't capture the event. Interact with the diagram (e.g. right-click) to force it to "catch up."

Get more info; provide feedback

• Product page
• Official product documentation
• SentryOne Plan Explorer / Query Tuning forum
• July release announcement from the Azure Data Studio team
• I am always happy to answer questions or take feedback at abertrand@sentryone.com