ADS – Server Groups

Azure Data Studio has a ton of great features. Of course some I like more than others, but Server Groups is something I really like.  The team I am currently part of manages almost 200 production SQL Servers.  However we do find ourselves working on Dev and QA servers as well, greatly increasing the numbers of servers where could potentially work with.  This greatly increases the likelihood of making a mistake and running the code on the wrong server.   However, even with just a few servers, it would be nice to be able to group the connections to make it a bit easier.  Server Groups are not new, it has been in SQL Server Management Studio for quite some time.

Create a New Server Group

Creating a new server group is pretty easy.  When in the servers window, float your cursor in the upper right corner and three buttons will appear.  These buttons are, from left to right, New Connection, New Server Group and Show Active Connections.  The middle one is the one we want.

Once you do, a very simple window will open up.  This window may appear on the right side of your screen, which is by default where it will appear.  To create a group you only need three bits of information, the group name, group description and color.

Once you click “OK”, you will now see your server group.  You will see the description when you float your cursor over the group.

Now that we have our server group, it is time to add a connection to that group.  This is pretty easy as well.  When creating a new connection, one of the properties you will be asked to fill out, although not required, is the Server Group.

What if it is an existing connection I want to move to the new group?  This is even easier.  When in the connections window, simply drag the connection to the group header that you want to move it to.

Add a Server Connection to a Group

Now that we have the group, we can begin to add server connections to the group.  This can be done simply by right clicking on the group and then clicking, New Connection.  This will open the New Connection window with the server group already filled in.

If the connection already exists and would like to add it to the new group.  All you need to do is click and drag the connection to the header of the group.

Changing settings of an Existing Group

If you attempt to right click on a group, you will get three options.

      • New Connection
      • Edit Server group
      • Delete group

As mentioned earlier, the New connection will take you to the new connection window with the server group already filled in, although you can still change it if you like.

Edit Server group will take you to the same window that you used to create the group.  This allows you to change the Name, Description and color.

However, what if the setting I want to change isn’t in the list.  For this you will need to go to Settings.  This can be found under the File menu.

File —> Preferences —> Settings

When you are there, type group into the search box and a number of settings will appear. The ones we are looking for are under Data.  As highlighted below.

Notice there are four settings.  Of which, only two can be modified here.

Server Group: Auto Expand – will expand the server groups automatically

SQL: Tab Color Mode – This will change the color of the tab to the color of the server group it is in.  I complete another blog post on the details of how this works and how to change it.  The post can be found here.

That leaves Server Group: Colors and Data Source: Connection Groups.  These will need to be modified in a much different way.  These will involve editing the settings.json file.

The settings.json file can be found here:

C:\Users\daveb\AppData\Roaming\azuredatastudio\User

According to Microsoft’s web site the file can be found in this location:

%APPDATA%\azuredatastudio\User\settings.json

Although knowing the location is good, we really don’t need it.  Azure Data Studio provides a link that will open the file in ADS so we can modify it.

Reading the Json File

Let’s take a few minutes to go over how to read the json file.  While this is not a json post, knowing how to read what you see is important.  We will discuss only the two settings that are relevant to this post.  While reading this, please keep in mind I am not a json expert. Jim Dorame has a really nice SQL Saturday presentation titled “Json for the Data Mortals” that is very good.  He presented it at the SQL Saturday in Madison, WI this past year.  The presentation can be found here.  The image below is a screenshot of what you might see if you clicked either one of the links in the settings window.

If you look above, you will see these []. These define an array.  In this case the array is named: dataource.connectiongroups.  Under that you will begin to see these:{}.  These will define each object within the array. In this case there are four items.  Finally within each object you can find the properties.

Notice that each object has an ID as well as the ParentID.  The ParentID can be used to create a hierarchy of the server group.  This could be useful in many ways.  For example, you have a ton of SQL Servers, some in North America and some in Europe.  You could create a top level group named Produciton and then under that of two subgroups, North America and Europe.  Similar to what you see below.

Now let’s take a quick look at the Json.  In the image below, you will notice that the parentID for the North America object is the same as the ID for the Production object.  If you wanted to move the North America group to another top level group, you will need to find the ID of that new group and paste it into the ParentID setting of the North America group.  When you exit out of the settings.json file, you will be asked to save the file.  Of course you will want to save it.  If you do change it, keep in mind that the setting won’t take effect until Azure Data Studio is restarted.

Another property you may want to change is the group color.  When creating the server group, you only have a few options.

Well, what do I do if either I don’t like the colors or I have more groups that colors.  Again, this is where the settings.json file will come into play.

For each group you will see a color setting.  All you will need to do is find the hexadecimal number for the color you are wanting to use and place it into this property.  Here is a few good links, HTMLColors and Color-Hex.

Let’s say I wanted a group to be purple.  I could find the hex number, which is #800080 and paste it in the settings.json file as you can see below.

After restarting Azure Data Studio, this is what you will see, North America is now purple.

Now you have all the information to create groups to help you manage you servers.

Thanks for visiting my blog!!!

 

 

 

 

ADS – How to Mark a Tab as Production or QA

Have you ever been in an environment where you worked with both DEV\QA servers and Production servers?  Of course you have, we all have.  However, being in such an environment brings a certain level of risk.  We all try to be very careful to run any code we are asked to run on the correct server and in the correct environment.  Despite all of our checks and our Due diligence, we are still human and do make a mistake from time to time.  Azure Data Studio has a nice feature that will allows us to change the color of tabs based on the environment the server is in.  The image below is an example of what it would look like.

Setting this up can easily be done in two steps, creating Server Groups with different colors and changing the tab color property in Settings.

Create a New Server Group

Creating a new server group is pretty easy.  When in the servers window, float your cursor in the upper right corner and three buttons will appear.  These buttons are, from left to right, New Connection, New Server Group and Show Active Connections.  The middle one is the one we want.

Once you do, a very simple window will open up.  This window may appear on the right side of your screen, which is by default where it will appear.  To create a group you only need three bits of information, the group name, group description and color.

Once you click “OK”, you will now see your server group.  You will see the description when you float your cursor over the group.

Now that we have our server group, it is time to add a connection to that group.  This is pretty easy as well.  When creating a new connection, one of the properties you will be asked to fill out, although not required, is the Server Group.

What about if it is an existing connection?  This is even easier.  When in the connections window, simply drag the connection to the group header that you want to move it to.

Tab Settings

Now that we have our groups, it is time to set Azure Data Studio up so the query tabs will match the group color, similar to the image below.

To do this we need to change a property.  The property can easily be found by first going to Settings.  This can be found under the File menu.

File —> Preferences —> Settings

Once there, do a search on “Tab Color” and the proper setting will be returned.  The name of the setting is SQL: Tab Color Mode.

In the drop down box there are three options.

      •  Off
      • Border
      • Fill

Off is what it is…Off.  Border will place a colored line that matches the group color.  Fill turns the entire tab the color of the group.  If you change the setting, you should restart Azure Data Studio.

This is what the Border setting will look like.  Notice that there is a line across the top of the tab.

Now we have an additional check we can make to help us assure we are in the proper environment.

Thanks for visiting by blog!!!

 

 

 

 

 

 

 

 

Azure Data Studio – Server Reports Extension

This is my fourth blog post on Azure Data Studio extensions.  Of the three I have completed a post on, all of them I found to be very useful.  The Server Reports extension is no different.  As the name implies, it is for reporting on the server.  This extension is currently in preview mode, so things could change.  According to the documentation, this extension returns information about the following items:

        • DB Space Usage
        • DB Buffer Usage
        • CPU Utilization
        • Backup Growth Trend
        • Wait counts

To find the extension search the extensions on “Server Reports”, at the time of this post, this is the only extension that will be returned.

To install, simply click the Install button.  This is a very fast install and does require a restart of Azure Data Studio.  To use the extension, you will need to navigate to your connections by clicking the button that is in the green box below.

Once there, right click on the database connection you would like to review and go to “Manage”.  Once you do, you will see along the top extensions that you have installed.  However, you will only see relevant extensions, mostly extensions for monitoring or configuration.  I currently have the PowerShell extension installed, however it really isn’t a monitoring or configuration reporting extension, so it does not appear in the list of options.

What I found interesting is that if your connection points to the default database you will not see “Server Reports” as an option.

If you see this, you will need to expand the tree, right click on a database and go to “Manage”.  Then you will see Server Reports as an option.  Once you click on Server Reports you will see some pretty useful information.

Starting with what you see under the Home option. You will see two buttons, the top one is for Monitoring and the bottom one is for Performance.

The top one, Monitoring is already selected by default and returns four charts.  One for each of the following items.

          • DB Space Usage
          • DB Buffer Usage
          • CPU Utilization
          • Backup Growth Trend

When you click the “Performance” button you will see a chart returning information about waits.

If you look at all the charts, you will see an ellipsis in the upper right corner.  For all the charts when you click this you will see two options, run query and refresh.  However, some charts may have additional options.  The Wait chart above has a third option, Show Detail.  Which is what is below.

The thing I really like is that one of the options is Run Query.  Not only will this allow me to view the data in a grid format, it will allow me to see the actual query what was used to pull the information.

I really like this extension, I think it returns some useful information.  However, I think I like it more for the potential rather than for what it current returns.  Since this extension is still in preview mode, my hope is that there will be more counters added as time goes on.

Here are the links to my other blog post on Azure Data Studio.

Azure Data Studio Introduction

How to View Agent Jobs in Azure Data Studio

Azure Data Studio – SentryOne Plan Explorer Extension

Azure Data Studio – Explain Button

Azure Data Studio – Searching Extensions

Azure Data Studio – SSMS Keymap

 

Thanks for visiting my blog!!

 

 

 

Azure Data Studio – SSMS Keymap

One of the key board shortcuts I use quite often is ctrl + shift + u, which changes all the selected letters to upper case.  However, when I tried this in Azure Data Studio it did nothing. At first I was disappointed but then was very quickly relieved when I found the SSMS Keymap for Azure Data Studio Extension.  This extension will bring many of the keyboard short cuts into ADS, just as they are in SQL Server Management Studio.

In case you haven’t worked with Azure Data Studio and extensions, they are kind of like a snap in for ADS.  Once you have ADS open, to install it you will need to open the extension window.  You can do this either by clicking Extension under the view menu or click the button that is highlighted below.

When you finally arrive at the extension window, you will need to look for the SSMS Keymark extension.  It will looks similar to below.  If you look carefully you will see that this is a third party extension and not written by Microsoft.  This was developed by Kevin Cunnane.  When look at the GitHub to download the file, it looks like Kevin may work for Microsoft.  Although Microsoft’s name is not on this extension.

Click the install button and you will be taken to the GitHub location where you can download the .vsix file.  Place this in a location that it will not get deleted or overwritten.  Open Azure Data Studio, under the File menu you can click on the “Install Extension from VSIX package” item and navigate to the location you place the file.

When the install begins you may see this:

Click Yes and the install will begin.  This extension installs very fast so not much waiting.  Unlike some extensions, this one does not need a restart of Azure Data Studio to work properly.

This is a short post, but I plan on doing more posts on some of the different extensions.

Thanks for visiting my blog.

 

 

 

 

 

Azure Data Studio – Searching Extensions

As time goes on I would think that there will be more and more extensions for Azure Data Studio that we will be able to utilize.  While that is going to be great, it will also create a challenge, finding the proper extension.  This is where the searching capabilities come in handy.

Just as a reminder, to get to the extensions, all you have to do is click the button that is highlighted below.

Let’s take a quick tour of the extensions window.  There are three groups that you will see, Enabled, Recommended and Disabled.  To the right of the header for each group will also see the number of extensions that are in the group.  There is also a search box, we will cover that a bit later.  You may notice that some extensions have a star in the upper left corner.  When you see this, that extension is being recommended by Azure Data Studio. These are the extensions that you should really consider installing.

Above the groups there is a search box.  Once you start typing Azure Data Studio will begin to filter the extensions based on what you type. For example if I am looking for extensions that can help me manage SQL Server Agent jobs I could type the word “jobs” in the box.  The extensions will then be filtered as you type.  Notice below there are two extensions that have “jo” somewhere in the name or description.  In the example below, Palenight Theme is returned by cause of the provider name, JoseRocha.  “Jo” is obviously in the first name.  The second extension, SQL Server Agent is returned because “jo” is in the description.  Microsoft has made this type of searching very easy.

In the upper right hand corner of the extensions window you will see three dots, if you click on that you will see a number of predefined search criteria.  The first group in the context menu is about searching.  However, there is more you can do here.  You can also install extensions, enable and disable all extensions and check for updates to extensions.

Since this post is about searching we will focus our attention on the top portion of this menu.  I think the names do a pretty good job of describing what they are going to search on, so no need to really go into that.  However, when you do pick on the options, in the search box you will see an entry that starts with an @.  This is the criteria Azure Data Studio will search on.  In the example below I wanted to see only the extensions that are installed.

You really don’t need to use the menu if you don’t want to. You can type the @ into the search box and a number of options that are not available in the above mentioned menu.  There are a number of options that are not in the menu.  Such as @builtin, these are the extensions the obviously come installed as part of Azure Data Studio.

Notice that in addition to placing a filter on the extensions, you can also sort the extensions by a number of columns.

If you pick @builtin, you will see that as of the writing of this post, there are 32 extensions installed.

If you click one of the builtin extensions, many of them will have this warning in the documentation for the extension.

I am hoping in the future there are going to a large number of extensions we can pick from.  Searching can cut down on the time for us to search for an extension that works with a particular area, like SQL Agent jobs.  It can also help us identify what extensions are in need of an update and which extensions are install or disable.

Thanks for visiting my blog!!

 

 

 

 

Azure Data Studio – SentryOne Plan Explorer Extension

A few days ago I completed a post that hopefully provided information of what the Explain button is and why you might want to use it.  This post is a continuation of the same general topic, execution plans.  As I prepare to present my new session on Azure Data Studio I will be completing several blog posts on some of the great extensions that are available. Many of us have used the SentryOne Plan Explorer application and have enjoyed the ease at which it integrated with SQL Server Management Studio.  The extension for Azure Data Studio has similar ease of integration with the added benefit of addressing a few of the challenges that currently exist in ADS.  While this extension is still in preview, it can be very useful.

In case you are not familiar with an extension in Azure Data Studio, think of it as simply something that extends the functionality of Azure Data Studio.  There are a number of extensions that cover a wide range of topics.  These topics include Agent Jobs, performance reports, PowerShell and the topic of this post, SentryOne Plan Explorer.  Even though the extension is currently in preview, it already has many great features that can easily make our lives as a DBA better!

First things first, let’s talk about how to install the SentryOne Plan Explorer extension.  Once we have opened Azure Data Studio we will need to open Extensions by either clicking the item on the red box below or go to Extensions under the view menu.

Once there you will need to find the Extension for SentryOne Plan Explorer. It will look like what you see below.  One thing I would like to point out, the star in the upper left corner.  When an extension has this, that extension is being recommended by Azure Data Studio.

When you click the Install button you will be redirected to the SentryOne website.  On this page look for this,  ” Visit extensions.sentryone.com” link just a little bit down on the page.  Once you click the link you will be asked to login to the SentryOne site. After logging in, find the “Download Plan Explorer Extension” you will be asked to download a .vsix file.  This is the file you will need to add the extension to ADS.  Place this file in a location of your choosing and then click the “Install Extension from vsix Package”.  My suggestion is to put the file in a location that is easy to get to and won’t get over written or deleted accidently Locate the file and click Install.

If you have the Extension window open while it is installing you might see a small bar moving from left to right.  This will continue until the extension is fully installed.  Once it is installed you will see a popup in the lower left corner of ADS stating the install is complete.

After a quick restart of ADS, you are ready to utilize the SentryOne Plan Explorer extension.  In the lower right corner you will see a new item.  This will make it very easy to turn the Plan Explorer On and Off.  If by chance you are running queries and don’t want to see the execution plans, just click that to toggle it to off.  Now you will not see the plans.  The best part of this is that it does require me to restart Azure Data Studio like I would have to if I disabled the extension.

So now we have it installed, what’s next.  Well…run a query!  When you do a new tab will open up and you will see something like below.  When you click that new tab you will see this grid.  There will be one row for each statement in the batch.

This grid has some great information. The meat of the grid is to the far right.  You will see two links, one for the graphical plan and the other the XML.

When you click the “View Plan” link, the plan will open up in another tab.  Here you will see the familiar SentryOne look to the operators in the plan.

Here is the same plan using SentryOne Plan Explorer.  Very few changes to the look and feel of the plan diagram, which I think is a great thing.

 

You may notice the arrow in the upper left corner, this will take you back to the grid mentioned earlier.  You can still move the operators around by clicking and dragging them to another location as in the image below.  Also the selected operator still has the box around it.  This is another feature of Plan Explorer that I have always liked.

Another change you will notice pretty quickly is the differences in the properties displayed when you float your cursor over an operator. Initially you may think that a bunch of important properties were left out.  They weren’t left out as much as they were moved.

You will get the above pop ups when you float the cursor over the operator.  However, if you click on the operator, another larger popup will appear on the right side of the screen. If you compare the properties below and the properties above from Plan Explorer you will see that all the same properties are there.  What I like about what SentryOne has done, it allowed for a smaller popup when you float your cursor over the operator allowing for a quicker review of the properties.

 

In Plan Explorer, we are used to right clicking in the plan and seeing the menu on the right below.  You can still right click on the plan, however there are fewer properties displayed.  The biggest thing here is that the Zoom feature is still present.  The zoom slide bar we are used to at the bottom of the screen isn’t available.  I just need something that allows me to zoom in on the larger more complex plans. I really am more concerned that I can zoom if needed, not necessarily how it can be accomplished so although the slider makes it easier to zoom I can live with this.

When in Plan Explorer you may be used to see this. It is basically a number of sliders that control various aspects of how the plan is displayed.  These can be very useful, but the one I have used far more than the others is Zoom.  Sometimes the plan is very complex and have a zoom slider is a very simple way to zoom in on a particular part of the plan.  Even though there is still a way to zoom, I really liked this feature.

When I am working in SSMS and I want to save the plan, I just right click and save plan.  It will be save with a .sqlplan file extension.  Plan Explorer is only different in that the saved file will have a different file extension, pesession.   I have saved many plans over my career for a number of reason, to review later or to send to someone come quickly to mind.  I clicked on Save As under that file menu and nothing happened.  I tried SaveAs and Save both.  Same result.  This could be more of a limitation of Azure Data Studio than the SentryOne extension.

Even though the plan doesn’t appear to be something we can save, we can click on the “View XML” link and then save the XML.  Then find the file and change the extension to .sqlplan.  It can then be opened up in either SSMS, SentryOne Plan Explorer or Azure Data Studio.  I like that we have three options to view a plan.  Each has it’s pros and cons.  My plan is to use Azure Data Studio and the SentryOne extension because it keeps me in one application rather than two.

I will have all three applications installed on my computer.  Although the Azure Data Studio extension is not as robust as Plan Explorer, it can still be very useful. Even though this extension is still in preview, there are many things I like about it and I would highly recommend that you plan on getting this one if you are going to be using Azure Data Studio.

 

Thanks for visiting my blog!!!

 

How to View Agent Jobs in Azure Data Studio

As a DBA creating and managing SQL Server Agent jobs is an essential part of our day to day activity.  We have always done this via SQL Server Management Studio or Transact SQL.  Both of these are still great options for managing jobs, but what about Azure Data Studio.  Unfortunately, the management of agent jobs is not part of the native functionality of Azure Data Studio.  If you look at the screenshot below you will see that the SQL Server agent is not available.  So does this mean that Azure Data Studio isn’t the proper tool for the management of agent job?  The answer to this question is a classic DBA answer, it depends.  If you do not install an extension you will not be able to manage jobs.

Before going any further let’s take a few minutes to go over what an extension is.  As the name might imply, it extends something.  In this case Azure Data Studio has it’s functionality extended to do something that is normal cannot do.   An example of this would be the ability to manage SQL Server Agent jobs.  Since Azure Data Studio requires an extension to manage jobs, what extension would need to be added?  The answer is the Admin Pack for SQL Server extension.  There is a SQL Server Agent extension as well, this is part of the Admin Pack for SQL Server. While this extension does more than just job management, we will focus mostly on how manage Agent jobs using Azure Data Studio.

Here is a list of some of the functionality of the Admin Pack for SQL Server extension.

    •   Agent Job management
    •   Import file wizard
    •   Deploy and extract dacpacs
    •   Deploy and extract bacpacs
    •   Browse Extended Events.
    •   Other functionality as well

As stated earlier, we will need to install the Admin Pack for SQL Server extension.  So where do we get it?  Since it was created by Microsoft, you will be able to get it right from inside Azure Data Studio.

Before installing the extension, we should probably check to see if it is installed already or if it has been disabled. If you click the icon in the green box below, the extension Marketplace will open up.  If you see the Admin Pack for SQL Server under the Recommended block, it is not installed.  If you see it under Disabled, the extension has been disabled.

List of disabled extensions can be found in the following location. Notice that the extension has been greyed out.

Another way to determine if the extension has been installed is to see if the SQL Agent appears as an option when you are managing the server level.   As in the image below the Agent will be present when managing the server.  If you do not see SQL Agent, it means either it is not installed or has been disabled.  If it is not installed you can go to the MarketPlace and install it from there.

Now that we have confirmed the Agent extension is not only installed, it is also enabled, we are ready to manage the jobs on the server.  Let’s do a quick inventory of what a typical DBA would do with jobs.  All of these can be completed with Azure Data Studio, however there just a few minor limitations that will be discussed later in this post.

      • Manage jobs history
      • Manage operators
      • Manage alerts
      • Manage proxies

To get to the Agent job information in Azure Data Studio, you will need to right click on the server and go to “Manage”.  As seen below.

Once you do, you will not only see the SQL Agent but also other Extensions you may have installed in Azure Data Studio.

In the above image, you will see that not only do I have the Admin Pack for SQL Server extension installed, but I also have the extension for sp_whoisactive and a few others.  All of which I would recommend that you install.

When you are finally in the proper place to manage the jobs, you will see the same items you will see when using SQL Server Management Studio.  You will of course see the job name, last run, enabled status, category and a few others.  These are the same items we have been working with for years in SSMS.  In the green box in the image below you will be able to click each item and you will be taken to the management area for jobs, alert, operators and proxies.

As you can see above, you can sort by any column you would like and it also displays information about the failure if the last execution was a failure.  In addition to sorting, when you click the down arrow in the column header, you will also have an option to filter the column, similar to Excel. In the red box above is something that is pretty cool. Each bar represents one of the last five job executions. If you click on the of bars, you will be taken to the details of that execution. The image below is an example of what you will see.

When looking at the jobs you will see the button we can use to create a new job. When creating a new job, all the same information is requested so it is pretty easy to figure out.

There are a few differences though when creating a new job in Azure Data Studio.  Starting with job step type.  When working in Azure Data Studio you don’t have the three BI stack related types.  The image below shows the job step types for both SQL Server Management Studio on the left and Azure Data Studio on the right.

Another difference is the creation of new job schedules.  This can be done using SSMS, but can not be done with Azure Data Studio.  You can get use existing shared schedules, just can’t create new ones.

Moving away from jobs, alerts can also be created and modified utilizing Azure Data Studio.  All the same information can be entered, however there is one difference.  When creating an alert you are asked to chose an alert type. When using SSMS you will have three options:

      • SQL Server Event alert
      • SQL Server Performance condition alert
      • WMI event alert

When using Azure Data Studio, you only have one option, SQL Server Event alert.

While there are a few limitations, Azure Data Studio is still a very good tool.  The few limitations in Azure Data Studio for Agent job management can easily be over come simply by using SQL Server Management Studio.

Thanks for visiting my blog!!

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!!

Azure Data Studio Introduction

I recently downloaded and installed Azure Data Studio and so far it looks pretty good.  As I learn more about it I blog about the features and what is different from SQL Server Management Studio.  Azure Data Studio is a free offering by Microsoft and the install is very simple.

Once installed, you will find the menu item in a slightly differently location.  You will not find it under any of the SQL Server related groupings.  As you can see below, there is a new menu grouping, “Azure Data Studio”.  Under this is where you can find the menu item to launch the application.

Once you open Azure Data Studio obviously you do not have a connection to any data source.  At this point you will asked to establish a connection.

You are asked to provide much of the same information you would in SSMS.  This includes server name, type of authentication and well as the database.  You can also provide a Server Group if you like.  This provides nothing more than a logical grouping of servers for easier management.

Once you connect you will then see the dashboard, similar to below. Here you are able to confirm the connection information, see a list of all the objects in the database,

Here you will see a list of all the objects in the database.  You will also see that there are widgets.  The default widgets are to perform such tasks as database backup, database restore, new query and new notebook.  The first three of these have been around for years.  What might be new to some is the notebook.  We will get into that later in this post. This dashboard also includes information about the connection including the compatibility level of the database.

If you click the server name in the breadcrumbs across the top as below.  You will see a few widgets that apply to the server level.

As in the screenshot below, you can see the backup status of all the databases and the currently size.

If you click the upper left corner of the backup status, the current state of all the databases will be returned.

One of the biggest reasons of why we might be using Azure Data Studio is to read data.  This is very similar to SQL Server Management Studio.  Click the “New Query” item on the dashboard or New Query under the File menu.  When you do it is similar to SSMS.  There are tabs across the top and you can also change databases.  Both of these are the same as SSMS, although the database list is in a slightly different location.  You will see the connect\disconnect button and change connection. These do exactly as you would expect them to do.

Results and Messages are still there, just as in SSMS.  Now let’s take a quick look a few of the things that are different than SSMS.  The most obvious difference is the “Explain” button. This will simply return the estimated execution plan of the query you are looking to run.

When you click the “Explain” button you will see what is above.  The query plan and messages as well as the Top Operators.  The image below is what you will see on the Top Operators tab.

You might think that the results table would have results in it.  The answer is yes and no.  It does return results, just not data. You will find the XML of the Execution Plan.  If you click it, a new tab will open with the XML in it.

Another item that is different is on the far right of the results tab, as below.

The top four buttons are to save the data.  In order, they are for CSV, Excel, Json and XML.  The bottom two are for visualizing the data using charts.

Now we will move to the upper right corner.  The icon in the box below is for connections.

This is where we can add new connections.  In the upper right corner you will see three buttons.  The farthest on the left is the new connections button.  The middle button is for creating server groups.  This is what you see below in the red bar.

 

When adding a server group you are asked only to provide a group name, description and a color.  When you create a new connection, you will be able to pick a server group to add the new server to.

All the items covered above are also in SSMS in some form or another.  What is really cool about Azure Data Studio is something that cannot be found in SSMS.  That is SQL Notebook.  So far I am a big fan of this new feature.  It basically allows you to store notes and executable T-SQL code in the same document.

To add a new Notebook, you can find the Add Notebook item under the file menu.  There are two types of items that can be added to a notebook, code and text.  To add either one, you can click the buttons in the upper left or the links in the middle.

When the Text is clicked you will then have the opportunity to enter notes.  Below is an image of what you will see. It looks like there are duplicate lines, but in reality there is not. Once you click away from the note, the top one will disappear, leaving on the note.

If you click Code, you will then be able to enter T-SQL code just as you would in SSMS.

Notice the icon on the left side that looks like a play button.  Well that is exactly what it does, it runs or the query.

In the image above, you can see the text as well as the results of the query.  Once you are done with the results, you can clear them by clicking the ellipsis in the upper right corner of the results.  The bottom option is Clear Output.

Once you have everything in the Notebook you can save it.  The Notebook, the file extension is ipynb.

In the coming weeks I will be doing more posts on Azure Data Studio.  Although so far I really like it, there are a few things that I have not been able to find yet, like Actual Execution plans.

 

 

 

 

 

 

 

 

 

 

 

How to capture an Execution Plan

Since in the coming weeks I will be presenting a session on Execution plans at a few SQL Saturday events, I thought I would do a post on how to capture execution plans.  Execution plans are a critical tool when attempting to troubleshoot a performance issue.  SQL Server Management Studio is one of the common tools we use to get the plan, if we have the query. You can also use Extended Events and Query Store.

Using SSMS

In SSMS there are two options to capture the execution plan, Actual and Estimated plan. The biggest difference between the two is that the Actual plan needs to execute the query, while the estimated plan uses statistics to create the plan and does not execute the query.

To get the Estimated Execution plan, you can use the Query menu. There you will find the Display Estimated Execution Plan menu item.

There a two ways to get the actual execution plan.  One is on the tool bar and the other is on the Query menu.

When looking to capture the estimated execution plan, as soon as you click on the menu item, the plan will be returned.  If you are looking for the actual execution plan, SQL Server will execute the query and when it is finished the plan will be returned.  For simple queries the estimated execution plan will be returned very fast, however for more complex queries it might take some time.

When in SSMS, how can I tell if the plan is actual or estimated? There are a few ways. The easiest way is to check the number of tabs.  As you can see below there are two tabs, one for messages and another for Execution Plan.  There is a missing table, results.

Estimated Execution Plan:  Two tabs

Actual Execution Plan: Three tabs

Another way to determine if the plan is actual or estimated will require us to dig a little deeper.  We will need to look at the properties of a few of the operators.

When you float the cursor over the Index Scan you will see this popup.  The items in the green boxes are for the actual execution while the items in the red boxes is for estimated execution.  Although having the options for estimated execution, you might think this is for the estimated execution, it is not.

When both estimated and actual number of rows can be found in the pop up, this means that it is an actual execution plan.  If it were an estimated execution plan, you will not see the actual items, as you can see in the image below.

If the estimated and actual plans are captured very close to each other, you will not see much difference.  However, if there are a large period of time between them, there could be a number of differences.

This will work with both an on-prem instance of SQL Server as well as an Azure SQL Database.

Extended Events

Using SSMS works great if we have the query, however it is a manual process.  What if you want to capture the plans after hours or automatically.  This is where Extended Events comes into play.  Just a word of warning, when capturing execution plans with Extended Events, you could actually cause a performance problem so be careful.

This is what Microsoft has placed in the description of the events related to Execution Plans. I think it is a pretty clear warning about using Extended Events to capture Execution plans.

“Using this event can have a significant performance overhead so it should only be used when troubleshooting or monitoring specific problems for brief periods of time.”

When working with an On-Prem instance of SQL Server, Extended Events can be found under “Management”.  This is a server level setting, although you can limit the activity captured to a single database.

The location is slightly different for an Azure SQL Database.  Extended Events can be found under the database.

You can right click on the “Sessions” folder under Extended Events and then click “New Session”.

When creating the session there are three events we will want to consider. These events can be found in both an on-prem instance as well as Azure SQL Database.

query_pre_execution_showplan – this event is the equivalent of an estimated execution plan

query_post_execution_showplan – this event can be used to capture the actual Execution plan.

query_post_compilation_showplan – Occurs after a SQL statement is compiled.

To create an Extended Event session you can either use SQL Server Management Studio by right clicking “Session” under the  Extended Events item or using the CREATE SESSION statement.

If using SSMS graphical tool, you can right click on Sessions and then click “New Session” you will see something similar to this.

Once you give the session a name, the you are ready to move on to picking the proper events.  Remember, there are three options for events, query_pre_execution_showplan and query_post_execution_showplan.

If you enter “showplan” into the Event Library search box.  You will see four events, the three mentioned earlier and a fourth related to Query Store.  We will discuss this one later.

Move the events over by clicking the “>” button and then you are ready to add the target.  Once you do, save the session, start it and you will now be collecting execution plans.

You can also use the CREATE EVENT SESSION statement. The statement below collects all three events, again NOT recommended.  Notice that this statement also has a filter for the AdventureWorks2014 database.  If you do decide to attempt, what even Microsoft says you should use with caution, try to reduce activity by limiting the collection to a single database.

CREATE EVENT SESSION [ExecutionPlan] ON SERVER
ADD EVENT sqlserver.query_post_compilation_showplan,
ADD EVENT sqlserver.query_post_execution_showplan,
ADD EVENT sqlserver.query_pre_execution_showplan(SET collect_database_name=(1)
    ACTION(sqlserver.sql_text)
    WHERE ([database_name]=N’AdventureWorks2014′))
ADD TARGET package0.event_file(SET filename=N’ExecutionPlan’),
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

Now that we are collecting the plans all we have to do is right click on the target and then click view data.  You will see something similar to below.  Notice the Query Plan tab highlighted.

If you right click on the plan you will see this context menu.  You can now save the plan, view the XML and even open the SentryOne Plan Explorer, if you have it installed.  If you don’t have it installed…..go get it.  It is a great tool.

 

Extended Events are a great tool to collect information about what is going on in your server.  As stated several times, if you are looking to Extended Events to collect your execution plans, do so very cautiously.  Also, limit the time the sessions is running and limit the activity to only the database you are looking to find a solution to a performance problem.

Query Store

The last option we will cover is Query Store.  This is a great tool!!!  Query Store came out with SQL Server 2016.  What I like about Query Store is that it allows me to collected the needed execution plans without the overhead of Extended Events.

To enable Query Store, simply go to the properties of the database and select Query Store.  The Operation Mode Property is Off by default. Change this to either Read or Read Write.  Now Query Store is enabled.

Notice that there are two Operation Mode properties.  One is actual and the other is Requested.  If you change Requested to something different than Actual, once you click OK, the requested setting will now become the actual setting.

Per Microsoft, here are a number of scenarios of why you might want to utilize Query Store.

Once you enable it and are collecting data, there are a number of reports that you can use to view the plans. Under that database, in this case, AdventureWorks2014, you will now see an option for Query Store.  When you expand it, you will see the reports that are available to you.

When you right click on one of the reports you will see something similar to this. By clicking on the bar in the chart on the upper left, the other two panels will update.  As you can see, the execution plan is in the bottom panel.

Once you have the plan visible, it works just like all the option methods of collecting the plan.  You can view the properties of each operator as well as save the plan for analysis later.

If you utilize query store, there is an event in Extended Events you might want to consider.  This event is query_store_generate_showplan_failure and will fire when the Query Store has an error creating the plan.

Here is the description from the event:

“Fired when Query Store failed to store a query plan because the showplan generation failed”

This post is about how to collect the Execution Plans, including Query Store.  In this post, I am not going to get indepth about all the options of Query Store.  There is some really great information out there, including some great videos.  Eric Stellato has some great content on Query Store in a number of locations, including SQLSkills.com, SQLPerformance.com and yes, even YouTube.

Query Store on SQLPerformance

There are number of options of collecting Execution plans.  My preference would be to utilize Query Store. Mostly because it captures the plans automatically, has really good reporting and has very low overhead.

Thanks for visiting my blog!!!