Featured Database Articles
Posted January 8, 2019
Overview of Azure SQL Database Performance Monitoring
By Marcin Policht
Transitioning to the Platform-as-a-Service model typically implies relinquishing certain degree of control over your computing environment. One of the primary concerns related to this transition is diminished level of transparency providing insights into performance of cloud-resident workloads. Fortunately, with Azure SQL Database, you have a wide range of options that address this concern, allowing you to identify and remediate overwhelming majority of performance-related issues.
Traditional mechanisms that used to be employed by database administrators to monitor their on-premises SQL Server instances, including SQL Profiler and SQL Trace are not only absent in Azure SQL Database (although SQL Profiler is supported by Managed Instance), but have been deprecated SQL Server 2017. However, Microsoft offers several cloud-based features that deliver functionality exceeding the capabilities of their legacy counterparts:
- Metrics and Diagnostics Logging - By default, you have access to the core performance metrics of Azure SQL Database (such as DTU percentage, CPU percentage, Data IO percentage, database size percentage, or deadlocks) directly in the Azure portal, accessible from the database blade or from the Azure Monitor blade. The metrics are collected every minute and are retained by the platform for 93 days. If you need to maintain access to them for an extended period of time, your options include archiving them to a storage account, streaming them to an event hub, or sending them to a Log Analytics workspace. The same options are available for storage of diagnostics logs, which provide visibility into a variety of aspects of database operations, such as blocks, deadlocks, timeouts, and errors.
- Query Performance Insights - This feature is intended for basic performance tuning and troubleshooting. Some of its primary benefits include simplified access (directly from the Azure portal) to detailed data exposing performance of individual queries and corresponding SQL Database Advisor recommendations. Query Performance Insights rely on Query Store to collect performance data (Query Store is, by default, automatically enabled for any new Azure SQL Database instance).
- Azure SQL Analytics- This Log Analytics-based solution (in preview at the time of publishing of this article) is geared towards more advanced monitoring scenarios. It relies on collecting metrics and log data and sending it to Log Analytics, which subsequently relies on the intelligence built into the solution in order to present a number of perspectives representing different aspects of Azure SQL Database performance (including drill-downs into database errors, timeouts, blocking events, query durations, and query waits).
- Intelligent Insights- This Artificial Intelligence-based solution implements continuous database monitoring that relies on collecting SQLInsights logs (which you can enable for individual databases by configuring their diagnostics settings) and designating one or more destinations as the persistent store for log data (which can be subsequently analyzed and reviewed). These destinations include an Azure Storage account, an event hub, a Log Analytics workspace, or a third party log analytics solution. When targeting Log Analytics, you can leverage Azure SQL Analytics solution as the graphical interface for presenting database performance issues. Intelligent Insights is capable of detecting temporary deviations from a longer-term workload baseline, evaluating their impact, performing root cause analysis, and providing remediation options.
In addition, you have the ability to leverage tools and techniques meant to replace SQL Profiler and SQL Trace in SQL Server-based deployments, which offer the same or superior functionality, while, at the same time, result in a considerably lower performance overhead. The primary offerings in this category include Extended Events, Database Management Views (DMVs) and Query Store:
- Extended Events offer performance monitoring functionality capable of correlating event data from SQL Server, the underlying operating system, and database applications. They are exposed in the SQL Server Management Studio via New Session Wizard and New Session interfaces. Some of the extended events are available via dynamic management views. Extended events in Azure SQL Database constitute a subset of those available in SQL Server.
- Dynamic Management Views (DMVs) are objects in the sys schema that provide insight into database state and operations. They are divided, based on their scope, into three main groupings: database-related, execution-related, and transaction-related. In the context of monitoring Azure SQL Database, some of the most commonly used DMVs include:
- sys.dm_db_resource_stats residing in each of the user databases, intended to provide resource consumption statistics, such as the average CPU utilization along with the corresponding compute limit expressed in either Database Throughput Units (DTUs) or vCores (depending on the purchasing model of the Azure SQL Database deployment), average data I/O utilization for data and log files, average memory utilization, as well as statistics representing usage of concurrent workers, sessions, and in-memory OLTP. Data is collected in 15-second intervals and maintained for 1 hour.
- sys.resource_stats residing in the master database, intended to provide statistics representing the same metrics as the sys.dm_db_resource_stats DMV with less granularity (data is collected and aggregated every 5 minutes) and longer retention period (of 14 days), but for all user databases on the same logical server.
- sys.dm_exec_sql_text residing in the master and user databases, useful in scenarios that require identifying currently running queries with the most significant impact on CPU utilization.
- Query Store provides the ability to query current and historical performance-related data, including details regarding query plans, as well as run-time and wait statistics collected by default every hour (you have the option of changing this interval to 1, 5, 10, 15, 30, or 1440 minutes). Collected data is retained by default for 30 days with the Standard and Premium tiers of Azure SQL Database and for 7 days with Azure SQL Database Basic. The retention period is customizable but is constrained by the maximum size of the Query Store determined by the MAX_STORAGE_SIZE_MB configuration setting (100 MB by default).
Finally, you also have the option of leveraging intelligence built into the underlying platform in order to apply performance-related remediating actions. This functionality is offered by SQL Database Advisor, which provides performance recommendations and assists with their implementation and by Automatic Tuning that continuously monitors executing queries and dynamically adjusts database settings to optimize their performance. We will be exploring these features in more details in upcoming articles published on this site.
MS SQL Archives