Microsoft has two main tools for querying and managing SQL Server databases in a graphical UI. SQL Server Management Studio (SSMS) is for many people still the default choice; it used to be included with a SQL Server installation, it is the most familiar tool for most SQL Server professionals, it gives easy access to (almost) all SQL Server features, and it’s simply the interface we have been used to for as long as we’ve been working with SQL Server.

The alternative is Azure Data Studio (ADS). This tool seems to be mostly targeted towards developers. A DBA will notice that lots of actions typically associated with their job of database administration have no or very limited support in the GUI, though they can of course still be done with T-SQL statements in a query window. But a developer will rejoice at having a tool that doesn’t require Windows but also runs natively on Linux or macOS, and that connects to almost every data provider instead of just SQL Server and Azure SQL databases.

Plus, notebooks!

Execution plans

Query tuning is, in my opinion, part of the job of both DBAs and developers. Both roles should be able to access the execution plan of a query. If ADS wants to be a serious tool for the serious developer, then it must have serious support for working with execution plans.

Sadly, that has been lacking. So far.

History

There has, indeed, always been support for viewing execution plans in ADS. But I would severely hesitate to call it serious support.

The icons used to represent operators were all different from the icons that people were used to from working with SSMS. That adds needless complexity to the jobs of developers, as well as to trainers. Beauty is, of course, in the eye of the beholder; but I have yet to meet someone who actually prefers the design of the ADS icons over the SSMS icons. A more objective issue I have had since first seeing the ADS execution plan interface is that many operators, including a few very common one, have either a generic icon or no icon at all!

Another issue I had with the ADS display of execution plans is that the icons used up much more screen space. This means far more scrolling is needed when working with larger execution plans, and it’s much more challenging to see the overall picture.

Properties are, of course, the most important part of execution plans. Looking at the icons and the arrows is just the beginning. We need to dive in and check the properties for a better understanding. In SSMS, one can debate the choices Microsoft made towards which properties are exposed in the popup window we see when we hover an operator, but we can at least always open the Properties window to access the full properties list. In ADS, this last option was missing. If a property was not included in the UI design of ADS, there was no way to get its value (short of accessing and parsing the XML representation of the execution plan).

And while we are at it, let’s not forget the bugs. When running a batch with multiple T-SQL statements, the tool would only render the execution plan for the first query. There rest of the execution plans were only “shown” as XML output. That is, of course, totally useless for a serious developer trying to troubleshoot the performance of a stored procedure.

Future

But I am not writing this post to moan about past issues. I am writing this post because Microsoft has made huge improvements to execution plan support in ADS. These are officially still in preview, but they are already available. However, you will need to take a few steps to see these improvements in action.

First, you’ll need to make sure you have version 1.35.0 of Azure Data Studio. You probably already have ADS installed on your system, even if you never wanted it, because it is automatically dumped on your system when you install SQL Server Management Studio. But this might be an older version of ADS. So if you do have ADS installed, use Help / About in to verify whether you are already at version 1.35.0. If you don’t have ADS, or if you are on an older version, then you will need to download and install version 1.35.0.

Once ADS 1.35.0 is installed, start the program and go to the menu choice File / Preferences / Options. Or simply hit the short-cut key combination: Ctrl-, (control + comma). You’ll see some settings and on the top of the screen is an empty text bar labelled “Search settings”. Click there, then paste the following: “Workbench: Enable Preview Features” (without the quotes). You should see a screen similar to the screenshot below.

If the option “Workbench: Enable Preview Features” is not checked, click to check it. There is no separate save action, clicking the box will immediately activate (or deactivate) the setting. You can just close the Settings tab when done.

Next, open a query window and connect to a SQL Server or Azure SQL Database. Type (or paste) a query. Or even a batch of multiple queries. Then, hit the Ctrl-M shortcut, or use the menu to View / Command Palette / Run Current Query with Actual Plan. The query will run, and … you’ll see an execution plan that looks very similar to the execution plans we’ve come to know and love (or hate!) in SSMS.

The icons are all the same design as the icons used in SSMS, so you can now much easier switch between the two tools. Hover your mouse over an arrow and you will see a popup that shows the same selection of properties that you would get in SSMS. Right-click to open the Properties window, and you can access the complete properties list.

The entire look and feel, not only the graphical design but also the user interface and the interactions, is very similar to the execution plan viewer in SSMS. There are some differences, but any professional who is used to one of the tools will be able to work almost seamlessly in the other tool.

Quick facts

The new execution plan viewer in ADS 1.35.0 is officially a preview feature. (That’s why you need to opt in to using preview features to use it). This means that things might change before it goes out of preview and becomes “official”. But even now, after having played with it a bit, I can say I am already quite impressed with it.

If you disable the preview features, you will not be able to use execution plans at all in ADS. The old execution plan viewer, with all its shortcomings and bugs, has apparently already been removed. I will not miss it.

If you want to see an execution plan only (without running the query), then you can use the Explain button on the ADS toolbar. This is equivalent to using “Display Estimated Execution Plan” in SSMS. Note that the execution plan you will get, in either tool, is neither estimated not explained. It’s just the execution plan.

To fetch the execution plan with run-time statistics in SSMS, you have to activate the toggle to “Include Actual Execution Plan”. Once active, SSMS execution plan plus run-time statistics for each query execution until the toggle is disabled again. In ADS there is no way to automatically request execution plan plus run-time statistics for all query executions; you must always use the Ctrl-M shortcut or choose Run Current Query with Actual Plan from the Command Palette. Also, despite the suggestion made by the “estimated” / “actual” terminology used in both terms, I do want to remind you once again that they are actually the same execution plans, just with some additional run-time statistics added.

Finally, there is currently in ADS no functionality that is equivalent to the “Include Live Statistics” option in SSMS. If you want to execute a long running query and watch the execution plan with periodically updated statistics representing work done so far, you’ll need to use SSMS for that.

Conclusion

Azure Data Studio is now, in my eyes, finally a serious tool with serious support for working with execution plans. There will of course always be room for improvement, and I will certainly return to my regular bitching and moaning once I experiment a bit more with this new feature.

But at this time I just want to thank Microsoft for finally giving execution plans in ADS the love they deserve.

Thank you, Microsoft!