Azure SQL Database is slow: high CPU, DTU limits and throttling

#1. Is the database at its limit?

SELECT TOP 30 end_time,
       avg_cpu_percent, avg_data_io_percent, avg_log_write_percent,
       avg_memory_usage_percent, max_worker_percent, max_session_percent
FROM sys.dm_db_resource_stats            -- 15-second samples, last ~1 hour
ORDER BY end_time DESC;

For longer history (14 days, hourly), use sys.resource_stats in the master database.

#2. Which queries use the CPU?

SELECT TOP 10
  q.query_id,
  SUM(rs.count_executions) AS executions,
  SUM(rs.avg_cpu_time * rs.count_executions) / 1000 AS total_cpu_ms,
  MAX(qt.query_sql_text) AS query_text
FROM sys.query_store_runtime_stats rs
JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
JOIN sys.query_store_query q ON q.query_id = p.query_id
JOIN sys.query_store_query_text qt ON qt.query_text_id = q.query_text_id
WHERE rs.last_execution_time > DATEADD(hour, -2, SYSUTCDATETIME())
GROUP BY q.query_id
ORDER BY total_cpu_ms DESC;

Often one query, frequently one that changed plan, accounts for most of the load. Check whether its plan has changed (Query Store → Regressed queries in SSMS). If so, force the last good plan:

EXEC sp_query_store_force_plan @query_id = 1234, @plan_id = 56;

#3. What are queries waiting on?

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

#4. Quick fixes

• Missing indexes: check sys.dm_db_missing_index_details, and turn on automatic tuning (CREATE INDEX / FORCE LAST GOOD PLAN).
• Scale up for relief while you fix the queries. Scaling is online, with a short reconnect at the end:

  az sql db update -g rg-data -s sqlsrv-prod -n appdb --service-objective GP_Gen5_8
  

• Stale statistics after big loads: UPDATE STATISTICS dbo.Orders WITH FULLSCAN;

#Errors customers report

Apps must use retry logic for transient errors. All current SQL client libraries support it.

#Prevent it next time

Alert on CPU and log-write percentage above 80% for 15 minutes, and review Query Store's top consumers each week.