By: Eduardo Pivaral | Updated: 2020-03-10 | Comments | Related: > Query Plans
Problem
Are you a seasoned SQL Server Management Studio (SSMS) user, but want to give Azure Data Studio a try? Or are you new to T-SQL and want a lightweight tool to analyze execution plans? Azure Data Studio (ADS) is a cross-platform tool focused on working with data (not just our beloved SQL Server). Among its extensive features, it provides a great T-SQL editing environment and we can work with on-premises or Azure data and it also includes Git integration.
One thing data professionals do is to analyze their query performance with execution plan, but how do you obtain execution plans with ADS?
Solution
In this article, we will show you how to generate and work with execution plans when using Azure Data Studio.
Installing Azure Data Studio
If you don't already have ADS, you can follow these steps to install ADS and we can start generating execution plans.
Using ADS Brief Overview
In case you are completely new to ADS, this is the basic process to connect to a server.
Once we have the tool up and running you will see a window like this (it can vary from OS or color theme):
To connect to your desired database, use the Connections tab on the left side and connect to the database.
Then open a new query window with the New Query option and you can start working on your queries!
Generating Execution Plans with Azure Data Studio
With ADS, you can obtain estimated and actual e execution plans, the estimated is very straightforward, but for the actual it is not as clear the first time you try to generate a plan.
Obtaining Estimated Execution Plan with ADS
Just mark your desired query and click on the Explain option at the top of the query window.
The estimated plan will show at the bottom of the query.
At the time of this writing, there is no keyboard shortcut on ADS to generate the estimated plan, so we have to stick to the mouse for now, but don't worry, I have submitted a request so it can be added in the future.
Obtaining Actual Execution Plan with ADS
Obtaining actual execution plans is a little bit different and is not intuitive the first time.
Just select your code and press Ctrl+M (Windows users) and we can see this time we obtain the actual execution details.
Another way to do it is to go to the Command Palette (Ctrl+Shift+P or F1) and search "Run Current Query with Actual Plan" option.
Execution Plan Details
In ADS we can navigate to the "Top Operations" tab and one cool feature is that you can order by any column you want. This is a nice easy way to see which operations take the most time and resources.
Saving Execution Plans
In ADS, go to Results tab, then locate the XML Showplan table and select the row containing the XML string.
When you click it, a new window will open and you can save the results with a .sqlplan extension.
If you open an existing .sqlplan file, it will open with just the graphical execution plan. You cannot copy or open the query text and you cannot see the top operations tab on this window. Also, it renders with a white box below the plan as shown below.
Using ADS Extensions for Execution Plans
If the native options are not want you want, you can use the SentryOne Plan Explorer extension which is free and can be installed under the extensions tab.
It allows you to have additional functionalities (like zoom, highlight and operator properties). You have to enable it on your current query with the option at the bottom.
Once you execute the query, the execution plan will generate in a new window.
SSMS or ADS
It depends entirely on your personal preferences and want you need to analyze. This article is not a comparison on which one is better, you can even use both for different circumstances.
Now you know these options are available, and the more you know, the better you can adapt to different scenarios. Also, keep in mind that new features and fixes are made to ADS constantly, so stay tuned to the latest release notes here.
Next Steps
- Check out my latest Azure Data Studio articles here.
- Check more database developer articles here.
- Check Azure Data Studio GitHub repository here.
- Note that on newer releases of the Azure Data Studio, some features can change, or new features can be added.
- You can download the latest Azure Data Studio release here.
About the author
Eduardo Pivaral is an MCSA, SQL Server Database Administrator and Developer with over 15 years experience working in large environments. This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2020-03-10
