31 January 2019
31 January 2019

Monitoring Azure SQL Database with SQL Monitor

SQL Monitor provides a full suite of monitoring specific to Azure SQL Database and SQL Elastic Pools. You can therefore use it to monitor all your databases, regardless of whether they are on local physical servers or virtual machines, or in the Azure cloud.

Azure SQL Database is just a SQL Server database under the covers, and so many of the standard database monitoring mechanisms and metrics are available. Therefore, SQL Monitor can collect these metrics for all your databases and make the data available in a standard way for your whole SQL Server estate. The difference is that you’re now monitoring a SQL Server database on a co-tenanted Azure machine over which you have no responsibility, or access. You don’t, for example, have access to the underlying storage, or to the VM that is hosting the Azure SQL Database. This means that some metrics are irrelevant or off-limits. For example, there is no equivalent database metric for a server characteristic such as disk space, or machine processor time, and Azure SQL Database does not even support SQL Agent jobs.

However, SQL Monitor will collect Azure-tailored metrics that will tell you how the database is consuming resources such as storage space, and CPU, and there are also a few new metrics to collect that you wouldn’t normally require. Azure SQL Database will have unique “compute resource” constraints, as measured by the number of DTUs, or the vCore quota, available with your chosen performance and storage tier. This requires a few special types of metrics and associated alerts so that SQL Monitor can alert you very quickly to performance problems with your Azure SQL Databases, and let you know if it’s a compute resource problem, and which resource (data IO or log IO) is the constraint. If it is, you’ll also have the data you need to decide if it’s a problem that you can solve with tuning and indexing, or if you need to go to a higher service tier.

This article will show you how to integrate your monitoring strategy to take account of the differences of Azure SQL Database. I do not cover other Azure-based SQL Server hosting options that are simpler to integrate, such as Azure SQL Managed Instances or SQL Server running in an Azure VM.

Why Use a Monitoring Tool?

If you use only Azure SQL Database, then you have the option of using the built-in monitoring that is supplied in the Azure Portal to obtain the overview of your entire Azure SQL Server estate, and to see it within the context of your overall Azure resources. If, for example, you have mixed types of Azure-hosted databases, including MySQL MongoDB or PostgreSQL you can also include these in your Azure Monitoring.

However, this versatility comes at a cost. Only a limited number of database metrics are gathered for you automatically by the Azure system. Alerts require much more of your work to set up. It is going to require you spending lots of your time, your most precious commodity, to make it complete. This means there are advantages to using a tool like SQL Monitor, which does all this work for you, even if you do have an “all-Azure” estate.

If, as is more common, you have a hybrid SQL Server estate, with some servers and services in the cloud and some local, then if you’re using the Azure Poral monitoring, you would still need to separately monitor all your non-Azure database estate.

Instead, you need a single monitoring tool that integrates the supervision of your on-premise and your Azure servers and databases, and so provides an overall landscape with a common set of metrics and alerts. If, for example, an Azure SQL Database is unavailable, then SQL Monitor will let you know about it immediately, just as it would for any monitored instance. It also has built-in all the performance metrics and alerts you need to monitor expensive queries, blocking, deadlocking, and so on, on your Azure SQL Databases. Of course, you still have the Azure Monitor tools, and others, to assist if you need to drill deeper into a problem to which SQL Monitor has alerted you.

Monitoring Azure SQL Database

Under the covers, Azure SQL Database is just a SQL Server database and so you will have the same concerns about monitoring performance, behaviors, errors and the like. As such, to a large degree SQL Monitor looks and behaves with Azure SQL Database largely the same way as it does with SQL Server running on your local hardware or within a virtual machine. However, you will also see differences, and you’ll also have special monitoring concerns depending on exactly how you have set up your Azure SQL Databases, what purchasing model and service tier you’ve chosen, and so on.

This article does not attempt to offer any advice on how to migrate to Azure SQL Database, nor on what options you should choose. I’ve merely set up four Azure SQL Databases to reflect some of the available options and models:

  • Standalonedb – this is, as you might assume from the name, a single Azure SQL Database and is in the Basic Tier and charged by DTU
  • RedgatePool – an elastic pool with three databases which are in a higher performance tier and billed by vCore.
  • Firstshareddb and secondshareddb – are two single database on the same logical server which are in a higher tier and billed by DTU again.

Certain resources limits will apply to your database and pools, either DTU-based or vCore-based. If you hit them, you’ll start seeing severe performance issues, and even connection problems. Of course, SQL Monitor has metrics and alerts that will allow you to see these problems coming and plan for how to avoid them.

Overviews

Let’s start at the Overviews screen in SQL Monitor, where you can see the Azure SQL Databases and database pool that I set up for monitoring, as described above.

The cloud icons at the top right of each of these tiles indicate that you’re monitoring a cloud-based resource, and the icon at the left tells you whether it’s a single Azure SQL Database or an elastic database pool.

Other than that, in most respects the overviews look and behave similarly to the overviews for any other SQL Server instance. You can tell if a database is online or off. If any alerts have been generated, you’ll be able to see those. You also get immediate feedback on the general behavior of a given database. For example, there is some activity on the standalonedb database as shown here:

You also get the additional measure of DTU, or Database Transaction Units, which we’ll cover in more detail shortly, and the measures of CPU, Data I/O and Log I/O, which are the metrics that make up DTU.

We’ll review the SQL Monitor metrics and alerts, using the standalonedb as an example. I’ll cover elastic pools separately and point out any differences. A server with shared databases has some areas where management is easier, but basically works the same as a standalone server and database, so I won’t cover that separately.

Resource metrics

Clicking on a database will open a detailed view of the behavior of that database:

As you can see, SQL Monitor includes a DTU utilization metric for Azure SQL Database, alongside the CPU Utilization metric. Since billing within Azure can be either by the DTU or by vCore, you need to measure both CPU and DTU. You can see straight away that earlier in the day the CPU and DTU usage was quite different from the normal behavior.

Below that we see the data I/O and log I/O utilization for the database. All these are expressed as a percentage of this database’s maximum allowable utilization. If a database reaches its maximum quota for any of these resources, you’ll start to see severe query performance problems, with queries getting queued. You need to track use of these resources carefully, and as you’ll see shortly SQL Monitor also provides built-in and configurable alerts on these metrics.

For a SQL Server instance running on a physical or virtual machine, you’d generally see resource metrics such as Disk I/O and Memory here. While a lot of the measurements within Azure SQL Database are the same, many are quite different because the term ‘database as a service’ means that you won’t be looking at a server instance, but instead just have a database, and the resources its currently using.

Of course, you still need to measure I/O, memory use and more, so SQL Monitor captures these database resource usage metrics from Azure SQL Database-specific DMVs and other metrics available in Azure. For example, it captures CPU utilization data from the sys.dm_db_resource_stats DMV.

Top 10 Expensive Queries and their execution plans

If resource usage is high, and especially if you’re approaching resource limits, you’ll want to look at the longest-running, or resource-intensive queries that are active on the system. Scrolling, further down the overviews page, we find them. In my case there’s only a few queries currently generating a test load but on a larger system, you would see the normal top 10 queries:

You can sort queries by execution count, duration and all the rest. You can drill down on the query to get the execution plan, associated T-SQL statement, and the query history:

All this is the same information you get from SQL Monitor for any SQL Server instance.

Wait Statistics

In addition to the queries, you can also see the wait statistics:

Again, the behavior here is exactly as you would expect within a SQL Server instance. The only difference is that these waits are from sys.dm_db_wait_stats instead of from sys.dm_os_wait_stats because this is an Azure SQL Database.

SQL Server performance metrics

Scrolling down even further you get most of the metrics you expect to see, demonstrating general performance of the database:

It is as important to track metrics such as lock and latches, batch requests, SQL compilations in Azure as it is inside a SQL Server instance, so all that data is made available.

Analyzing trends and correlations in database metric values

SQL Monitor shows the complete list of Azure SQL database metrics we collect on the Analysis screen:

This means that, just as with any other metric from any SQL Server instance, we can plot and analyze relevant metrics for our Azure SQL Databases and create baselines for comparison with previous behavior over a similar period.

Here’s a baseline showing CPU percentages for a 1-hour period, compared to the previous hour, and extending that comparison the previous 7 days:

The lighter line on the graph indicates that the CPU was maxed for an extended period, and there was also a brief period where the CPU was below the reasonably steady state that this graph shows is normal for this database. This provides information that could be useful in cparing current to previous behavior within Azure SQL Database, just as you would within a regular SQL Server instance.

Monitoring SQL Elastic Pools

Let’s now look at some of the metrics that SQL Monitor provides specifically for monitoring databases running in an elastic pool, using the RedgatePoolas our example, which contains three databases.

For Pools, SQL Monitor now displays the overall resource usage across the pool. Clicking on RedgatePool, we’ll see the aggregate behavior for the various individual resources, across the three databases in the pool:

When working with databases in a pool, you’re more at risk from hitting session or worker thread limits, at which point you’ll start seeing connection errors. You could also hit limits for database space used.

Scrolling down we get to the list of databases that make up the pool, their DTU use, transactions and size:

Clicking on any of them will open the database details and the behavior within SQL Monitor is then the same as I’ve already outlined. Tracking the database resources, which are subject to strict “caps” at the pool-level, is a fundamental part of the monitoring, so that you can see how close you are to the limits, and act, as required.

Again, all the elastic pool metrics are available in the Analysis tab of SQL Monitor:

As with every metric within SQL Monitor, we’ve written some guidance around what the metric is showing you and how to interpret it:

Alerts

Most alerts that you’re going to want for an on-premises SQL Server instance, an instance running inside a VM locally or on the cloud, or even an Azure SQL Database within a SQL Elastic Pool are going to be the same. Errors, deadlocks, these things don’t change. However, since some metrics are captured in different ways and some metrics have different meanings, a different set of alerts are included out of the box for both the Azure SQL Database and SQL Elastic Pools:

This ensures that if you exceed any of the defined metrics that are unique to Azure SQL Database, such as a DTU limit, a worker thread limit, you’ll be made aware. These are in addition to all the standard alerts, so no extra work is required to set them up. However, like the standard alerts, you may want to tune these to ensure maximum signal and minimal noise.

For example, earlier I noted that my CPU had been maxed for a period, and here are the alerts SQL Monitor generated from this event:

Finally, even standard errors and problems such as Deadlocks will be captured by SQL Monitor:

Reports

SQL Monitor comes with a reporting tool that enables you to set up your own reports. This tool works from the data collected, so you can easily incorporate Azure SQL Databases into your reporting or set up special reports just for Azure SQL Database. I can take the standard tiles and the metrics we gather to create a report. For example, if I wanted a report with the DTU usage, I just add a tile, like this:

I can combine that with a tile for the Pool usage, and others, to come up with a report that is unique for Azure SQL Database. In the following example, I’ve built a report showing the slowest running queries across all my Azure databases, the CPU percentage from the pool databases, and the collected DTU use for each of the databases that is billing by DTU:

In short, this is the exact same behavior as all other reporting, just with metrics gathered from Azure SQL Database. You could easily customize this to do whatever you need.

Conclusion

It is becoming increasingly important to have a broad view of all your SQL Server databases with a consistent way of checking for problems and stresses. SQL Monitor has a full suite of monitoring specific to Azure SQL Database and SQL Elastic Pools, so you can easily extend an existing SQL Server monitoring strategy to include Azure SQL Database.

SQL Monitor allows you to bring your whole estate into a single monitoring landscape that has a consistent interface, whether you have an all-Azure environment, or, more likely, a mixed database environment of some servers and services in the cloud and some local.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    SQL Monitor Quick Tip: Using the SQL static code analysis performance rules

    Static code analysis parses the source code, checking the syntax for compliance with a built-in set of rules. These rules are designed to encourage good coding practices and, applied during development and testing work, help you minimize the number of ‘code smells’ that creep into your application and database builds. For SQL, these ‘smells’ could

  • Article

    SQL Monitor plans for the second half of 2017

    SQL Monitor’s dev team has made huge improvements to the product over the last year. In the first half of 2017 alone, they released reporting capabilities, support for collecting metrics from VMWare hosts, significant improvements to performance and scalability, improved configurability of alerts, as well as dozens of smaller enhancements. Since we’re about half way

  • Article

    Checking for Database Drift using Extended Events and SQL Monitor

    You need to make sure that nobody makes unauthorized changes to the metadata of your production databases. Governance and testing processes during the deployment pipeline are there to ensure that any proposed database changes aren’t going to open any legal or security issues, and that they won’t introduce any bugs as a side effect. Once

  • Webinar

    The importance of monitoring your Azure SQL Database

    In this demo webinar, Microsoft Data Platform MVP and PASS President, Grant Fritchey shows you the important metrics that you need to track on your Azure SQL Databases.

  • University

    Take the SQL Monitor course

    This course takes you from installation and configuration, all the way up to getting the most out of the advanced features in SQL Monitor to help you proactively monitor your SQL Server estate. Learn how to explore in-depth issues in your environment, run an analysis, manage alerts, create custom metrics, and more.

  • Forums

    SQL Monitor Forum

    Real-time SQL Server performance monitoring, with alerts and diagnostics