Diagnosing Azure SQL Database Resource‑Governance Throttling (40501, 10928, 10929)

Recognizable Condition

Applications intermittently receive errors such as 40501 (service busy), 10928 (resource ID 1 – worker/session limit), or 10929 (resource ID 2 – memory limit). These errors appear during load spikes rather than steady traffic and often disappear after a short pause.

Cause/Diagnostic Table

Error	Typical Resource Exhausted	Common Cause
10928	Worker threads or session slots	Too many concurrent connections or long‑running queries holding workers
10929	Memory grant pool	High memory‑grant demand from large sorts, hashes, or concurrent queries
40501	Generic engine throttling wrapper	Indicates any of CPU, Data IO, Workers, or Memory approaching the limit; requires deeper inspection

Ordered Checks

1. Review historic resource usage
   Connect to the master database (requires VIEW SERVER STATE) and run:

   SELECT end_time,
          avg_cpu_percent,
          avg_data_io_percent,
          avg_log_write_percent,
          max_worker_percent,
          max_session_percent
       FROM sys.resource_stats
       WHERE database_name = N'YourDatabaseName'
         AND end_time BETWEEN DATEADD(minute,-30, SYSUTCDATETIME()) AND SYSUTCDATETIME()
       ORDER BY end_time DESC;

   Look for any avg_*_percent or max_*_percent values nearing 100% during the incident window.
2. Inspect current database‑level stats
   In the user database (requires VIEW DATABASE STATE):

   SELECT 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;

   This view retains roughly the last hour; use it to see short‑term spikes.
3. Identify blocking or worker‑consuming queries
   Still in the user database:

   SELECT r.session_id,
          r.status,
          r.command,
          r.wait_type,
          r.wait_time,
          r.total_elapsed_time,
          t.text AS sql_text
       FROM sys.dm_exec_requests r
       CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
       WHERE r.session_id > 50   -- filter out system sessions
       ORDER BY r.wait_time DESC;

   High wait times on RESOURCE_SEMAPHORE (memory grant) or many sessions with SOS_SCHEDULER_YIELD point to worker or memory pressure.
4. Correlate with application connection‑pool settings
   Review the pool size, idle timeout, and retry logic in the application configuration (e.g., MaxPoolSize in ADO.NET). Compare the observed max_session_percent from step 1 against the documented session limit for your tier (see Azure portal → Metrics → “sessions” limit).

Fixes Tied to Findings

• Worker/session exhaustion (10928)
  • Reduce the application connection‑pool MaxPoolSize to stay safely below the tier’s session limit.
  • Enable or tighten idle‑connection cleanup (e.g., Connection Lifetime or Idle Timeout).
  • Add exponential back‑off retry logic (e.g., wait 200 ms, then 400 ms, then 800 ms) to avoid thundering‑herd effects.
  • Identify and kill long‑running blocking queries using KILL after confirming they are safe to terminate.
• Memory‑grant pressure (10929)
  • Use Query Store (ALTER DATABASE … SET QUERY_STORE = ON) to find queries with high avg_memory_grant_percent.
  • Add missing indexes or rewrite queries to reduce sort/hash memory needs.
  • If memory pressure persists, consider moving to a higher vCore or DTU tier that provides more memory grant pool.
• Generic throttling (40501) with CPU/IO saturation
  • From step 1, note which avg_cpu_percent or avg_data_io_percent is near 100%.
  • Tune top‑consuming queries: add indexes, update statistics, check for parameter sniffing.
  • Enable Automatic Tuning (ALTER DATABASE … SET AUTOMATIC_TUNING = ON) to let the service create/revert indexes.
  • If tuning does not relieve pressure, scale up the service tier (increase DTUs or vCores).

Escalation Criteria

• Throttling errors continue after query tuning and a tier increase.
• sys.resource_stats shows no resource near 100% yet errors persist (possible platform‑side issue).
• Availability SLA breaches are observed in Azure Monitor alerts.
• You suspect a bug or misconfiguration in the hyperscale or serverless tier (e.g., auto‑pause causing cold‑start failures that look like throttling).

When escalating, collect:

• Time range of incidents (UTC).
• Output of the queries from steps 1‑3.
• Application connection‑pool configuration.
• Any recent schema or workload changes.

Verification

1. After applying a fix, rerun the same load test or wait for the next natural traffic spike.
2. Confirm that sys.resource_stats and sys.dm_db_resource_stats show the previously saturated metric now comfortably below 80%.
3. Check application logs for the absence of 40501/10928/10929 errors during the verification window.

Note: Limits for sessions, workers, and memory vary by tier, compute size, and purchasing model. Always consult the latest Azure SQL Database documentation for the exact numbers before adjusting pools or scaling.