Azure SQL: diagnose connection pool exhaustion before scaling

An API first slows down, then starts returning errors such as Timeout expired prior to obtaining a connection from the pool. Azure SQL CPU still looks acceptable and a few manual queries succeed. Under pressure, the immediate answers appear to be increasing the database tier or raising the pool limit.

Both actions can hide a connection leak, multiply idle sessions and move the incident to the next limit. This runbook covers a distributed application using Azure SQL with one pool per process. Its goal is to locate the constraint in the application process, SQL engine, network or identity, then make an explicit decision: correct the defect, scale temporarily or roll back.

Freeze a representative failure

Tie one error to a specific revision, instance and database before changing capacity. A pool is normally local to a process. Ten replicas configured with a maximum of 100 connections do not share a pool of 100; they can theoretically open 1,000 connections.

incident: inc-20260817-014
window_utc:
start: 2026-08-17T08:10:00Z
end: 2026-08-17T08:40:00Z

application:
service: orders-api
revision: release-2026.08.17.2
instances: 12
runtime: dotnet
pool_max_per_process: 100

database:
logical_server: sql-prod-weu
database: orders
service_tier: <current-tier>

evidence:
- exact exception and stack
- role instance and operation ID
- SQL session and request snapshot
- connection success, failure and latency
- recent deployment, scale or identity change

temporary_constraints:
- do not raise pool size before counting all processes
- do not restart every replica at once
- do not increase database tier without a server-side limit
- preserve one failing instance for evidence

Check whether every instance fails or only the new revision. One drifting instance points to local retention. Every instance showing the same rise in SQL duration points more strongly to the database or a shared dependency. A failure wave after scale-out may simply expose the multiplication of pools.

Define the connection contract

Inventory what the application is expected to do before reading metrics. Record the maximum pool size per process, maximum process count, acquisition timeout, command timeout, retry policy and authentication mode.

A connection must return to the pool after each unit of work. A long transaction, an unclosed reader, an exception path that misses Dispose, or a slow downstream operation can retain the connection while SQL shows little activity. Retries then amplify demand: each blocked request creates another attempt before the first one releases its resource.

Build a simple capacity envelope:

Theoretical connection ceiling
application replicas x worker processes x pool max

Compare with
observed concurrent requests
observed SQL sessions
active SQL requests
database session and worker limits

Example interpretation
12 replicas x 1 process x pool max 100 = 1,200 possible sessions
70 observed SQL sessions + local pool timeout on one replica
=> investigate local retention before scaling Azure SQL

This envelope is not a utilization target. It makes the risk of an oversized pool and unbounded application autoscaling visible.

Prove the Azure SQL state

Take several snapshots a few seconds apart because one instant can miss a burst. Dynamic management views require suitable read permissions. Use a dedicated observation identity instead of distributing an administrator role for incident diagnosis.

SELECT
  s.status,
  s.login_name,
  s.program_name,
  s.host_name,
  s.client_interface_name,
  COUNT(*) AS session_count,
  SUM(CASE WHEN r.session_id IS NOT NULL THEN 1 ELSE 0 END) AS active_requests,
  MIN(s.login_time) AS oldest_login_time,
  MAX(s.last_request_end_time) AS latest_request_end_time
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r
  ON r.session_id = s.session_id
WHERE s.is_user_process = 1
GROUP BY
  s.status,
  s.login_name,
  s.program_name,
  s.host_name,
  s.client_interface_name
ORDER BY session_count DESC;

SELECT
  r.session_id,
  r.status,
  r.command,
  r.wait_type,
  r.wait_time,
  r.blocking_session_id,
  r.cpu_time,
  r.total_elapsed_time
FROM sys.dm_exec_requests AS r
WHERE r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;

Read the two planes together:

• many sleeping sessions from one revision with few active requests: retention or an oversized application pool;
• many active requests with long waits or blocking: diagnose the SQL workload before changing the pool;
• few visible sessions while one process times out during acquisition: local process issue, fragmented connection strings or failure before opening;
• connection failures from every instance: investigate network, firewall, DNS, a service limit or authentication.

Do not kill sleeping sessions in bulk. They may be healthy and reusable. Identify their origin first and prove they exceed the pool’s expected behavior.

Correlate dependencies and instances with KQL

Application Insights traces can compare revisions and instances. Adapt dimension names to your instrumentation. The important part is keeping the same UTC window and distinguishing local pool acquisition, SQL connection and command duration.

let Window = 2h;
dependencies
| where timestamp > ago(Window)
| where type =~ "SQL"
| summarize
  Calls=count(),
  Failures=countif(success == false),
  P95Duration=percentile(duration, 95),
  Results=make_set(resultCode, 8)
by cloud_RoleName, cloud_RoleInstance, target, bin(timestamp, 5m)
| order by timestamp desc, Failures desc

Add a structured event around acquisition with wait duration, outcome, instance, revision and operation ID. Never log the connection string or token. If logs cannot distinguish pool wait from SQL execution, the incident first exposes an observability gap.

Then compare the Azure SQL metrics exported to your workspace: session percentage, workers, CPU, successful or failed connections and deadlocks. Exact metric names depend on the export configuration, so list available metrics before turning the query into a permanent alert.

Rule out network and identity

An empty pool caused by failed new connections can look like a full pool. Test DNS resolution and TCP establishment from the same subnet and identity as the application. A Private Endpoint may exist in the architecture, but its role here is limited to the access path: it proves neither engine availability nor correct pool management.

With managed identity, inspect token acquisition separately from the SQL connection. An implementation that requests a token for every operation or fragments pools by varying connection strings can create latency and too many distinct pools. Keep SDK-compliant token caching, a stable connection string and a connection lifetime compatible with token lifetime.

Do not immediately replace managed identity with a client secret. If token acquisition fails, preserve the error code, UTC time, actual identity and requested scope, then correct that plane without changing SQL capacity.

Correct with a bounded canary

Match the action to the evidence:

• leak or missing disposal: fix the faulty path and deploy one canary instance;
• long transaction or command: reduce its scope, correct the query or remove the blocker;
• retry amplification: bound attempts, total duration and jitter;
• fragmented pools: stabilize the connection string and options that define pool identity;
• too many pools after scale-out: bound application scaling and recalculate the global envelope;
• proven SQL limit with useful connections: add temporary capacity with a documented return threshold.

The canary must receive a measurable share of traffic. Watch acquisition P95, errors, SQL sessions, active requests and business latency. Stop if timeouts rise, sessions keep growing after traffic falls, or the database approaches a limit faster than useful throughput grows.

Validate, decide or roll back

Validation is more than five quiet minutes. It must cover a representative peak and one application scale cycle.

Validate
Pool acquisition P95 returns to the target range
Error rate stays stable through a representative peak
SQL sessions follow traffic and return after the peak
Active requests and waits remain explainable
Managed identity token acquisition remains healthy
Scale-out does not multiply sessions beyond the envelope

Rollback application change
Route traffic back to the previous revision
Drain the canary without killing healthy SQL sessions
Confirm errors, sessions and business latency recover

Rollback temporary scaling
Keep the larger tier until the application fix is proven
Return to the previous tier during a controlled window
Recheck session, worker, CPU and latency headroom

Do not close the incident when
Restarting replicas is the only action that works
Pool wait and SQL execution are still indistinguishable
Session growth has no owner or stop condition

Conclusion

An Azure SQL pool timeout is not evidence that the database is undersized. The pool belongs to the application process, while session, worker and resource limits belong to the SQL service. Diagnosis must connect those two planes with network and identity evidence.

The useful outcome is a verifiable decision: correct local retention, remove SQL contention, repair the connection path, or scale temporarily because a real limit is proven. In every case, a canary, stop conditions and rollback prevent a capacity increase from turning a quiet leak into a larger incident.