Azure SQL DB and @@version

Many of us are used to using @@version to get the version of SQL Server we are using.  This also works when you are utilizing an Azure SQL database, although the results are slightly different. However, before getting to @@version, I would like to go over a few other methods to obtain version information.

How to Get Version

There are many reasons why it is important to know the version of a SQL Server.  The reasons include everything from licensing to upgrades.  In this section, I will cover a few of the common methods used to get the version of SQL Server.

With an on-prem instance of SQL Server one method that can be used to get the version is to right click on the server in SQL Server Management Studio.  Once you do, you can click on the properties menu item and the window below will appear.  As you can see, in the boxes you are able to see both the version and edition of the SQL Server.

However, when you attempt to do this in an instance of Azure SQL database, the properties menu item is not available.  Below is what you will see when you right click on the instance of an Azure SQL database in SQL Server Management Studio.

Another method to get the version of SQL Server is to utilize the SERVERPROPERTY function.  The SERVERPROPERTY function can be used to view server level properties, in this case the product version.

SELECT SERVERPROPERTY(‘ProductVersion’);

When you execute the above statement you will then see the version. It will look like the number below.  The number represents the version, in this case, SQL Server 2017.

14.0.1000.169

As mentioned above, the ServerProperty function can be used to return a number of bits of information about the server.  Here is a link for more detailed information, click here.

The final method I would like to talk about is using @@version.  This is a global variable in SQL Server that will return not only the version of SQL Server, but also the OS version.

When reading from this global variable, you include it in a SELECT statement as illustrated below.

SELECT @@Version

The results will vary from an on-prem instance of SQL Server to an Azure SQL Database.

This is what you will see for an on-prem instance.

Here is the complete text of the results.

“Microsoft SQL Server 2017 (RTM) – 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Home 10.0 <X64> (Build 17134: ) “

As you can see it returns some good information.  Including the version of SQL Server, the service pack status, 32-bit or 64-bit and the OS version.

However, the results change quite a bit when this is ran against and Azure SQL database.

The complete text is just as you see it above.  Notice the lack of OS version.

What is next?

Now that we have covered a number of ways to get the version information, let’s take a look at some of the differences from an on-prem instance to an Azure SQL database.

When looking at the results, it is easy to identify an instance as an Azure instance in just in the first few words, it clearly states that it is an Azure instance, it is indeed in Azure.

If you look at the results below you will see that I used both @@version and ServerProperty to get the information I am looking for.  You will also notice that I included a simple query using the sys.databases object. This will return the compatibility level for each database, which as you will see is going to be important.

Looking above you will see that it is an Azure instance, has a product version of 12.0.2000.8 and both databases have a compatibility level of 140. The compatibility level of 140 is SQL Server 2017.

What….12.0?  Isn’t that SQL Server 2014?  If you said yes, you are correct. Is it really possible that Azure SQL Database is SQL Server 2014? The answer is no.  That number is different that an on-prem instance of SQL Server.  According to what I have ready, version 12.0 is the most current version. Given both the Azure instance and the SQL Server 2014 both of a product version of 12.0, it now comes down to the compatibility level for the databases for Azure.

As you can see both databases of a compatibility level of 140, which is SQL Server 2017, not 2014. Since I didn’t change this when I created the databases, this tells me that the instance of Azure SQL Database was on SQL Server 2017 when I created the database.  As with an on-prem instance of SQL Server, the compatibility level for an Azure SQL database can be changed. When I created a new Azure SQL Database, the compatibility level was 140, which is SQL Server 2017.

To change the compatibility level of a database you have to run an Alter Database command.

ALTER DATABASE Advent
SET COMPATIBILITY_LEVEL = 160

When I ran the above code, I got an error.

Msg 15048, Level 16, State 1, Line 1
Valid values of the database compatibility level are 100, 110, 120, 130, 140 or 150.

Notice it states that 150 is an option, while 160 is not.  Compatibility level of 150 is SQL Server 2019, the latest version.  It looks like the available options go back quite a bit, version 100, which is SQL Server 2008.

Since in Azure SQL Database, the version of the database is key when investigating versions, below you will find the code to retrieve the versions of all databases.

SELECT name, compatibility_level FROM sys.databases;

All the above code, works on both an Azure SQL database and an on-prem SQL Server.

Notice that the Advent database has a compatibility level of 150, SQL Server 2019.  When investigating version information, it important to also review the compatibility level of the database you are looking at as well as the server version.

Thanks for stopping by, I hope you learned something.

Azure Metrics Reporting

A few days ago, I did a post on DTUs of an Azure SQL Database.  In the post, I mentioned a report that you can use to see how many DTUs your subscription has used.  This is an important number to know and measure over time so you don’t get slapped with an unexpectedly high bill or have performance issues because you have exceeded your DTU limit.

Azure has quiet a bit of reporting that can me used to get the current state of your Azure subscription. Although many of the metrics are targeted more for subscription rather than database performance counters, there are few DB related such as Deadlocks. Below is a small list of the items that can be viewed, of course there are more than included in the list:

      • Azure SQL Database
        • Data space allocated
        • Data space used
        • Deadlocks
        • In-Memory OTLP usage
        • DTU used
          • What is a DTU?  Stands for Database Transaction Unit. Here is a nice article by Andy Mallon the does a nice job explaining what it is. Click Here
      • Storage Accounts
        • Used Capacity
        • Egress
        • Ingress
        • Availability

How do I Get to the reporting tool?

Getting to the reporting tool is just a matter completing a few clicks. Of course with most products there are several ways to get to a specific place.

On the left side of the Azure Portal, click “All Resources”.  You could also click on Resource Groups if you like.

Just a little side note on how to add a resource. As expected there are many resources in the Azure world.  Here are just a few: Azure SQL Database, Azure VM, Storage accounts, resource groups, Cosmos DB as well as many other items.  Not all resources are from Microsoft.  There are a number of resources that are made available in Azure.  After you click the “Add” button, you will be taken to a page that looks something similar to what is below.

Above is a screenshot of some of the available resources.  The list on the left is just the list of resource categories.  On the right are some of the resources in the chosen category.  You can either pick a category or click the “See All” link towards the top.

Once you click the “All Resources” link you will be taken to something that looks like this.

The above image lists a number of important items.  Obviously the resource name is included and the Resource Group that resource is part of.  You can also see the resource type and the Location.  The location is really important, especially when it comes to cost.  Since this blog post is about Metrics reporting, it is important to note that not all the resource types have this type of reporting.  Above you will see that I have three resources, a storage account, a SQL Sever and an Azure SQL database.  Of these three, the SQL Server is the only one that doesn’t have this metrics reporting functionality.

Once you click the resource you would like to get the reports for, you will be taken to a page similar to this.

By chosing a resource on the left, the panel on the right will update with the information for that resource.  In the above situation, I chose the storage account.  Notice two green boxes, both of these will take you to a reporting tool. You want to click the one on the top. The bottom one, Metrics(Classic) is going to be retired on June 30, 2019.  This is according to Microsoft.

Creating the Chart

Now you simple click on the Metrics link and you will be taken to a page that looks similar to what is below.

Now confirm that you are on the proper resource by viewing the resource identified in the box highlighted in the green box.  If you are not on the proper resource, simply click the resource that is there and you will have the option to change to the proper resource.

Staying in the same box as the resource, you will see a few other items.

Metric Namespace:  This is the group of metrics that you are looking for.  The items in the drop down box changes depending on the type of resource selected. Since I am working with a storage account, you see the account option.  If you don’t see this for some reason, click the “Add Metric” link that can be found just under the Chart Tile.

Metric: This is the counter that we are looking to review.

Aggregation:  This is what it says it is.  There are usually three options, Avg, Min and Max.  However, some only have Avg as the only choice.

Once you have chosen all the desired options, you will see this.

As you can see, this chart contains more than one metric.  This is easy to add, just click the “Add Metric” link in the upper left hand corner. When you do, you will be given the same options as before.  The additional metrics do not have to be from the same resource, as you can see.  The metric on the left is from the storage account, while the one on the right is from the Azure SQL Database.

Although this example is a line chart, you can change the chart type easily.  Just click the arrow to the right of where it says “Line chart” in the upper right corner.Just like in Excel, there are a number of options for chart types, just not as many options.  Only 5 options here.  Just like in any other reporting tool, not all charts work well with all data sets.

Also in the upper right corner you can change to time range of the chart.

If you click the time, the options window for the chart will open up. As you can see there are a number of options, including the time range, the Time granularity(time interval) and the time(GMT or local time).

The options for Time granularity are in intervals that would be expected.  The options are listed below.

      • 1 minute
      • 5 minute
      • 15 minutes
      • 30 minutes
      • 1 hour
      • 6 hours
      • 12 hours
      • 1 day
      • 1 week
      • 1 month

You also have another option, Automatic.  The interval for automatic is dependent on the Time Range chosen. Below are the time ranges with the automatic interval.

      • Last 30 min – 1 minute
      • Last hour – 1 minute
      • Last 4 hours – 1 minute
      • Last 12 hours – 5 minutes
      • Last 24 hours – 5 minutes
      • Last 48 hours – 15 minutes
      • Last 3 days – 15 minutes
      • Last 7 days – 15 minutes
      • Last 30 days – 30 minutes

Another area that could be very helpful is in the lower left corner.  There will be one number for each metric that has been added to the chart.

In the green box above there are a number of data points.  It contains the resource name of the color of the line in the chart.  It also contains the name of the metric, aggregation used as well at the value.  In this case the number represents the aggregation listed for the data range of the chart.  If you float your cursor over the chart and vertical line like below will appear.  When you do this, you can move it to any time on the chart and the numbers in the lower left corner will no longer represent an aggregate, but the numbers of the point in time on the chart.

Now that I have created my chart, but the numbers on the Y axis don’t meet my needs.  This can easily be changed.  In the upper right hand corner you will see three dots.  If you click it a menu opens up and you can the click “Chart settings”.

Once you click the menu item, the settings window opens.  Here you can change only Y value.  Remember the X values are determined by the date range mentioned earlier.

In addition to the Y value, you can also change the Chart Title and chart type.  Notice that the unit of measure is bytes, but many of the metrics are in MB.  If you change to a time granularity to something other than automatic, the warning goes away.

Notice the warning:

“Setting a min or max value is not recommended when the time granularity is set to Automatic”

Pin to Dashboard

Once you are in the Azure portal, you can have one or more dashboards.  Once  you have your chart looking they way you want it to look, you can then pin it to your dashboard.  Once you do that it, will now appear as below.  In this case I have two charts pinned.

 

To pin your chart to the current dashboard, click the “Pin to dashboard” button in the upper right corner.

When you do, you will have an option to pin to current dashboard.  However, when it is pinned, your dashboard may not look as you like it.  If this is the case, you can click the “Edit” option on the dashboard.

Once in edit mode, you can move the charts as you see fit.

One nice feature of pinned charts is that the vertical line that appears when you float your cursor over the chart still works.  There is one additional feature that is very nice if you have more than one chart.

 

When you have more than one chart, the line appears on all charts that are pinned to your dashboard.

Hopefully this will help you with creating charts in the Azure portal.  Thanks for stopping by my blog and I hope you learned something.

 

 

 

 

 

Azure SQL Database and DTU

As you know, when you are using an Azure SQL Database you can use SQL Server Management Studio on your computer to execute queries.  You also have an option to use the Azure Query Editor. Here is my post on the query editor.

As I was writing the post I wondered more and more about whether or not there might be a difference in how data usage is billed.  My thought was that since I am going from an Azure SQL database source to another Azure tool, Query Editor, could this potentially reduce my costs.

There are a number of Azure resources that have Egress as a metric that can be used to measure the amount of data that is being pulled from the Azure data center.  These resources include the Azure Storage Account. However, an Azure SQL Database does not need to use a storage account.

Before we move on, let’s define what egress is.

Here are a few definitions that I found online.

Azure egress charges apply only when data crosses from one Azure region to one or more other Azure regions. Any traffic that originates and terminates within a single Azure region is not subject to egress charges. It does not matter that traffic moves from one subscription to another. If the data remains within the same region, there are no egress charges.”  – help.bittitan.com

“….data going out of Azure data centers” – Microsoft

I think the most important point the two definitions are trying to make is that egress is data going out of a data center, including if it is going from a data center in one region to a data center in another region.  According to Microsoft, if there is a data pull and the database and the querying tool are both in the same region, you will not be billed.  However, you will need to confirm this.

I am not planning on getting into the pricing models for Azure in this post, however you can get that from Microsoft.  https://azure.microsoft.com

Although some resources have Egress to measure data, an Azure SQL Database does not.  It does however have another option, DTU.  DTU stands for Database Transaction Unit and it is a unit of measure that is used, in part, to calculate your bill and to help you chose the proper pricing tier for your database.

A DTU is a “blended measure of CPU, memory, and data I/O and transaction log I/O”, per Microsoft.  The DTU is a measurement for the Azure SQL database only, not for your entire Azure environment or any other database.  An important thing to keep in mind is that when your workload exceeds the DTU maximum for your pricing tier, Azure will then throttle your workload.  My subscription currently has a max DTU of 10. As long as my workload stays below that I should be fine.  Because of the potential of being throttled, it is important to monitor your work load.   This is a nice article on the DTU by Andy Mallon.

If you are not sure what your workload might be in Azure, there are a number of DTU calculators available that can help.  Here is a link to one, DTU Calculator.

As for the how many DTUs you can have, that will depend on the tier that you have chosen.  Below is a sample of a chart from Microsoft that outline the limits of two of the tiers.  Notice that there are a number of options for Max DTUs. Source of the chart is here. As you can see, the Max DTUs goes from 5 to 100.  It even goes higher if you have are using the Premium tier.

Per Microsoft, you have up to the Max DTU for your tier before you will be charged.

The DTU price for a single database includes a certain amount of storage at no additional cost. —Microsoft

Of course this might change at any moment, so I have to make a disclaimer.  Always consult your MS rep or Licensing expert to get the exact pricing and rules.

As I stated earlier, it is important to monitor your work load to see if you are at a position in which you might be throttled.  Azure is a pretty good set of monitoring reports that you can create.  You can use the metrics reports to accomplish this.  Here is a link to my post on how to create a metrics report.

So what should you monitor?  Well since we are talking about an Azure SQL database, you have three options for measure DTUs.


DTU Limit – this is what you think it might be, the limit for your tier.

DTU Percentage – this is just that, the percentage of the DTU max that is being used.

DTU Used – this is the current DTUs that are being your by your workload for the database.

This post is less about DTU calculation and pricing tiers and more about does it make a difference if I use SSMS or the Azure Query Editor on the use of DTUs.

From the tests that I have completed that answer is no.  Using Azure Query Editor showed little or no improvement in the use to DTUs.

Before we get into the results of the test, let’s cover a few basics of what I did.  I used the AdventureworksLT sample database that you can get from the Azure portal when creating a new sample database.

This is the query I ran this query using both tools.

SELECT TOP 100000 *
FROM saleslt.salesorderdetail a
CROSS JOIN saleslt.salesorderdetail b

In the chart below the green boxes represent the test using SQL Server Management Studio, while the blue boxes are the executions in the Azure Query Editor.  Notice that there is very little difference in the DTU usage.

Although there really isn’t much difference in DTU consumption, I did notice something I didn’t really didn’t expect.

When I ran the query in SSMS is usually took less the 3 seconds to run.  However, when I ran the same query using the Query Editor, it usually took 45 to 60 seconds.  That is a huge difference.  Also note that in the above chart, the Max DTU never reached 10, which is my limit.  Because if this, I know that I was not throttled by Azure.

You might be wondering with the red box is.  This is the test when I ran this query.

SELECT *
FROM saleslt.salesorderdetail a
CROSS JOIN saleslt.salesorderdetail b

The only difference it that it doesn’t have the TOP keyword.  What is interesting about this is that when I ran the query in SSMS, it took about 45 seconds.  However, when I ran in the Query Editor, it was pushing 5 minutes before I cancelled it.

How can I get information about the MAX DTU for the Azure SQL Database?  There are a few DMVs that you can use.  The first on is the dm_db_resource_stats.  This can be used to the Max DTU, as well a number of other bits of useful information.

If you run this query in your Azure SQL Database you can get the information.  The column you want to look for to get the Max DTU limit is DTU_limit.  Here is a link to get more information on this DMV.

SELECT * FROM sys.dm_db_resource_stats;

However, if you try to run it in the Master database, you will get this error:

Msg 262, Level 14, State 1, Line 1
VIEW DATABASE STATE permission denied in database ‘master’.
Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.

You can also use he sys.resource_stats dmv to get the DTU limit.  This DMV has the same column name as above, dtu_limit.  This DMV must run in the Master database.  Here is a link to get more information on this DMV.

SELECT * FROM sys.resource_stats

If you try to run it in another database, you will get this error.

Msg 208, Level 16, State 1, Line 3
Invalid object name ‘sys.resource_stats’

How do I get to the metrics report. This is pretty straight forward.  Navigate to your Azure SQL Database in the portal.

 

As you can see, in the menu on the side you will see the Metrics items.  If you click it, You will then be taken to an Azure Reporting tool.

You will then create your report.  This my post on the Metrics Report.

Thank you for stopping by and I hope you learned something.

Disabled Database User?

In my many years of working as a DBA, I have encountered many disabled logins.  However, I have never really encountered what looks to be a disabled database user account.  I didn’t even think it was possible to disable a user account in a SQL Server database.  I checked the user account properties just to makes sure I was correct.  Sure enough, no option to disable a user account. This finally turned out to be a simple case of looks can be deceiving.

When we first received the call that a user was having an issue, we asked what was the error.  This is what we were sent:

For the purpose of this blog, we will be using a SQL login named, testuser.

My first thought was maybe a permissions issue.  I very quickly realized that it was something else.

The first thing I did was look at the properties of the login.  This clearly showed that the login had access to a few databases, except the one related to the reported error, 1GBDB.  As you can see below, it looks like the login doesn’t have access into the database.

 

However, if you look at the users in the database, you can see that the account does indeed exist in the database.  As you can see below.

Of course the red “x” on the user account was a bit suspicious, we will get into that in a bit.  One of the items I checked was if we have an orphaned account.  So I check the SIDs for the login and the user account using these queries.

SELECT sid, name
FROM sys.syslogins
WHERE name = ‘testuser’

SELECT sid, name
FROM sysusers
WHERE name = ‘testuser’

Here are the results.

As you can see, the SIDs for both the user account and the login are the same. So that rules out an orphaned account.

I still couldn’t figure out why they user couldn’t get into the database.  That red ‘x” was still bothering me.  After some checking, I discovered there was something different about the user account in this database, 1GBDB, when compared to the same user accounts in different databases.

Database Permission properties for the 1GBDB, which the user cannot connect to.

This the a screenshot of the database permissions for the AdventureWorks2014 database, which the user can get to.

There is one important difference, the Connect permissions.  If you remove the “Connect” permissions from a user account in a database that has a corresponding login, you will see this.

Once you grant the Connect permissions for the user account, the red “x” goes away and all should be good.  Unless of course there are other issues.

Thanks for stopping by and I hope you learned something!

Azure SQL DB Query Editor

We have all been using SQL Server Management Studio to query and manipulate data, even for an Azure SQL database.  There is also an option to do this same thing built into the SQL Azure database interface in the Azure portal.  Although there have been a number of posts related to this topic dating back a few years, this feature is still marked as “preview” in the Azure portal.

How to get to the Query Editor

Once you are in the SQL Database list in the portal, find the database you would like to work with and click it.

When you do, you will be taken to the administration page for the database.  On the left side you will see the “Query editor” link.  You will also notice that this is a feature that is still considered to be in preview.

When I did a Google search on the Query editor in the Azure portal, I found posts from as far back as 2017.  Despite that, I still wanted to complete a blog post on it.  Once you click it, you will be taken to a login page similar to below.

Notice on the right side there is the ability to utilize the Active Directory single sign on functionality, which I do not have.  On the left you will enter the appropriate credentials to login to the database.

Once at the Query Editor

Once you are in the Query editor, you will notice many of the same features as SSMS.

These features include the following

      • Tabs for new queries
      • Open query
      • Results pane
      • Messages pain
      • A condensed object explorer
      • as well as many other features of SSMS

Below you will find a screenshot of the Query editor interface.

Menu Bar

Starting with the menu bar across the top, you will find the expected buttons.  In addition to the Open query, new query and save query buttons, there is also an Edit Data(Preview) button.  This does as you expect it might, allows you to edit the data.  Just like the Query editor, this is also a preview feature. There is also a Login button if you wish to use a different user.

Object Explorer

 

The Object explorer is very similar as that in SSMS, however there are some very glaring differences, mostly that there are fewer object types displays in Query editor.  As you can see you can browse tables, views and stored procedures.  As for the tables, you can see the column names and data types as well. For stored procedures, you are able to see the parameters just as the image below indicates.

In SSMS a user can right click on a stored procedure and execute it.  In the Azure query editor, when you right click on an object you get nothing.  The context menu does not appear, therefore not allowing you to execute the procedure.  Of course you can still use T-SQL to execute a stored procedure.

Query Window

In many aspects the query window is very similar to that of SSMS.  There are tabs across the top, the T-SQL pane, a results pane and a messages pane.

Starting with the T-SQL pane, there is color coding of the SQL, however it is slightly different than that of SSMS.  First big difference is that there is not an option to change the size or color of the font.

If SSMS if you execute two SELECT statements, like the ones below, the results are much different in Azure Query editor when compared to the same two queries in SSMS.

SELECT TOP 10 p.name
, p.productnumber
FROM saleslt.product p

SELECT TOP 10 *
FROM saleslt.address

Here are the results when you run the code in SSMS.  Notice that there are two data sets

Now look what happens when I run the same queries as the Query editor, I only get one data set vs the two when executed in SSMS.

In SSMS, if you right click on a table you will see an option to Edit X number of rows.  That options is very similar in Azure Query Editor. The way this is done in the portal is to click on a table and then click the “Edit Data (Preview)” button just to the right of the Login button.

This will in turn, return the data in a grid that allows for editing data. You can just double click in a cell in the results set.  This will change the cell to an editable version.  The edit menu has just 5 buttons on it.

The are:

        • Create New Row
        • Save
        • Refresh
        • Discard
        • Delete row

When you click the new row button, the new row will appear in a different location that if you attempted to enter a new record use the grid results set in SSMS.  The big difference here is that the new row will appear at the top of the grid.

One option that is important to me is the ability to change the font size.  I have NOT found out how to do this in Azure Query editor.

When you execute a statement and an error is returned.  In SSMS this error is red, but default. In the Query editor it is black.

Once you have your query as you like, all you have to do it click the Run button

Limitations

There are a number of limitations in the Query editor at this time.  Since it is in preview, my hope is the Microsoft will address some of these deficiencies as the Query editor moves closer to having the preview label removed.

      • Does not allow for the viewing of object definitions
      • Limited objects in object explorer
      • Tabs in the query window does not have the SPiD on it
      • If more than one SELECT statement is executed in a batch, you will only see the results of the last one
      • Cannot right click on a stored procedure to execute it
      • No options such as display Actual Execution Plan
      • Cannot drag and drop object names into the query

Despite all of the limitations, I still like it.  Once you get used to the limitations it really isn’t that difficult to use.  Since the name of the tool is Query Editor, it does make sense that it doesn’t have all the same functionality as SQL Server Management Studio. However, it would be nice to have some the items that are currently not included.

Thanks for stopping by and I hope you learned something small.

Anatomy of a SELECT statement part 6 – the WHERE clause

After a brief break, ok..maybe not so brief, this post continues the series of the Anatomy of a Select statement.   This post is about the WHERE clause.  The WHERE clause will provide a filter to limit the rows returned to only the rows that are needed.  As we should always do, it is important to only pull the data that is needed.  The WHERE clause is a key part of that effort.  We use the SELECT to limit the columns, while the WHERE is one of the statements that can limit the number of rows.  The sample code will be at the end of this blog post.

The first thing we should look at is exactly where does the WHERE go?  As indicated below, it follows immediately after the FROM.  Since the FROM is the first part processed, the WHERE is the second clause processed.

SELECT …..

FROM ……

WHERE….

The Expression

When utilizing the WHERE clause, you will also need to create an expression that will be used as a comparison for limiting the rows returned.  How this expression looks will depend on the data type you are comparing.  Although there are many data types, you will see a few fare more frequently then others. These will include the string based data types like VARCHAR and CHAR,  the DateTime related data types and numeric data types.

This expression can be a number of things.  First of all it can be a simple value you are attempting to compare. In the statement below you will see after the WHERE clause, there is a column name, a comparison operator and a comparison value.

Comparing to a Value

In the statement below we are comparing the SalesOrderID column values to the number 4.  The expression must evaluate to TRUE or FALSE. If it evaluates to TRUE, those records are returned. Of course the means that if the expression evaluates to FLASE that row will not be included in the result set.

FROM …..

WHERE SalesorderID = 4

When comparing a character based data type such as VARCHAR, you of course must include the single quotes. We will talk about wild card searches later in this post.

FROM ……

WHERE LastName = ‘Smith’

In this case the column name is LastName, the comparison operator is the equal sign(=) and the value to be compared is ‘Smith’.

Comparison Operator Options

We have a number of options when deciding on what comparison operator to use. This list includes, but not limited to, the list below.

Equal sign ( = )

Minus sign ( – )

Plus sign ( + )

Not Equal ( <> )

Greater Than ( > )

Less Than ( < )

IN

NOT IN

More than one Comparison

Many times we will have to complete more than one comparison to capture the rows we are looking for.  When you look below you will see that this WHERE clause is looking for anyone that has the LastName of Smith and the StateOfBirth is WI.

WHERE LastName = ‘Smith’

AND StateOfBirth = ‘WI’

You can also use the keyword OR rather than AND. In the statement below, All the records with a last name of Smith will be returned.  In addition, all records that have the StateOfBirth of WI.

SELECT ….

FROM …..

WHERE LastName = ‘Smith’

OR StateOfBirth = ‘WI’

An example would be if I have a last name of Smith, but I was born in New York, I would be returned in the results because I have the last name of Smith, even if the StateOfBirth is Ohio.

Using a List

The WHERE clause also supports the use of the IN keyword.  This can be used if there a multiple values in the date set that I would like to see.

SELECT …..

FROM …..

WHERE LastName IN (‘Smith’, ‘Jackson’, ‘Denault’)

In this case, all records that have the last name of Smith, Jackson or Denault will be returned in the result set. As you can see, this also works with numeric data types.

SELECT …..

FROM …..

WHERE EmployeeID IN (1, 2, 3, 4)

The above statement can also be written using Less Than or Equal to comparison operator.

SELECT …..

FROM …..

WHERE EmployeeID <= 4

Using the NOT Keyword or <>

Just as you can search for values that are equal to something, you can also search for something that is not equal to.  How you do this depends on if you are excluding a single value or a list of values.

If you are comparing a single value, you simply use this, <>.

SELECT …..

FROM …..

WHERE EmployeeID <> 4

This also works for character based data types as well.  The statement below will return all records that have a LastName of something other than Smith.

SELECT ….

FROM …..

WHERE LastName <> ‘Smith’

What Else Can be Used the WHERE

As mentioned before there are a number of ways to pull data from a table and limit the number of rows using the WHERE clause.  There is another option I would like to mention.  Although it may not be the best option, using a sub-query is indeed an option.

A sub-query is nothing more that a query in the SELECT statement.

SELECT *
FROM Sales.SalesOrderDetail
WHERE ProductID IN (SELECT ProductID
FROM Production.Product
WHERE MakeFlag = 1)

If you look at the above statement, the sub-query is in green.  This will work just fine.  If the sub-query returns more than on row, it will still work because of the use of the IN keyword.

A sub-query is nothing more that a query in the SELECT statement.

SELECT *
FROM Sales.SalesOrderDetail
WHERE ProductID IN (SELECT ProductID
FROM Production.Product
WHERE MakeFlag = 1)

However, if I were to change the IN keyword to an equal sign, it will fail. You will see this error.

Msg 512, Level 16, State 1, Line 45

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The reason it is failing is because the equal sign expects just a single value, while we passed in a number of values.

Wildcard Searches

Sometimes when we are searching for something, we don’t know enough details about the value we are looking for.  This is where the wildcard searches come in to play.

There are a number of options where using a wildcard search, we will just focus on the use of the % character.  This the same as searching with an * to search for a file, like *.docx.

The statement below will return any state that starts with the word New.

WHERE State LIKE ‘New%’

This statement will return anything that starts with New and ends with the letter K.

WHERE State LIKE New%k’

The code below will return any state that has the work new in it.  Notice that the percent sign is also at the front of the value.  While all wildcard searches may not be the best performing, when placing the % at the beginning of the value, this will force a scan rather than a seek.  This is a potential performance problem and should be used with caution.

WHERE State LIKE ‘%New%’

Notice that these statements use the LIKE keyword.  If you use a wildcard search with the %, you must use the key word LIKE.  If you do not, SQL Server will process the string of ‘%new%’ as a literal and look for the word new and having the % both at the beginning and at the end.  It will not be processed as a wildcard.

Examples Below

These examples will work with the AdventureWorks2014 sample database.  Although it could work on a newer version of the database, I just haven’t tested it.

SELECT *
FROM Production.Product
WHERE Productid = 4

SELECT *
FROM Production.PRODUCT
WHERE Name = ‘Blade’

SELECT *
FROM Production.Product
WHERE MakeFlag = 1
AND FinishedGoodsFlag = 1

SELECT *
FROM Production.Product
WHERE MakeFlag = 1
OR FinishedGoodsFlag = 0

SELECT *
FROM Production.Product
WHERE Name IN (‘HL Road Frame – Black, 58’, ‘Headset Ball Bearings’)

SELECT *
FROM Production.Product
WHERE ProductID IN (1, 2, 3, 4)

SELECT *
FROM Production.Product
WHERE ProductID <= 4

SELECT *
FROM Production.Product
WHERE ProductID <> 4

SELECT *
FROM Sales.SalesOrderDetail
WHERE ProductID IN (SELECT ProductID
FROM Production.Product
WHERE MakeFlag = 1)

SELECT *
FROM Sales.SalesOrderDetail
WHERE ProductID = (SELECT ProductID
FROM Production.Product
WHERE MakeFlag = 1)

–This well return all records have a Name starting with Blade.

SELECT *
FROM Production.PRODUCT
WHERE Name LIKE ‘Blade%’

–This well return all records have the word name in the product name somewhere.

SELECT *
FROM Production.PRODUCT
WHERE Name LIKE ‘%Blade%’

–This well return all records have the a name that starts with the letter A and ends with the letter r.

SELECT *
FROM Production.PRODUCT
WHERE Name LIKE ‘A%r’

This may conclude my series on the Anatomy of a Select statement.  During this series of posts, I covered the processing order, SELECT, FROM, WHERE, GROUP BY\HAVING and ORDER BY.

Thanks for visiting my blog..I really hope you learned something as you read it!

 

 

 

 

 

 


 

 

 

 

Where are the events for Extended Events stored

Today I received an email from someone who attended my Extended Event session on Boston last fall.  He was asking what table stores information about what Extended Events exist on the server.  The answer is sys.dm_xe_objects data management view.

In this DMV, obviously there are a number of columns.  This post will focus mostly on the object_type column.  This column has a number of potential values, including Event, Target and Action.  When setting up a SQL Server Extended Event session these are a few of the key items that need to be filled out.

Let’s just take a few moments to identify what these are.

Target – This is where the data captured by the Extended Event session will be stored.  Many times you will find the target to be the event_file target, although from time to time you will find the data going to other targets.  The data will be stored in an XML format. See the image below.

 

Actions – These are also referred to as Global Fields.  These are the fields that many of the events have in common.  You can decide what data you would like to collect.  These differ from the Event Fields in the sense that are shared with all  the events.  Event Fields are data points that are specific to that event.  Sometimes you will find a particular data point in both the Actions and Event Fields.

Events – This is the focus of this post.  Events are things that happen on the server and you would like to capture the what, when and other information about the event.  As each release of SQL Server comes out, there are more and more events included. The image below is an example of some of the events you might want to use.  In this case they are related to Deadlocks.

Now back to the purpose of this post, where is this information found in the system tables.  As stated above, the information can be found in the sys.dm_xe_objects DMV.

If you run this query you will see all the events for that instance.  When I run this on my SQL Server 2017 server I get 1,505 events.  Although, your results might be slightly different depending on version.

SELECT Name
, Description
FROM sys.dm_xe_objects
WHERE object_type = ‘event’

Here what the data set looks like.

There are really two columns that are important for this post, the Name and Description columns.

Thank you for stopping by and a big thank you to the attendee that emailed me the question.

 

PIVOT and dm_os_performance_counters

Dm_os_performance_counters is a great Data Management View that I use quite often.  You can find a ton of information that can help you identify performance bottlenecks in your system.  It can be used to view Page Life Expectancy, Free list stalls/sec, Lazy writes/sec, Batch Requests/sec, SQL Compilations/sec,  SQL Re-Compilations/sec, Cursor Requests/sec, Checkpoint pages/sec as well many other key data points.  All of which can play a role in the effective identification of performance bottlenecks.

First of all, let’s cover a bit of background on this DMV.  It has been around since SQL Server 2008 and will work in Azure SQL Database as well as Azure SQL Data Warehouse.  Although in the Azure SQL Data Warehouse it has a slightly different name, sys.dm_pdw_nodes_os_performance_counters.  The use of the DMV requires view server state permissions. There are a number of columns that are important, object_name and counter_name are just a couple.  There is a column named cntr_type column.  This is the column that will help us identify the type of each counter.

There are five counter types, although we will only discuss two of them here.  You can find more information on the other types here, link.  The first one has a counter type value of 27269576. This counter type is cumulative since the last time the SQL Server service was started.  An example of this type of counter is Batch Request/sec.

The other counter type have a value of 65792.  This counter type is the value as it stands right now.  An example of this is Page Life Expectancy, which changes constantly as normal activity takes place on the server.

This post focuses on the counter type that is cumulative.  The challenge that this counter type presents is that although it may be a value greater than 0, it is something that may not be happening at this point in time. This is what we need to determine, is this still happening or did it happen in the past.

Free List Stalls/sec is an example of this counter type.  This counter is one of several that can help us determine if there is memory pressure on the server. If the current value is 30,000 and the service was last restarted 3 months ago, we don’t really know when these took place.  So what we want to do is determine if they are still happening.  This is where the PIVOT can help.  If you simply pull from the DMV you will get a single row for many of these types of counters.  Even if you use the WAITFOR to capture the value multiple times and place it into a table, it will look something like this.  Notice each row is 10 seconds apart.

I think this presents a few challenges, the biggest being how can I easily tell if the event is still happening.  Another challenge is when you have two counters that should be looked at as a pair, like Batch Requests/sec  and SQL Compilations/sec.  I want to be able to easily look at the data and see the values as they change over a period of time. I want it to look something like this.  The counter names on the left and the times of capture along the top.  As you can see, in this example the capture of the data took place every 10 seconds.

This can be accomplished by using the PIVOT command. The first thing we do is to declare a counter variable and a table to store the values.

DECLARE @iCount AS SMALLINT
CREATE TABLE #CounterValues
( Counter_name VARCHAR(50)
, Currentvalue INT
, DateTimeOfCapture DATETIME)

Then set the counter variable to 1, although you can set this to 0 if you like.  This variable will server as our counter so the WHILE knows when to stop.

SET @iCount = 1

The next step is to write the code to capture the counter values and insert the data it the temporary table created above.  Because we need to capture the values over a period of time, the WAITFOR DELAY is used.  In this case the delay is 10 seconds, although you can change this to suit your needs. Of course, don’t forget to increment the counter variable. You will need to determine what counters you would like to capture.  Notice in the WHERE clause, we are looking for an instance_name of ” or ‘_total’.  This will allow the code to only capture one row for each counter.  The number 10 is the number of times we want to capture the counter values.  If you want to capture the data more frequently, simpley modify the number of seconds in the WAITFOR DELAY.  Here is link to my post in this topic, WAITFOR.

WHILE @iCount < 10
BEGIN

INSERT INTO #CounterValues
SELECT Counter_Name
, cntr_value
, GETDATE()
FROM sys.dm_os_performance_counters
WHERE counter_name IN
( ‘Free list stalls/sec’
, ‘Lazy writes/sec’
, ‘Batch Requests/sec’
, ‘SQL Compilations/sec’
, ‘SQL Re-Compilations/sec’
, ‘Cursor Requests/sec’
, ‘Checkpoint pages/sec’)
AND instance_name IN (,‘_total’)

WAITFOR DELAY ’00:00:10′

SET @icount = @icount + 1
END

Then we need to declare two variables, one to hold column names and the other to execute the dynamic SQL.

DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX);

Now we can into the meat of the PIVOT.  We want to capture the column names from the time of each cycle of data capture above.

SET @cols = STUFF((SELECT DISTINCT ‘,’ + QUOTENAME(
CONVERT(VARCHAR(40),LEFT(CONVERT(CHAR(40),DateTimeOfCapture, 109) , 20))) AS DateTimeOfCapture
FROM #CounterValues
ORDER BY DateTimeOfCapture ASC
FOR XML PATH(”), TYPE
).value(‘.’, ‘NVARCHAR(MAX)’)
,1,1,)

The above code will allow us to use the time of data capture as column headers as seen below.

If you print the @cols variable it will look something like this. You can see a series of dates with times separated with a comma. These values will now become the column names for our final result set.

[Mar 19 2019 11:17:00],[Mar 19 2019 11:17:10],[Mar 19 2019 11:17:20],[Mar 19 2019 11:17:30],[Mar 19 2019 11:17:40]

Now that we have our column names, let’s finishing up with the PIVOT.  Because the column names are created dynamically, we will need to use Dynamic SQL to complete it.

SET @query = ‘SELECT Counter_Name,’ + @cols + ‘ from
(
SELECT Counter_Name, currentvalue, LEFT(CONVERT(CHAR(40),DateTimeOfCapture, 109) , 20) AS TimeOfCapture
FROM #CounterValues
) x
PIVOT
(
SUM(Currentvalue)
FOR TimeOfCapture IN (‘ + @cols + ‘)
) p ‘

EXECUTE(@query)

If you use these blocks of code together you will get what I think is the desired result. Notice the times across the top and the counter name on the left.

Here is a link to the complete code, PIVOTDMV.

Thank you for stopping by my blog and I hope you will find this useful!

sp_helptext

If you need to see the definition of an object you have several choices.  First, you could right click on the object and generate the script.  Another option you have is to use sp_helptext.  This system stored procedure can be used on any version of SQL Server since 2008, this includes Azure SQL Database and Azure SQL Data Warehouse.  This stored procedure can be used to obtain the definition of a number of objects, however it does not work on all object types.

According to the Microsoft documentation you can use this on the following object types:

  • User-defined rule
  • Default
  • Unencrypted Transact-SQL stored procedure
  • User-defined Transact-SQL function
  • Trigger
  • Computed column
  • CHECK constraint
  • View
  • System object such as a system stored procedure

The procedure has two parameters, @objname and @columnname.  The first parameter, @objname is the name of the object you are seeking to get the definition.  The second parameter, which is optional,  can be used to get the definition of a computed column.

When using the stored procedure you must have the rights to view definitions of objects.

We will start with the obvious, the first parameter, the name of the object.  There just a few rules regarding this parameter.  First of all, the object must exist in the current database.  If the object is not in the dbo schema, you will need to include the schema and include single quotes like below.

sp_helptextsales.SalesOrderDetail

If you include the schema in the object name and do not add the quotes you will get following error.

sp_helptext dbo.uspGetBillOfMaterials

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘.’.

If the object is in the dbo scheme it will look like this, single quotes not needed.  Although quotes are not required, it will also not fail if you include them.

sp_helptext uspGetBillOfMaterials

When you execute the above statement you will get these results.  Sometimes it might be nice to see it in the grid, however I find it better to send the results to text instead.  It is easier to copy to the clipboard.

When you send the results to text, SQL Server will add the word text at the top, this is actually the column name of the result set.

 

I really like to use sp_helptext when I have multiple objects that I need script. However, there is a bit of a catch.  If you try to run it without the GO keyword between you will receive this error.

The second parameter only works on calculated columns.  Below you will find the code.  Notice that the column name is not in quotes.  It will work either way, with or without quotes.

sp_helptext @objname = N’Sales.SalesOrderHeader‘, @columnname = salesordernumber ;

This is the results of the above statement:

(isnull(N’SO’+CONVERT([nvarchar](23),[SalesOrderID]),N’*** ERROR ***’))

If you attempt to run this against a column that is not a calculated column you will get this error:

Msg 15646, Level 16, State 1, Procedure sp_helptext, Line 75 [Batch Start Line 0]
Column ‘orderdate’ is not a computed column.

Just a few additional comments.  Sp_helptext will not work on encrypted objects.

This next topic may be in a future blog post, however, I did want to mention there as well.  There is another option. This code will return the definition of the uAddress trigger.

USE AdventureWorks2014;
GO
SELECT OBJECT_DEFINITION (OBJECT_ID(N’Person.uAddress’)) AS [Trigger Definition];
GO

When you run this, you will see the definition of the trigger.

I really do hope you learned a little something, that is why I do this, to help be become better at their jobs..  Thank you for visiting my blog.

 

 

 

 

 

 

 

Trace Flag: Global vs Session

Trace flags can be used for many things.  They can be used to capture information about what is happening.  For example trace flags 1204 and 1222  can be used to capture information about deadlocks. Trace flags can be used to change the behavior of SQL Server for example, trace flag 3226 can be used to suppress successful backup messages in the error log.  You can even use trace flags to change the behavior of the TempDB, trace flags 1117 and 1118 can help keep the TempDB files close to the same size by changing how the data files grow.

There are many more trace flags that can be used.  Here is a link to a Microsoft document that describes each of the trace flags, click here.

In order to use trace flags they must be enabled first.

The first step would be to determine which flags are currently enabled.  This can be done with the DBCC TRACESTATUS statement.

If you run this statement to see what trace flags are enabled.

DBCC TRACESTATUS(-1);
GO

Notice the -1 in the parameter, the value tells SQL Server to return information on all enabled trace flags.  You will get one of two results. You will either get the list of trace flags that are enabled or you will get this statement.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If you receive the above statement, it means that there are not any trace flags enabled at the moment. However, let’s say I have a number of trace flags enabled and I just want to check the status of one or more specific trace flags, you can use the code below.

DBCC TRACESTATUS (2528, 3205, 1204);
GO 

This statement accepts the trace flags you are looking for in a comma separated list.  In in the above statement you will have a set of three rows returned.  The results are below.  Notice there are 4 columns, the TraceFlag number, status, global and session.  The trace flag number is obvious as is the status.  In the status column it will either be a 1 or 0, enabled or disabled.

This brings us to the point of this post.  The third and forth column of the result set, Global and Session. These columns identify if the trace flag is turned on globally or just for the current session.

If you run this code the trace flag will be turned on for the session.

DBCC TRACEON(2528)

This can be confirmed by running this.

DBCC TRACESTATUS (2528, 3205, 1204);
GO

Notice that the value for the 2528 trace flag for Session is set to TRUE.

So what does it mean to when a trace flag is turned on just for a session?  It means that the trace flag is only active for the current session and is not visible from other sessions.  The image below show that 2528 is not active.  The trace flag was enable for SPID 62 and can not be seen from session 69.

In order to enable the trace flag globally, you will need to make one simple change to the DBCC statement.

DBCC TRACEON(2528, -1)

You simply have to add the -1 to the TRACEON statement as in the above statement.  When you run TRACESTATUS again, you will see that the flag under Global has changed to true.  This trace flag was enabled for SPID 62.

If I run TraceStatus in SPID 69, I see that the session is indeed enabled globally. Notice that trace flag 2528 is not enabled for SPID 69, remember we enabled that only for the session, in this case SPID 62.

You may have the question of, what happens when I restart the SQL Server service.  The answer is simple, the trace flags will no longer be enabled.  If you would like to have the trace flags enabled upon start up, you can add the -T command line startup option for SQLServr.exe.  One thing that is important here is for you to assure that the trace flags you would like to have enabled to be enabled again when the service is restarted.

Per Microsoft, this is a best practice, link to MS.  In this same document, Microsoft recommends that you not run TRACEON while users are connected to the system, otherwise you could see unpredictable behavior.

Now let’s say you want to disable the trace flags.  To do this you will need to use the TRACEOFF statement, like the one below.  Notice the -1 parameter is included when you want to turn the trace off globally.

DBCC TRACEOFF(2528, -1)

To turn off a trace flag for a session use this

DBCC TRACEOFF(2528)

You can also enable and disable multiple trace flags with the same statement. The desired trace flags will need to be listed and separated by a comma.

Here is an example of how to enable multiple trace flags at the same and as well as check the status.

DBCC TRACEON(2528, 3205, 1204, -1)

DBCC TRACESTATUS (2528, 3205, 1204 );
GO

Now comes the challenge, what trace flags should I enable.  There are many posts on different blogs that provide many great recommendations. In my opinion, before enabling a trace flag you should thoroughly understand what it does and if there is a potential for a negative impact on performance.

Thank you for stopping by my blog and hopefully you learned something.