ADS: New Database

Regardless of what tool we are using, SQL Server Management Studio or Azure Data Studio, the need to create new databases is always present. Using Transact SQL is an option in both tools.  What is not an option in both tools is to right click and to go “New Database”.  This has been in SSMS for many years, however it is NOT present in Azure Data Studio.

Below you can see what the menu options are when you right click on databases in SSMS.  The New Database option is right at the top.

 

Now lets move on to Azure Data Studio.  When you right click on a server you will have a number of options, including new query, refresh and disconnect.  What you do not see is “Delete Database”.

As with a great deal of functionality in Azure Data Studio, the ability to right click on a server and create a new database comes with the installation of an extension.  In this case the extension was created by Keven Cunnane, It is pretty simple to install and cost the same as ADS, nothing.  This extension is currently not in “Preview” mode.  It is currently at version 1.0.0.

To install you will need to click the install button when looking at the extension documentation in Azure Data Studio.

Once you do, you will be taken to the location to download the .VSIX file.  You will need to download it and save to a location where it won’t be deleted or overwritten. To install, simply click on “Install Extension from .VSIX package” on the File menu.

Once you do you will see in the lower right corner that the extension has been installed.  A restart of Azure Data Studio is not required.  Now when you right click server, what do you see?  Well..you will see that nothing changed.  You will need to right click on Databases under the connection.

Once you click “New Database” you will not see the same dialog box you will see in SSMS.  What you will see is something like what is in the image below.  In the box you will enter the name of the new database.

Then hit enter and assuming you have the proper rights, ADS will create the database.  You will not be able to enter any of the settings we often change to accommodate the specific needs of the database or system.  This includes things like file location, recovery model, file growth rate and many more. With this, you will either need to use TSQL to adjust some of the settings or use SSMS.  This method of creating a database will depend on the defaults for all the settings.

Overall this is a good extension although it depends heavily on default database settings.  I still prefer to use TSQL when creating a database, however this is a nice simple method to use as well.

Here is a list of a number of available extensions, click here.  This list does not include all the extensions that are available but is still a nice list.

Thanks for visiting my blog!!

Why do I include Plan Guides in my Server Assessment

When I complete a server assessment there are a number of areas I look at.  These areas include security, performance and configuration.  Most of the specific items fall into one of these categories.  In my opinion, a complete server assessment should not only return the state of the server but should also return any configuration settings that may have an impact on performance.  Plan Guides fall into this area.  They can have a negative impact on performance just a quickly as they can have a possible impact on performance.

I my over 20 years of working with SQL Server I have never seen a plan guide actually used in production.  This doesn’t mean they are not being used, just that i haven’t seen it.

Before getting into why I look for Plan Guides, let’s take a few moments to go over exactly what a plan guide is.

Below is the definition from Microsoft:

“Plan guides let you optimize the performance of queries when you cannot or do not want to directly change the text of the actual query in SQL Server 2019 (15.x). Plan guides influence the optimization of queries by attaching query hints or a fixed query plan to them.”

Basically Plan Guides allow you to utilize optimizer hints without modifying the source code.  For example, Plan Guides allow for the use of a particular physical join type.  The optimizer utilizes a Hash Match join and you feel that the query might perform in a more optimal manner with a Nested Loop, the Plan Guide will allow this to happen without changing any code.

While this really isn’t a post about how to create and use Plan Guides, I think it is important to have a basic understanding of them. This is a link to Microsoft that will provide more detail.

When using the Object Explorer, Plan Guides can be found under Programmability in each database.

When I execute the query below in the AdventureWorks2014 database the actual execution plan will utilize a Merge Join operator.  This is without a plan guide to tell the optimizer to do anything different.  Of course, as you can see, an optimizer hint is also not used in the query.

SELECT *
FROM Sales.Customer AS c
INNER JOIN Sales.Customer AS ca
ON c.CustomerID = ca.CustomerID
WHERE ca.TerritoryID = 5

 

When a Plan Guide is created, you will be asked to provide the desired hint.  In the case below, the hint is to use a Nest Loop whenever the above query is sent to the server.

When the plan exists, the execution plan will include the use of the Nest Loop join.  This is because the query that matched the one in the defined in the Plan Guide.

Something to keep in mind, Plan Guides are both case sensitive and space sensitive.  If the plan guide includes the query below:

SELECT *
FROM Sales.Customer AS c
INNER JOIN Sales.Customer AS ca
ON c.CustomerID = ca.CustomerID
WHERE ca.TerritoryID = 5

And the following query is executed:

select *
FROM Sales.Customer AS c
INNER JOIN Sales.Customer AS ca
ON c.CustomerID = ca.CustomerID
WHERE ca.TerritoryID = 5

Notice that the SELECT keyword is not capitalized.  This will force the plan guide to be ignored.  The plan guide will also be ignored if there is a trailing space at the end of one of the lines.

Also, plan guides can also be enabled\disabled.  This can be done by simply right clicking on the plan guide in Management Studio and going to “Enable” or “Disable”.

When to use Plan Guides

While I haven’t used Plan Guides I can see where they have the potential to improve performance of a particular query in certain situations.  Many vendors don’t allow you to change the code of the stored procedures to add a query hint, not to mention this also may not be the best idea in the first place.

So why do a look for Plan Guides during the server assessment?

During the server assessment, I look for these more for awareness than anything else.  If a particular query performs poorly, it could be because of the plan guide.  If they do exist then it is important to determine if is helping or contributing to the poor performance.  As mentioned earlier, if a Plan Guide does exist, you will also need to determine of it is enabled or disabled.

The code below will check every database and return a row for each plan guide found.  In addition, it will return the query and whether or not it is enabled.

        CREATE TABLE #PGInfo
          ( DBName                 VARCHAR(128)
          , GuideName            VARCHAR(500)
          , CreateDate              DATETIME
          , ModifiedDate         DATETIME
          , IsEnabled                BIT
          , QueryStatement    VARCHAR(300)
          , Type                         NVARCHAR(Max))
       
       EXECUTE master.sys.sp_MSforeachdb ‘USE [?] INSERT INTO #PGInfo   SELECT
         DB_Name()
          , Name
          , create_date
          , modify_date
          , is_disabled
          , query_text
          , scope_type
        FROM sys.plan_guides ‘
        SELECT *
        FROM #PGInfo
        IF @@ROWCOUNT = 0
        BEGIN
         SELECT ‘No Plan Guides were found in any databases.’
        END
        DROP TABLE #PGInfo

 

As stated earlier, looking for Plan Guides is more about awareness than anything else.  If there is a performance issue with a particular query, knowing that these exist important.

Thanks for visiting my blog!

 

ADS: Demo Mode Extension

Through out the year I present a lot of sessions on SQL Server and related technologies. These take place at user groups(9 in 2019), SQL Saturdays(14 in 2019), training sessions at work and teaching the SQL Server certification courses from Microsoft.  With these many different presentations there are differences in the screen sizes and room sizes.

When presenting, setting the demos up in a way that will allow all attendees to easily see the code can be a bit of a challenge sometimes. To accomplish this presenters have several tools available to them.

Tools commonly used

      • ZoomIt – This is a free third party tool that I have been using for a few years
      • Windows 10 Zoom feature
      • Increase the font of the code window in SQL Server Management Studio or Azure Data Studio
      • Use the Demo Mode extension in Azure Data Studio
      • A number of other tools

While I would love to list the pros and cons of each, that really isn’t the focus of this blog post.  Recently an extension for Azure Data Studio was released by Drew Skwiers-Koballa that I think is a great addition to the list of available tools.

The first thing to do is to install the extension.  To do this, you will need to download the .vsix file.  In order to find the extension, you can type the word “Demo” in the extension search box.  Taking a small step back, if you are not sure how to get to the extensions, you can open the extensions by using the menu item under the View menu.

To download the file you will need to click the install button in Azure Data Studio when looking at the documentation of the extension.

When you download the file, place it in a location that will be secure and the file will not be overwritten.  Once saved, to install it, go to the “Install Extension .VSIX package” menu item under the file menu.  You will need to restart Azure Data Studio when the install is complete.  Please remember, this is extension is still marked as being a Preview.  Given that, things could change with the next release.

Now that it is installed, let’s go over what it does.  This part is pretty simple, when enabled, the font in the query window will be come larger and when disabled the font will become the original size.

The two images below show the font with Demo Mode enabled and disabled.  The first image is disabled, while the second has Demo Mode enabled. Notice that the only thing that changed is the font size of the query.

When Demo Mode is enabled the only thing on the screen that will change is the font size of the query.  Everything else will remain the same size.

To enable\disable the extension you will need to do one of two things.  First, you can use the Command Pallett.  Type “Demo Mode” into the search box.  Two options will be returned, Enable and Disable Demo Mode.  This can be seen in the image below.

The second option is to click the Enable\Disable Demo Mode button that is located in the lower left corner of Azure Data Studio.  I personally like this option because it is very easy to use.  By default neither of these options has a keyboard short cut assigned to it.  However, under File–>Preferences–>Keyboard Shortcuts, you can add the short cut key or key combination that will work for you.

You may be asking yourself where does the extension get the font sizes to use.  When the Demo Mode extension is installed, three settings are added.  Two of these settings identify the font size to use when the extension is enabled or disabled.  See the image below.

The Demomode: Demo Font Size setting is what font size ADS will use when the Demo Mode extension is enabled.  As expected, you would want this to be bigger than the Original Font Size, which is just below it.  This represents the font size with the extension is disabled.

The third option will allow Azure Data Studio to send confidential about the usage of the extension.  As you can see with the presence of a checkbox, this is something you can option out of if you like.  The documentation can be found here. This is what the documentation actually states:

“enables anonymous usage data to be sent to an online service”

The documentation also goes on to state the following:

“The extension telemetry sends usage data anonymously to an Application Insights instance. Machine information, such as operating system or general location, may be shared with the general public in aggregate. Your information will not be sold to any third parties. For more on the collection of telemetry in this extension, read more here or ask questions here”

Please read through these very carefully to determine of this is something you would like to opt out of.

Under the hood, when this extension is enabled, it changes the Editor: Font Size to be the same as the Demo: Font Size setting for the extension.  If you close Azure Data Studio while the extension is enabled, the font setting will persist.

What happens if there are more than one query window open?  Well, the extension will apply the font setting to all query windows that are open.  I also noticed that if the extension is enabled and you close settings, the current font size will also persist.

Another question that comes to mind is will this setting only apply to queries?  The answer is no. This extension will also have an impact on the font in Notebooks.  If you look at the image below you will see what the text will look like with the extension enabled and disabled.  Notice the difference in size of the box the text will be entered into.  Of course this assumes that the two font settings for the extension are different sizes.  In a notebook, this font change only applies to when text is being entered, it will not have an impact on the look of the text that will be displayed.  I checked the Terminal that will be used for PowerShell and the use of the extension does not have any effect on the size of the font.

If you open Settings and you only get json, earlier I completed a blog post on how to open the Settings in json rather than the UI.  Here is the link to that blog post.  In the settings.json file, the Demo Mode extension will look like below.

I like this new extension and plan on utilizing it for all my sessions that use Azure Data Studio.  For additional information you can go to the documentation. I would like to point out that while this extension has a target audience of people who do presentations, it also has another audience.  Sometimes, I just need the font to be slightly bigger for a temporary reason, like I am tired and the eyes just can’t focus as well as normal.  I can use this extension to make that much easier.

For more information about  Drew, the developer of this extension, you can go to his blog, https://www.drewsk.tech/.  Also he has a nice presentation on developing extensions.  If you get a chance to hear this at your local user group or SQL Saturday, make sure you go, it is a very nice session!

Thanks for visiting my blog!!!

 

ADS: Settings Editor

When working with the Settings in Azure Data Studio, you have a few options on how you will see them.  The two options available at this point are the “user interface” and “JSON”.  While it is usually easier to modify the settings using the UI, you might prefer to use the json instead.  I feel that anyone that uses Azure Data Studio on a regular basis should learn the basics of JSON.

These are my reasons why I think we should learn the basics of JSON.

      • Some settings must be changed in JSON
      • One of the export options for a result set is JSON

The first reason is where the focus of this post is.  Many settings can easily be modified using the user interface, however not all can.

The settings can be found under the File menu or you can use the keyboard shortcut of “Ctrl + ,”.  Under the File menu is you will find Preferences.  It is in the pop out menu that the Setting menu item can be found.

Once in settings you will quickly see that there are many settings.  Most of these you will not have a need to change, but it is good to know what can be changed. Once in the Settings, the user interface is quite easy to navigate around. You can even search for specific key words on the top.

As you scroll through the various settings you might notice that not all the options can be changed in the UI.  Some must be changed using the JSON file.

The settings.json file can be found in this location.

C:\Users\<<UserProfileName>>\AppData\Roaming\azuredatastudio\User

Notice that “azuredatastudio” does not have any spaces.  When you navigate to this location “C:\Users\<<UserProfileName>>\AppData\Roaming” you may see two folders, both named Azure Data Studio.  One will have spaces, the other will not.  The settings.json file is found in the folder without spaces in the name.

 

Now that we know the location of the settings.json file, let’s return to the editor.  As mentioned earlier, the settings can be changed in two places, the editor UI and the JSON file.

This is what you will see if the setting can be modified in the UI.  Depending on the setting, you may see a dropdown box, a text box or a check box.

If you see something like what is in the green box below, the setting must be modified in using the settings.json file.

When using the UI, you have both options.  However, there is an setting that will allow you to use only the settings.json file.  This setting can be found in the Workbench group.

The dropdown box will have two options, ui and json.  When set to json, the settings.json file will open when you attempt to look at the settings.  Keep in mind, when you change this to json, you will not get a warning and it will be saved automatically by default.

With it set to json, as expected the settings.json file will open instead of the user interface.

You might be asking, how can I change it back?  This is quite simple.  Look for the “workbench.settings.editor” setting and change it back to “ui”.  Remember there are only two option, “ui” and “json”.  Make sure to keep the quotes.  As you can see in the above image, it is on line 209 on my instance, although will probably be different for your instance of ADS.

When in the json file, if you float your mouse pointer over one of the settings a pop up will appear that may provide a bit of information about the setting.  Something like what is in the red box below.

While I prefer to use the ui, it is nice to have this option if I wanted to change.

If you are not using Azure Data Studio on a regular basis, my suggestion is to take a look at it.  The price is right, it is free and has many great features.

Thanks for visiting my blog!!

 

 

ADS: Number on the Explorer Icon

When you open Azure Data Studio you will see a tool bar on the left side of the screen.  On this bar you will see a number of icons.  The image below is what you might see.  From time to time you might see a number on the lower right of the icon.

In the example, there are two icons that have number over them.  The bottom one is related to extensions and it is telling you there are updates to extensions you have installed.  In this case there are two.

The top number is the focus of this post.  The icon is for the Explorer. When you click on the icon you will see a screen pop up. At the top of this screen you will see “Open Editors”. Similar to below.

These are files that you have opened, made a change to and have not saved.  As you open files and make changes, this number will increment.  It will also increment if you open a new query window and make a change to it.

Although Azure Data Studio can persist your unsaved documents, it is still a best practice to save your work as you go.

Thanks for visiting my blog!!!

 

SYS.DM_EXEC_SESSIONS and System Processes

Being able to gather information about current sessions has for many years been an important part of a DBA’s responsibilities when troubleshooting a performance issue.  The type of information that we need includes the SPID, host, database name, login name and application being used.  While all this is important, at times system processes need to be identified or even excluded from the results.

Over the years, DBAs have developed their own technics for gathering information about current processes. These have included sp_who, sp_who2 and a query pulling data from the sys.processes table. SYS.DM_EXEC_SESSIONS is a DMV that came out more recently, SQL Server 2008.  I have read many articles and posts over the years that have said any SPID with an ID of 50 or less should be considered system related.  While this is true, there are some processes that will have an ID greater than 50 and also be a system process. SYS.DM_EXEC_SESSIONS allows us to easily identify system processes.

Sp_who and Sp_who2 are stored procedures I have used many times over the years. I usually use sp_who2 rather than sp_who.  Sp_who2 returns more information than sp_who. If you look at the image below, you will see two data sets.  The top one is sp_who and the bottom is sp_who2. These additional columns is why I prefer sp_who2.

Sys.sysprocesses is also a great resource   However is does not include a column that will allow us to determine if it is a system process or not.  Below are the columns in sys.sysprocesses.

SYS.DM_EXEC_SESSIONS is a view that was included starting with SQL Server 2008.  This view returns much, if not all the same information as the above. However, it also returns a great deal of additional information.  One of the columns returned is “is_user_process”.  This is a bit column that if set to 0 is a system process and if set to 1, is a user process.
SELECT     session_id
        , login_time 
        , is_user_process
        , login_name
        , host_name
FROM SYS.DM_EXEC_SESSIONS
If you run the above query, you will see a result set similar to what is below.  Is_user_process is the third column.  The rows in the red box are above session_id of 50 or more.  The items in the green box are user processes, as
identified in the is_user_process column.

This is a great view to use.  Some thing to keep in mind, it is a view and one of the tables it pulls from is sys.sysprocesses.  Here is a link to Microsoft’s documentation on this view.

Thanks for visiting my blog!!!

ADS: Simple Data Scripter

Moving data in a table from a production server to a lower environment such as a development server can be done a number of different ways.  These include backup\restore, using SSIS to move the data, import\export wizard and scripting the data in SQL Server Management Studio.  Azure Data Studio by itself doesn’t have a way to script the data in a table and it does not have the import\export wizard.  This is where the Table Data Scripter extension comes in handy.

Like most extensions for Azure Data Studio, this extension is also in a preview state.  As of the date of this post, it is currently at version 0.1.1 and is published by Sean Price. The documentation for this extension can be found here, Simple Data Scripter. In the documentation there are a few limitations identified

      • Not all data types are supported, specifically the binary datatypes like text, binary and varbinary
      • Has only been tested with SQL Server
      • Does not work on a view

Some extensions will add options under Preferences.  This extension does not add any options.  Given that, there is limited ability to configure this, which is OK, not really much of a need to do any configuration.

Just as a review, let’s take a look at how we can script the data in SQL Server Management Studio.  After right clicking on the database and going to Tasks…Generate Scripts…  When you do, a wizard will pop up and then after a few clicks on next, you will arrive at the screen below.  The box highlights the “Types of data to script” option.  With this there are three options, Data Only, Schema and Data and finally Schema Only.

When right clicking on a database in Azure Data Studio you will see limited options none of which are related to scripting data.  This is also true when right clicking on a table in the database.  Here are the options we see by right clicking on a table, as before, none of which are related to scripting data.

In order to script the data in Azure Data Studio we will need to install the Simple Data Scripter extension.  This is a pretty straight forward and simple install.  You will need to download the .vsix file.  You can get to the GitHub to download the file from the documentation for the extension in ADS.  You can install it under the File menu.  While you will not be prompted to restart ADS when you install it, I usually like to restart ADS anyway.  In this case, the extension doesn’t move to the installed extensions until after a restart.

Now that the extension is installed, you will see a new option when you right click on a table.  This is another potential limitation of the extension, there isn’t an option to script all the data in the database.  Well, at least I haven’t found it yet.  But then again, I am not sure having a simple place to script all the data in one place if the best way to go.

When clicked the following screen will appear at the top Azure Data Studio.  Initially when we look at it our first thoughts might be, what a bad query.  But it does make sense, we are creating a script of all the data in the table.

If you click anywhere, ADS will cancel the query and you will see this pop up in the lower right corner and the above screen will disappear.

If you press enter Azure Data Studio will execute the query and generate the script. You will not see a result set but you will see the script.  It will look similar to below.  Notice the Create Table statement at the top, it is commented out.  The table that is used for the example is named RentalCar.  The script is working with a table named #tempRentalCar.

When the script is finished, the Task window will open up at the bottom of the screen.  The top two are successful executions while the third failed, reason for the failure will be covered shortly.

You may be wondering if the query can be modified.  The answer is yes, however not every type of statement will work.  If you want to add a WHERE clause that works just fine.  However, as the query become more complex it failed more frequently.  I attempted to run a query with a join in it and it failed.  That is the failure in the image above.  The error stated “Query has no results to return”.  However, the query works just fine when running in ADS or SQL Server Management Studio.

Here are some of the statements I have tried.

      • Query with a join – failed
      • Query with a sub-query – failed
      • Query with a WHERE clause and just one table – succeeded

When compared to the expectations, this extension works very well and I can definitely see a use for this.

Thanks for visiting my blog!!