How to Measure Resource Status on Azure? – Interview Question of the Week #264

Question: How do you measure recent resource consumption in Azure SQL Database? Query sys.dm_db_resource_stats in the database you want to investigate.

During a Comprehensive Database Performance Health Check, I want to know what happened before I connected. This short query gives me average and maximum CPU, data I/O, log-write and memory percentages for the recent history available in the view.

SELECT
    AVG(avg_cpu_percent) AS [Average CPU %],
    MAX(avg_cpu_percent) AS [Maximum CPU %],
    AVG(avg_data_io_percent) AS [Average data IO %],
    MAX(avg_data_io_percent) AS [Maximum data IO %],
    AVG(avg_log_write_percent) AS [Average log write %],
    MAX(avg_log_write_percent) AS [Maximum log write %],
    AVG(avg_memory_usage_percent) AS [Average memory %],
    MAX(avg_memory_usage_percent) AS [Maximum memory %]
FROM sys.dm_db_resource_stats;

What does the last hour mean?

The view records a row for each 15-second interval and retains approximately an hour of history. After a failover, the available history can be shorter. It isn’t a permanent record of the previous 60 minutes.

CPU, data I/O and log-write percentages are relative to the database’s resource limits. Memory reports the workload’s memory use, not a Windows memory reading. The maximum CPU value here is the highest recorded interval average, not the highest instantaneous spike.

I compare the average and maximum together. A high maximum with a modest average suggests a different investigation from consumption that stays near the limit. The query summarizes the database; it doesn’t identify the expensive statement. Query Store and individual interval rows help with that next step.

Scope: This query is for Azure SQL Database. It does not describe every Azure resource, and this DMV is not supported in Azure SQL Managed Instance. Managed Instance has sys.server_resource_stats. A SQL Server installed in an Azure virtual machine needs its SQL Server and operating-system monitoring tools.

Related questions: transactions per second, table row counts, procedure recompilation, TempDB spills, mapping a network drive, copying files and renaming sa.

If you have an Azure monitoring query that has helped you find a real problem, share it in the comments. I’d be happy to discuss it and credit your contribution.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.