Wait statistics on Azure SQL Database: reading them per database and per pool
sys.dm_db_wait_stats vs sys.dm_os_wait_stats, the waits that matter in the cloud (log rate governor, resource governor, PAGEIOLATCH, CXPACKET) and a per-pool KQL query.
When a pool is "slow", CPU percentage tells you that something is busy, but not what the queries are waiting for. Wait statistics give that second half. On Azure SQL Database the picture differs from on-premises SQL Server in two ways: the views are scoped differently, and a whole family of waits comes from the platform's resource governance rather than from your queries. This article is how I read them, per database and per pool.
Two views, two scopes
| View | Scope | Use |
|---|---|---|
sys.dm_db_wait_stats |
the current database only | attribute waits to one database; requires VIEW DATABASE STATE |
sys.dm_os_wait_stats |
the engine's view, not limited to your database | see waits that do not belong to a user database, and platform waits |
Both are cumulative since the last reset (restart, failover or the database being moved), and neither shows waits in progress. So never read the absolute numbers: take two snapshots and subtract, or use a collector that stores deltas. The documentation for sys.dm_db_wait_stats states that counters reset whenever the database is moved or taken offline and that statistics are not persisted after failover.
-- snapshot, wait 5 minutes, snapshot again, subtract
SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
INTO #w1
FROM sys.dm_db_wait_stats;
WAITFOR DELAY '00:05:00';
SELECT TOP (10)
b.wait_type,
b.wait_time_ms - a.wait_time_ms AS delta_wait_ms,
b.signal_wait_time_ms - a.signal_wait_time_ms AS delta_signal_ms
FROM sys.dm_db_wait_stats AS b
JOIN #w1 AS a ON a.wait_type = b.wait_type
WHERE b.wait_type NOT LIKE 'SLEEP%' AND b.wait_type NOT LIKE '%QUEUE%'
ORDER BY delta_wait_ms DESC;
Always filter the idle and background waits (sleep, queue, wait-for) first, otherwise they top the list.
The waits that matter in the cloud
Log rate governance. Azure SQL Database limits how fast a workload can generate transaction log, to protect backups and replicas. It is surfaced by wait types documented on Microsoft Learn: LOG_RATE_GOVERNOR (database limit), POOL_LOG_RATE_GOVERNOR (pool limit), INSTANCE_LOG_RATE_GOVERNOR, and the HADR_THROTTLE_LOG_RATE_* family (feedback control: slow replica, log space, geo-replication). Bulk loads, SELECT INTO and index rebuilds hit it first. If you see POOL_LOG_RATE_GOVERNOR, one database's big write job is slowing the others: reschedule it, batch it, or scale up.
Resource governor and memory. RESOURCE_SEMAPHORE means queries wait for a memory grant; RESOURCE_SEMAPHORE_QUERY_COMPILE means compilation throttling (many ad hoc queries); IO_QUEUE_LIMIT is described as the asynchronous IO queue of the database being too full, with a threshold related to the service objective.
Storage reads. PAGEIOLATCH_* means a task waits for a page to be read from storage into memory. A little is normal. A lot means missing indexes, scans reading too much, or memory too small for the working set. Check sys.dm_io_virtual_file_stats for latency before concluding anything.
Parallelism. CXPACKET shows threads of a parallel query waiting for each other; CXCONSUMER is its more benign companion. They are symptoms, not causes: look at the plan with the highest cost, at the statistics, and at the degree of parallelism. Reducing MAXDOP at database level is an option, but fix the plan first.
Others to know. WRITELOG (commit flushes, many tiny transactions), LCK_M_* (blocking), SE_REPL_* (waiting for secondaries to acknowledge commits).
Reading them per pool
The per-database DMV does not give a pool view. Two approaches. First, export the platform's diagnostic category for wait statistics to Log Analytics, then aggregate by pool in KQL (the table and columns below are those I use; names are generic):
AzureDiagnostics
| where TimeGenerated > ago(24h)
| where ResourceProvider == "MICROSOFT.SQL" and Category == "DatabaseWaitStatistics"
| extend Pool = tostring(column_ifexists("ElasticPoolName_s", "")),
Db = tostring(column_ifexists("DatabaseName_s", "")),
Wait = tostring(column_ifexists("wait_type_s", "")),
DeltaMs = todouble(column_ifexists("delta_wait_time_ms_d", 0.0))
| summarize TotalMs = sum(DeltaMs) by Pool, Wait
| top 20 by TotalMs desc
Second, run the delta query above on each database and store the result centrally. Then I read in this order:
flowchart TD
A["Pool metric high : CPU, workers or log write"] --> B["Waits by pool (KQL)"]
B --> C{"Dominant wait ?"}
C -- "log rate governor" --> D["Find the write-heavy database, reschedule"]
C -- "PAGEIOLATCH" --> E["Indexes, scans, memory, file latency"]
C -- "CXPACKET" --> F["Plans, statistics, MAXDOP"]
C -- "lock" --> G["Blocking : transactions, indexes"]
D --> H["Query Store of that database"]
E --> H
F --> H
Lessons learned
- Total wait time is not response time: threads wait in parallel. Use waits to rank, not to add up.
- Compare with a baseline of the same hour on a normal day; a wait that is always there is not your problem today.
- The pool-level governor wait names the symptom but not the culprit: join with per-database log write or Query Store to find who writes.
- Check the diagnostic setting is on before an incident; you cannot export history you did not collect.