LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon