One of the primary benefits of Platform-as-a-Service (PaaS) offerings hosted by Microsoft Azure is considerably minimized management overhead. This principle also applies to Azure SQL Database, which eliminates the need to administer and maintain the underlying hardware, operating system, and the database management platform. However, such hands-off approach does not imply that you should fully relinquish the oversight of your database resources. In this article, we will present different methods that allow you to monitor performance and characteristics of your PaaS databases, ensuring that they behave in a manner that matches your expectations. In addition, you can use monitored data in order to optimize scaling and sizing of your deployment, ensuring that demands of your database-bound applications are addressed properly but at a minimum cost.
When deploying a new SQL Database, besides designating the regional datacenter that will provide its compute and storage services, you also need to choose its pricing tier, which can be set to Basic, Standard, or Premium. Your choice determines overall performance, maximum size, the range of recovery capabilities, as well as the price. The performance is measured in the form of Database Throughput Units (or simply DTUs), representing the relative capacity of individual tiers and reflecting their processing power, memory, and throughput characteristics. Additionally, each pricing tier is characterized by its benchmark transaction rate (number of transactions per hour measuring actual throughput of the corresponding performance level, as produced by the Azure SQL Database Benchmark simulating online transaction processing workloads), maximum number of worker threads, maximum number of sessions, maximum database size, as well as predictability (indicating consistency in response time).
Once the database is provisioned, you will be able to collect several metrics that should give you a basic understanding of its current state and ensure that it matches the expected performance level associated with the pricing tier you selected. The most straightforward method of accomplishing this objective involves the use of the Azure Management Portal or the Preview Portal. By default, both portals keep track of Deadlocks count, Storage amount (identifying the total database size), as well as the number of Successful and Failed Connections (incidentally, the preview portal also automatically includes DTU percentage). In order to monitor database utilization levels, you would want to also add at minimum CPU percentage, Data IO percentage, as well as Log IO percentage. For each metric, the Monitoring page in the management portal and the corresponding lenses or blades in the preview portal give you both numerical statistics and their graph-based equivalents illustrating the current (within one-hour intervals), average, minimum and maximum, as well as (whenever appropriate) total values. In addition, you also have the option to extend monitoring to connection attempts that were Blocked by Firewall or Throttled Connections as well as view any of the statistics listed above within the past hour, today, past week, or a custom time interval.
For any of the metrics you decided to monitor, you can define an alert (by clicking Add Rule in the command bar of the full management portal or Add Alert in the preview portal) which will get triggered when that metric crosses an arbitrarily chosen threshold within one of four predefined evaluation window intervals (the full management portal is limited to the last 15 minutes, the last 30 minutes, the last 45 minutes or the last hour, but the preview portal offers more flexibility, with the intervals ranging from 5 minutes to 24 hours). This will be reflected by an alert appearing on the Alert page within the Management Services node of the management portal and the Alert rules lense of the corresponding database blade in the preview portal. Alerts can be automatically forwarded to a designated mailbox (including mailboxes of the Service Administrator and co-administrators of the underlying subscription).
If your intention is to manage and view monitoring data without resorting to accessing portals, you can implement the equivalent functionality by leveraging two existing SQL Database constructs:
- The catalog view sys.resource_stats in the master database of the logical SQL Server instance where your user database resides. The view exposes a number of performance statistics (including usage_in_seconds, storage_in_megabytes, avg_cpu_cores_used, avg_physical_read_iops, avg_physical_write_iops, active_memory_used_kb, active_session_count, active_worker_count, avg_cpu_percent, avg_physical_data_read_percent, and avg_log_write_percent) aggregated over five minute intervals for each database hosted by the same logical SQL Server instance (which the master database is part of). Due to the delay inherent to collecting and calculating aggregated statistics, this method is typically employed to obtain monitoring data over extended periods of time for the purpose of analyzing longer term performance trends (data is preserved for 14 days). For example, the following T-SQL would allow you to view the aggregated statistics for a database named db1 from the last seven days (note that you have to be connected to the master database in order to execute it successfully):
SELECT * FROM sys.resource_stats WHERE database_name = 'db1' AND start_time > DATEADD(day, -7, GETDATE()) ORDER BY start_time DESC;
- The dynamic management view sys.dm_db_resource_stats in the target SQL Database. The view exposes a subset of performance statistics, including avg_cpu_percent, avg_data_io_percent, avg_log_write_percent, and avg_memory_usage_percent calculated over fifteen second intervals, so it is better suited for scenarios where performance analysis needs to be carried out in near-real time (the data is preserved for one hour). If these percentages remain near 100% utilization, you should consider improving performance by scaling out, scaling up, or optimizing your database design. The following T-SQL would allow you to view the most recent statistics in the current database:
SELECT * FROM sys.dm_db_resource_stats ORDER BY end_time DESC;
It is important to note that the functionality provided by dynamic management views extends beyond the monitoring features available in the management portals. In particular, you can take advantage of it to obtain additional information regarding the database and its objects, its connections, as well as individual queries and query plans. The first of these capabilities is facilitated by the sys.dm_db_partition_stats, which allows you to calculate both the database size and its individual objects by running the following two queries:
SELECT SUM(reserved_page_count)*8.0/1024 FROM sys.dm_db_partition_stats; GO SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024 FROM sys.dm_db_partition_stats, sys.objects WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id GROUP BY sys.objects.name; GO
Similarly, you can query the sys.dm_exec_sessions and sys.dm_exec_connections execution-related dynamic management view for active database connections, which allows you to identify values of login_name, host_name, session_id, or cpu_time associated with each of them:
SELECT *
FROM
sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections e
ON s.session_id = e.session_id
GO
If your intention is to monitor performance of query plans and identify long running queries, then you can leverage sys.dm_exec_query_stats dynamic management view. For more information regarding this subject, refer to the Microsoft Azure Documentation.
For larger SQL Database deployments that supplement your on-premises SQL Server environment in hybrid scenarios, you might want to consider enterprise-level solutions, such as System Center Operations Manager (SCOM) with Management Pack for Azure. The management pack relies on Azure REST APIs to collect instrumentation information about a variety of cloud resource types, including SQL Database. With SCOM, you can rely on standard health and performance monitors incorporated into the management pack or define your own that leverage dynamic management views described earlier. We will present this configuration in more detail in one of our upcoming articles.
