Azure Data Studio – Explain Button

One for the first things that jumped out at me in Azure Data Studio was the “Explain” button.  After you make your connection, you will see in the upper right hand corner of the query window the Explain button.

At first I was thinking maybe it is a short cut to help.  Nope, it is actually an easy way to get the estimated execution plan. The key is estimated execution plan. While in SQL Server Management Studio we have been able to easily get both the estimated or actual execution plan.

Just as a reminder, the actual execution plan requires the query to actually run. While the estimated plan will use statistics to generate the plan.  Generally, if the actual and estimated are capture very close to each other, you are more than likely not going to see a difference between the tow.

Now let’s talk about the Explain button.  It will return two things, the XML of the execution plan and a graphical representation of the execution plan.

You will notice in the above image, the Results tab.  Just as in SSMS, this is where you can find the results of the query.  In the image above I simply clicked the Run button and the results were returned.

When you click the Explain button, the look of what is returned will be a bit different.  You will still see the Results and Messages items, but there are now two additional items, Query Plan and Top Operators.

The query plan will be the item code that is selected, revealing the estimated execution plan.  There are two ways to show that it is indeed the estimated plan.  The first is that under the results tab you will not see the results of the query.  Rather, you will see the XML of the plan, no data.  Remember, SQL Server does not execute the query when returning the estimated execution plan.

Another area to look at to show that the plan is an estimated plan rather than actual is by looking at the properties of some of the operators.  Notice in the image below there are a number of Estimated properties but no actual.  If this was the Actual plan, you will see both estimated number of rows and actual number of rows.

Another feature that I like is the Top Operators item.  This does exactly what you expect it to do, return the top operators.  If you have used SentryOne Plan Explorer this is not new to you.  But for the others who have not used Plan Explorer, this will be a welcome feature.  And if you haven’t use the SentryOne Plan Explorer…..you SHOULD. It is a great tool and can be downloaded for free. To make is easy for you…here is a link to download it, Click Here.

What if I want the Actual execution plan and not the estimated plan.  There is not a button for this, but there is a short cut.  Just hit ctrl + m.  This will execute the query and return the actual plan.  As you can see below, we have both Actual number of rows and estimated number of rows.

When running this I did find something interesting.  If I have two queries such as below in SSMS and I capture the actual execution plan, I will see both plans.

SELECT * FROM Production.Product
SELECT * FROM Production.ProductCategory
Both plans returned in SSMS.
This is not how it works is Azure Data Studio.  While the results for both queries are returned as well as the XML for the plan, the graphical plan is returned only for the first query.
As you can see….both statements have the XML of the plan.  However only one graphical plan is returned, for the first query in the batch.
Another item that I noticed that is different in Azure Data Studio is the percent of batch in the plan.  In the image below from SSMS you can clear see which of the two queries was the most costly. In the other image for Azure Data Studio, notice no percent for the statement in the batch.  I really liked using this in SSMS.
I think overall, Azure Data Studio appears to be a pretty cool tool.  Thank you for stopping to visit my blog!!

SSMS Options – Part 1

We all use SQL Server Management Studio on a daily basis.  Although many of the options you will more than likely not change at all.  There are a few that would be good to change to help enhance our experience in SSMS.  This post, as well as a few future posts will go over a number of these options.  The thing to remember when changing options in SSMS, some change will require a restart of SSMS.  I will point these out as I cover the various options.

Obviously, the logical starting point is to go over how we get to the options form.  In SSMS you go to the “Tools” menu, under which you will see the “Options…”menu.

Once you click Options you will see this..

There are many options available that can be changed.  These options will not only change the way SSMS behaves, they can also change how it looks.

When you first look, on the left you will see a tree structure.  If you see an arrow to the left of the menu item, there are more options under that you can see when you click it the menu item.  You can see this in the image below.

As you can see, there are a ton of options.  If you are not sure of the exact name, you can search the options.  In the upper left hand corner of the Options window, you can enter the words you are looking for and SSMS will return all the options that have that word.

 

Color Theme

This can be used to change the over all appearance of SSMS.  With this option you have two choices, Blue and Light.  Blue is the default.  This does not require a restart of SSMS.

Blue

This give a darker look to SSMS. The borders are blue and the headers are blue as well.

Lite

As the name would imply, this turns SSMS into a light colored theme.

 

Track Changes

This option is used to track any changes you have made to the TSQL you are working with.  You can find this option under “Text Editor”.  Changing this does not require a restart of SSMS and is on by default.

When this option is turned on, you will see a yellow bar on the left side for every line you modify.  Once you save the file, the it will turn to green.  Like the two images below.

Line Number

This option is off by default.  Some folks like to turn this one, some prefer it off.  Personally I usually have it off.

When this option is turned on, you will see the line numbers on the left side.  When you copy the code, it does NOT copy the line numbers as well.

 


In order to keep the post kind of short, I will do another post on additional options that you might want to learn about.

Thanks for stopping by my blog and hope you learned something.