Query Plans in Azure Data Studio

I have long been a fan of Azure Data Studio, but one shortcoming has kept me from truly adopting it: Query Plans in Azure Data Studio. Sure, there was a plug-in you could install. Also, you could use a somewhat truncated version of Plan Explorer, but all I wanted was for SQL Server Management Studio plans to be query plans in Azure Data Studio.

Go and get version 1.35 of the tool. Right now.

DUDE!

You have 1.35 of Azure Data Studio?

Cool. Now, go to the menu bar. Click on “File.” Click on “Preferences”. Click on “Settings”. Now, type the following into the search box: workbench editor enable preview. You should see this:

Check the box below where it says “Workbench > Editor: Enable Preview” just like I have in the graphic above.

Now, let’s open up a query window on a SQL Server database (Azure, RDS, Container, Instance running on spinning rust, whatever). Here’s a sample query:

SELECT so.Description,
       p.Name AS ProductName,
       p.ListPrice,
       p.Size,
       pv.AverageLeadTime,
       pv.MaxOrderQty,
       v.Name AS VendorName
FROM Sales.SpecialOffer AS so
    JOIN Sales.SpecialOfferProduct AS sop
        ON sop.SpecialOfferID = so.SpecialOfferID
    JOIN Production.Product AS p
        ON p.ProductID = sop.ProductID
    JOIN Purchasing.ProductVendor AS pv
        ON pv.ProductID = p.ProductID
    JOIN Purchasing.Vendor AS v
        ON v.BusinessEntityID = pv.BusinessEntityID
WHERE so.DiscountPct > .15;

But you could really run anything you like. Regardless, if it’s a part of a batch, highlight just the one query. If not, just hit CTL-M. You should now see something that resembles this:

That, my friends, is a query plan with runtime metrics (aka Actual Plan). Oh, but there’s more. Notice the tool bar on the right, zoomed in here:

Click the bottom button. You should now see something like this:

That is the full properties page for the SELECT operator. It’s not a truncated list. It’s all the properties, paramter values, query hash, ANSI settings, wait stats, the lot. In short, we have a pretty darned healthy chunk of the functionality of SSMS. I mean, seriously, most of it. Plan comparison is missing, but you’ve got the ability search plan properties, open the XML, the query, a whole bunch of the standard behavior is now available within Azure Data Studio.

Now, it is a preview. So, all the functionality that’s ultimately going to be released might not be there. I’ve also noticed a few quirky behaviors (please do, report anything you find to Microsoft so they’ll know about it and will improve this), but overall, I’m going to come out and say it.

We have functional query plans in Azure Data Studio!

Conclusion

I know it’s hard to keep up with this stuff. Heck, I didn’t know about this until Erin Stellato pointed it out for me (thanks Erin). There’s even new functionality that’s not available in SSMS. I’d tell you about it, but then what would next week’s blog post be? However, for me, this is the single biggest hurdle that was keeping me from using ADS for all my development. I think, if you’ve looked at ADS and wasn’t impressed, it might be worth another look.

There’s one more, smaller, hurdle. I really need a good way to consume Extended Events like the Live Data Explorer in SSMS. Once that’s in ADS, I might largely walk away from SSMS.

Anyway, Query Plans in Azure Data Studio is a big deal. I thought I’d share it with you.