Azure SQL Database Wait Stats: Reading sys.dm_db_wait_stats

A familiar instance wait script can tell the wrong story after a database moves to Azure. Azure SQL Database wait stats are scoped to the database and include governance waits that need service-tier context.

Read Azure SQL Database Wait Stats Per Database

sys.dm_db_wait_stats reports waits for the current Azure SQL Database. Do not read it as an on-premises instance-wide history or compare raw totals from different database lifetimes. Counters can reset after a move or offline transition. I capture two snapshots around the problem and subtract them, just as I would with instance waits. Filter background waits deliberately, but keep raw results. A high wait total accumulated over weeks does not explain a five-minute complaint. Both queries in this post run only in Azure SQL Database. On my SQL Server 2025 test instance, each one fails with Msg 208, invalid object name.

SELECT wait_type, waiting_tasks_count,
       wait_time_ms, signal_wait_time_ms
FROM sys.dm_db_wait_stats
ORDER BY wait_time_ms DESC;

Recognize Governance Waits

LOG_RATE_GOVERNOR indicates the database is being limited by its log generation quota. Related waits include POOL_LOG_RATE_GOVERNOR and INSTANCE_LOG_RATE_GOVERNOR, depending on the service configuration. Other HADR throttle waits can reflect replication or log-space feedback. I do not lump all of them into "slow disk." A large load, index build, or burst of tiny transactions can hit the log path for different reasons. Review the active workload and service tier before deciding whether to tune or scale.

The wait name tells you where pressure was applied, not which statement created it. Query Store and the current requests provide the next layer.

Pair Waits With Resource Percentages

sys.dm_db_resource_stats reports recent CPU, data IO, and log-write usage percentages for the database. Align its end_time with the wait snapshot window. A high log-write percentage alongside log-rate waits supports a service-limit explanation. A low resource percentage with a large old wait total suggests the current complaint needs another cause. I also check worker and session usage when connections are failing. Resource data has retention limits, so capture it while the incident is fresh.

SELECT TOP (100) end_time, avg_cpu_percent,
       avg_data_io_percent, avg_log_write_percent,
       max_worker_percent, max_session_percent
FROM sys.dm_db_resource_stats
ORDER BY end_time DESC;

Find the Workload Behind the Wait

Look at Query Store runtime and wait categories for expensive statements during the same interval. For log-rate pressure, inspect bulk loads, index maintenance, large updates, and frequent commits. For CPU or data IO pressure, look at plans and logical reads. I separate a temporary batch from a steady user workload. A service-tier change can help when the workload genuinely needs more sustained capacity; it is wasteful when one avoidable scan or poorly scheduled rebuild causes the spike.

Ask whether moving maintenance to a quiet period would solve the user impact. It is usually a cheaper first experiment than increasing capacity permanently.

Compare the Two Choices

Tune the workload when a query reads excessive pages, writes more rows than necessary, or runs too frequently. Consider a tier change when efficient work consistently meets a documented service limit and the business needs that throughput. Test the tier change and record actual results; do not claim a theoretical percentage improvement. I also include cost and scaling time in the decision. The right answer can be a smaller query and a larger tier for different parts of the same workload.

Use the service objective and resource governance views to understand hard ceilings. A wait without a limit comparison leaves the diagnosis unfinished.

Calculate Azure SQL Database Wait Stats Deltas After a Move

sys.dm_db_wait_stats is database-scoped, and its counters can reset when the database moves or goes offline. Capture two samples with timestamps and verify counters increased normally before calculating a delta. If a wait count falls, discard that pair and start a new interval. I use the same window for sys.dm_db_resource_stats and Query Store. A weekly total divided by a five-minute resource chart is a tempting but invalid comparison.

Keep the raw and filtered wait lists. A background wait can be filtered from the ranking, but a governance wait should be read in context. LOG_RATE_GOVERNOR is more meaningful during a bulk load with high avg_log_write_percent than during a quiet period after a move. The service tier and elastic pool placement also matter. I note them with the sample so a later comparison does not treat two different limits as the same environment.

Separate Tier Pressure From Query Waste

A sustained high resource percentage can justify more capacity when the workload is already efficient and the business needs its throughput. Before changing the tier, inspect top queries, index maintenance, batch sizes, and transaction commit pattern. A large update that touches far more rows than needed can hit log governance at any tier. A scheduled index rebuild can consume the same log budget that user writes need. I test a narrower batch or different schedule in a safe copy and compare actual log rate and user latency.

What if capacity was changed but the wait remains? Check whether the workload expanded into the new headroom, whether an elastic pool limit still applies, and whether the bottleneck moved to data IO or CPU. A tier change is an experiment with a cost, not proof that the original query was healthy. I record both workload-tuning and tier-change results in the same units and interval.

Use the same service objective when comparing periods. A move between tiers changes the resource percentages' denominator, so a lower percent after scaling does not by itself mean the workload became more efficient. I compare completed work, user latency, and query reads along with the percent. That keeps the cost decision tied to actual benefit. The cloud still sends a bill for a guess.

Report Azure SQL Database Wait Stats for a Fixed Window

Record database name, service tier, wait deltas, resource percentages, top statements, and the exact observation window. Keep current and historical evidence separate. I rerun the same captures after tuning or scaling. If user latency improves but the wait category changes, investigate the new bottleneck rather than treating every changed wait as a regression.

The cloud adds limits you did not manage directly on a local server. The diagnostic habit stays familiar: measure a window, find the workload, and choose the change that matches the evidence.

Related reading on this blog: Wait Stats Collection Scripts : Updated March 2021 and Azure SQL Database or Managed Instance.

A cloud wait is not an automatic upgrade request, it is a workload signal beside a limit.

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