Track Azure SQL Database Load with sys.dm_db_resource_stats

calendar Nov 1, 2026 / · 9 min read · sys.dm_db_resource_stats sys.resource_stats sys.dm_elastic_pool_resource_stats sys.server_resource_stats azure sql database dmv dtu vcore elastic pool performance monitoring  ·
Share on: linkedin copy

When an Azure SQL database slows down, the first thing to find out is whether it has hit the limits of its service tier. sys.dm_db_resource_stats answers that with one row for every 15 seconds, even when the database is idle, and keeps about an hour of them. The three queries below return the raw rows, a DTU-style peak for each interval, and an average and maximum for the hour.

Purpose and Overview

The DMV arrived on September 11, 2014. At the time the Basic, Standard and Premium service tiers were in preview, and customers were asking how to decide which tier a workload needed. The new view exposed fine-grained, real-time resource consumption data to answer that.

Every value in the view is a percentage of the maximum allowed for the service tier or performance level the database runs at. A reading of 100 means the database is at its own cap for that resource. There is no minimum SQL Server version to state, because the view exists only in Azure SQL Database and SQL database in Microsoft Fabric. Azure SQL Managed Instance does not support it; query the sys.server_resource_stats catalog view there instead. Reading the view requires the VIEW DATABASE STATE permission.

For current-state analysis or troubleshooting, this is the view to query first. For anything older than the hour it holds, sys.resource_stats and Query Store take over.

Code Breakdown

Run these three queries while connected to the user database you want to measure, not master.

 1SELECT * FROM sys.dm_db_resource_stats ORDER BY end_time DESC;
 2
 3SELECT end_time,
 4  (SELECT Max(v)
 5   FROM (VALUES (avg_cpu_percent), (avg_data_io_percent), (avg_log_write_percent)) AS
 6   value(v)) AS [avg_DTU_percent]
 7FROM sys.dm_db_resource_stats;
 8
 9SELECT
10    AVG(avg_cpu_percent) AS 'Average CPU Utilization In Percent',
11    MAX(avg_cpu_percent) AS 'Maximum CPU Utilization In Percent',
12    AVG(avg_data_io_percent) AS 'Average Data IO In Percent',
13    MAX(avg_data_io_percent) AS 'Maximum Data IO In Percent',
14    AVG(avg_log_write_percent) AS 'Average Log Write I/O Throughput Utilization In Percent',
15    MAX(avg_log_write_percent) AS 'Maximum Log Write I/O Throughput Utilization In Percent',
16    AVG(avg_memory_usage_percent) AS 'Average Memory Usage In Percent',
17    MAX(avg_memory_usage_percent) AS 'Maximum Memory Usage In Percent'
18FROM sys.dm_db_resource_stats;

Raw rows, newest first

The first query returns every column, with the latest interval on top. end_time is a datetime in UTC and marks the end of each reporting interval. Convert it before you line it up against application logs written in local time.

Most columns read as their names suggest. avg_cpu_percent is a decimal(5,2) holding average compute use as a percentage of the tier limit. avg_log_write_percent is transaction log write throughput in MB/s as a percentage of the limit. These columns need a second look:

  • dtu_limit and cpu_limit: dtu_limit is NULL for databases on the vCore-based model. cpu_limit holds the number of vCores for the interval and is NULL for databases on the DTU-based model. Whichever one is NULL tells you the purchasing model.
  • avg_instance_cpu_percent: this measures the database engine instance hosting the pool, as a percentage of the instance limit, not the database. It is reported at one-minute granularity and includes CPU used by both user and internal workloads. Do not read it as your database's CPU.
  • avg_memory_usage_percent: this includes memory used for buffer pool pages and for storing In-Memory OLTP objects. Cached data pages count toward it.
  • xtp_storage_percent: returns 0 if the database does not use In-Memory OLTP.
  • avg_login_rate_percent: listed for information only, not supported, and future compatibility is not guaranteed. Keep it out of alerting.
  • avg_data_io_percent on Hyperscale: check the Data IO section of the resource utilization statistics documentation before trusting the number on a Hyperscale database.

The avg_DTU_percent value per row

The second query returns one row per interval with a single computed column. For each row, a correlated subquery uses a VALUES table constructor to build a three-row derived table from avg_cpu_percent, avg_data_io_percent and avg_log_write_percent, names it value(v), and takes MAX(v). The result is whichever of the three dimensions was closest to its limit in that interval, and the query labels it avg_DTU_percent.

When these percentages near 100% on a consistent basis, consider a higher performance level. One interval at 100 is a spike. A run of them across the hour is the signal. This query has no ORDER BY, so add ORDER BY end_time DESC when you want to read the timeline in order.

The hour summary

The third query collapses the hour into one row: the average and the maximum for CPU, data IO, log write and memory. Read the two numbers as a pair. A low average with a high maximum points to bursts. A high average means the steady load is too big for the tier.

The summary leaves out workers and sessions. Adding MAX(max_worker_percent) to the select list covers workers. max_worker_percent is the maximum number of concurrent workers (requests) as a percentage of the tier limit, and max_session_percent is the same measure for concurrent sessions. They matter because those limits fail in a different way. A workload that hits the CPU, data IO or log IO limit keeps getting resources at the maximum allowed level and sees higher query latency. That produces no errors, only a slowdown, unless the slowdown is bad enough that queries start timing out. A workload that hits the concurrent session or worker limit gets explicit errors on further connections or requests. If users report errors while the summary shows CPU at a comfortable level, add those two columns.

What "the last hour" means

None of the queries has a WHERE clause on end_time, because the view holds only about an hour of history. Three conditions shrink or change that window.

If the database failed over to another server within the last 60 minutes, the view returns data only for the time since the failover. In an elastic pool, the percentages are measured against the per-database maximum set in the pool configuration, not against the pool's total.

On a read-only replica, DMVs reflect the state of that replica. The replica_role column says where the numbers came from: 0 for the primary, 1 for a high availability (HA) secondary, 2 for a geo-replication forwarder, 3 for a named replica. It reports 1 when you connect with ReadOnly intent to any readable secondary. To check the session from the database side, SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability'); returns READ_ONLY on a read-only replica. Read scale-out is enabled by default on new Premium, Business Critical and Hyperscale databases, so a connection string with ReadOnly intent on those tiers lands on a secondary unless someone has turned the feature off. The exception is a Hyperscale database configured with zero secondary replicas, where read scale-out is disabled automatically.

Key Benefits and Use Cases

  • Tier right-sizing — run a representative workload for at least one hour, covering the expected range of production use, then compare the summary maximums with the current tier.
  • Live triage at 15-second grain — shows whether a slowdown reported a few minutes ago matches a resource cap, before you open query-level tools.
  • Purchasing model at a glance — the NULL in dtu_limit or cpu_limit sorts DTU and vCore databases when one script runs across a mixed estate.
  • Separate numbers for readable secondaries — the same queries over a ReadOnly-intent connection report the secondary's own load, so a reporting workload on a secondary is not mistaken for primary pressure.
  • Per-tenant view in a pool — each pooled database is measured against its own per-database cap, which shows when one tenant is pinned against its limit.

Performance Considerations

  • Small, fixed read: one row per 15 seconds over about an hour works out to roughly 240 rows (arithmetic from the interval and retention, not a documented row count), so the three queries are cheap to run repeatedly.
  • One-hour retention: rows older than about an hour are gone, so anything you need later has to be saved.
  • Failover resets the window: after a failover in the last 60 minutes, the summary covers only the time since the failover, a shorter span than the query appears to cover.
  • sys.resource_stats is not a live substitute: it lives in the logical master database, aggregates within five-minute intervals, and can take several minutes to show data. Idle databases may have no row for some intervals, and with read scale-out it returns data for the primary replica only.
  • Scope: Azure SQL Database and SQL database in Microsoft Fabric only. A monitoring script that also runs against Managed Instance needs a branch that reads sys.server_resource_stats there.

Practical Tips

  • Save the rows on a schedule — query the view every hour and store the results elsewhere. Polling more often than the roughly one-hour retention leaves an overlap so nothing is missed; deduplicate on end_time.
  • Alert on sustained CPU — CPU above 80% for extended periods is the point to start troubleshooting. A single 15-second interval above that is not.
  • When data IO hits the cap — there are two options: move to a larger compute size or service tier, or find and tune the queries using the most I/O.
  • Past incidents — connect to master and query sys.resource_stats, which keeps about 14 days at five-minute grain and is open to any role that can connect to the virtual master database. Use Query Store for root cause analysis of an issue that has already passed.
  • Pool-wide picture — query sys.dm_elastic_pool_resource_stats from any database in the pool. It reports usage for the whole pool, against pool limits that can be higher than a single database's limits.

Conclusion

sys.dm_db_resource_stats is the first view to query for live load on an Azure SQL database: 15-second percentages of the tier limit for the last hour, readable with VIEW DATABASE STATE. Run the three queries here during an incident, save the rows on a schedule, and move to sys.resource_stats and Query Store once the question is about anything older than an hour.

References

Posts in this series