Monitoring an Azure SQL Database Using DMVs

DMVs return diagnostic data that can be used to monitor a database's health and performance.

Monitoring Database Metrics

The metrics available on the Azure portal can also be monitored using the sys.resource_stats DMV. This DMV returns the historical analysis for all the databases in an Azure SQL server. The data is collected and aggregated every 5 minutes and is retained for 14 days.

The following query returns the resource utilization from the last six hours:

Note

You can also copy the queries from the C:\Code\Lesson09\MonitoringDMVs.sql file.

The file location may change depending on where you have unzipped the code files.

-- Execute in master database

-- Get utilization in last 6 hours for the toystore database Declare

@StartTime DATETIME = DATEADD(HH,-3,GetUTCDate()),

@EndTime DATETIME = GetUTCDate() SELECT

database_name, start_time, end_time, avg_cpu_percent, avg_data_io_percent,

avg_log_write_percent, (

SELECT Max(v)

FROM (VALUES (avg_cpu_percent...