Execution Plan in Azure Data Studio


By:   |   Updated: 2020-03-10   |   Comments   |   Related: More > 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):

ADS home page

To connect to your desired database, use the Connections tab on the left side and connect to the database.

ADS connections

Then open a new query window with the New Query option and you can start working on your queries!

ADS new query

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.

ADS estimated plan

The estimated plan will show at the bottom of the query.

ADS estimated plan visualization

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.

ADS execution plan visualization

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.

ADS actual plan

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.

ADS top operatiors

Saving Execution Plans

In ADS, go to Results tab, then locate the XML Showplan table and select the row containing the XML string.

ADS showplan

When you click it, a new window will open and you can save the results with a .sqlplan extension.

ADS save plan to file

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.

ADS open saved execution plan

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.

ADS sentryOne plan explorer extension

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.

ADS activating sentryOne plan exporer extension

Once you execute the query, the execution plan will generate in a new window.

using sentryOne plan explorer extension

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.


Last Updated: 2020-03-10


get scripts

next tip button



About the author
MSSQLTips author Eduardo Pivaral Eduardo Pivaral is an MCSA, SQL Server Database Administrator and Developer with over 15 years experience working in large environments.

View all my tips




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download


get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools