SQL Server user connections climbing: connection pool leaks and retry storms

A rising User Connections counter is easy to misread. It does not mean that many queries are running. Most application connections are pooled and idle, so the count can climb for hours while CPU, I/O, and batch rate look almost normal.

The useful split is shape and correlation. A slow upward trend without a matching rise in Batch Requests/sec usually points to an application-tier connection pool leak or pool fragmentation. A sudden spike, especially after transient errors, points to a retry storm or a burst of new application instances. The dangerous endpoint is the same: enough concurrent active work consumes worker threads until new requests queue on THREADPOOL, which is effectively connection refusal.

Treat the counter as a correlate, not a page trigger by itself. Page when the climb converges with worker exhaustion signals: active requests near max_workers_count, work_queue_count above zero, sustained THREADPOOL waits, and failed connect or query probes.

What this means

SQL Server sees application pool size multiplied by application instance count, not end users. If each app instance keeps 50 pooled connections and you scale from 4 instances to 40, the instance can gain 1,800 mostly idle sessions without any single user doing more work.

Idle pooled sessions are not free, but they are not the immediate killer either. The immediate risk is when those sessions become active together, hold locks, wait on external calls, or retry in a tight loop. Then each request needs a worker thread. When workers run out, SQL Server can still be alive on TCP 1433 while refusing useful work.

flowchart TD
  A[User Connections rising] --> B{Batch Requests/sec rising too?}
  B -- Yes --> C[Real load or retry storm]
  B -- No --> D[Idle pooled sessions accumulating]
  C --> E[Check active requests and THREADPOOL]
  D --> F[Group sessions by host program login]
  E --> G[Worker exhaustion risk]
  F --> H[Find app tier leak]

The first job is to separate three states: idle pooled connections, active requests, and blocked requests that are pinning workers. The second is to find whether the growth is gradual leak behavior or event-driven storm behavior.

Common causes

Quick checks

Run these read-only checks from sqlcmd or SSMS. On named instances, counter object names use the MSSQL$INSTANCENAME: prefix.

# Current User Connections counter
sqlcmd -S localhost -E -Q "SELECT cntr_value AS user_connections FROM sys.dm_os_performance_counters WHERE counter_name = 'User Connections' AND object_name LIKE '%General Statistics%';"

-- Who owns the sessions, and how many are actually active
SELECT
    s.login_name,
    s.host_name,
    s.program_name,
    COUNT(*) AS connection_count,
    SUM(CASE WHEN r.session_id IS NOT NULL THEN 1 ELSE 0 END) AS sessions_with_requests
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE s.is_user_process = 1
GROUP BY s.login_name, s.host_name, s.program_name
ORDER BY connection_count DESC;

-- Active request pressure
SELECT COUNT(*) AS active_requests
FROM sys.dm_exec_requests
WHERE status IN ('running', 'runnable', 'suspended');

SELECT max_workers_count FROM sys.dm_os_sys_info;

SELECT scheduler_id, runnable_tasks_count, active_workers_count, work_queue_count
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE'
ORDER BY work_queue_count DESC, runnable_tasks_count DESC;

-- THREADPOOL and lock waits
SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('THREADPOOL', 'LCK_M_S', 'LCK_M_X', 'LCK_M_U', 'SOS_SCHEDULER_YIELD')
ORDER BY wait_time_ms DESC;

-- Batch request rate over 10 seconds
DECLARE @t1 BIGINT, @t2 BIGINT;
SELECT @t1 = cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Batch Requests/sec'
  AND object_name LIKE '%SQL Statistics%';
WAITFOR DELAY '00:00:10';
SELECT @t2 = cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Batch Requests/sec'
  AND object_name LIKE '%SQL Statistics%';
SELECT (@t2 - @t1) / 10.0 AS batch_requests_per_sec;

-- Blocking chains turning sessions into pinned workers
SELECT
    r.session_id AS blocked_session,
    r.blocking_session_id AS blocker,
    r.wait_type,
    r.wait_time / 1000 AS wait_seconds,
    DB_NAME(r.database_id) AS database_name,
    t.text AS blocked_query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

If normal connections stop responding during suspected worker exhaustion, use the Dedicated Admin Connection for diagnosis (sqlcmd -A, or ADMIN:servername in SSMS). Do not use DAC as the fix; use it to see what normal sessions cannot.

How to diagnose it

1. Confirm the shape. Sample User Connections every 30 to 60 seconds and compare it with Batch Requests/sec over the same window. A gradual climb with flat batch rate suggests leak or pooling change. A vertical jump after errors suggests storm behavior.

2. Break down ownership. Group sessions by login_name, host_name, and program_name. One app identity across many hosts usually means scale-out multiplied by pool size. One host with an abnormal count usually means a leak, a stuck process, or a bad deploy.

3. Separate idle from active. Join sessions to requests. If thousands of sessions have no row in sys.dm_exec_requests, they are pooled or orphaned. If many have requests in suspended status, they are consuming workers while waiting.

4. Check worker headroom. Compare active request pressure with max_workers_count and look for work_queue_count > 0 or sustained THREADPOOL. Do not raise max worker threads as the first response; find what is pinning workers.

5. Look for a head blocker. If LCK_M_* waits rise while CPU falls, find the head blocker. A sleeping head blocker with an open transaction is the classic application bug that turns a normal connection pool into a worker thread fire.

6. Correlate with application events. Line up the SQL timeline with deployments, autoscaling events, app restarts, timeout bursts, deadlock retries, and transient network errors. SQL Server can show the blast radius; the app tier usually owns the trigger.

7. Decide the incident class. If connections are idle and workers are healthy, you have time to fix pooling. If active requests, blocked chains, and THREADPOOL are rising, treat it as an availability incident and reduce incoming work before the instance becomes unreachable.

Metrics and signals to monitor

Fixes

Fix a pool leak

Stop the growth at the app tier. Identify the leaking host or process from session ownership, then recycle the smallest blast radius that clears the leak: one pool, one worker process, or one app instance. A full SQL restart clears sessions but hides evidence and usually does not fix the cause.

On the application side, look for code paths that open connections without disposing them, asynchronous paths that abandon operations before completion, and retry logic that creates a new connection instead of reusing the pool. Validate that every execute path has a deterministic close or dispose.

Tradeoff: restarting app instances is fast but can drop in-flight work. Draining instances before restart is safer but slower.

Fix pool fragmentation or misconfiguration

Reduce unnecessary pool multiplication. Common drivers are too many app identities, per-user integrated security in a web tier, inconsistent connection strings, and uncontrolled instance scale-out. The fix is to standardize connection strings, consolidate app identities where appropriate, and set pool limits as part of capacity math.

The capacity equation is simple: expected app instances times maximum pool size must stay far below the worker thread danger zone, with headroom for blocking and parallel queries. Pool limits that look reasonable on one instance become dangerous when autoscaled.

Tradeoff: smaller pools reduce SQL-side connection pressure but can queue work in the app. Larger pools absorb app bursts but move the queue into SQL Server workers.

Stop a retry storm

First reduce amplification. If the application retries aggressively after transient errors, apply backoff and jitter, cap concurrency, and shed nonessential load. Circuit breakers are more useful than hero queries during the storm.

On SQL Server, verify whether the trigger was a real engine event: brief unresponsiveness, a blocking chain, log or I/O stall, failover, or resource saturation. If the engine had a short brownout, fix that cause or the retry storm will repeat.

Tradeoff: disabling retries entirely can turn transient faults into user errors. Unbounded retries turn one transient fault into a self-inflicted denial of service.

Relieve worker exhaustion safely

If THREADPOOL is active, reduce incoming work and unblock workers. Kill only after assessment. A sleeping head blocker with an open transaction is a candidate, but rollback can take as long as the original work. Killing active large queries can also trigger long rollback and more I/O. Warning: KILL is disruptive and its rollback is single-threaded; estimate rollback cost before issuing it against a large transaction.

Do not treat raising max worker threads as the fix. Find what is consuming workers first. Extra workers can buy minutes in a true emergency, but they also let a blocking cascade grow wider before it falls over.

Prevention

• Baseline connection shape by time and deploy. Track User Connections, batch rate, active requests, and session ownership together. A count without a baseline is noise.
• Alert on divergence, not count alone. Ticket when connections rise beyond baseline without batch growth. Page only when connection growth converges with worker exhaustion or failed probes.
• Make pool arithmetic explicit. Record pool size, app instance count, identity model, and connection string variants as capacity inputs.
• Watch worker headroom continuously. Trend active workers against max_workers_count, plus work_queue_count and THREADPOOL. The cliff is binary.
• Detect sleeping head blockers early. A 30 second check for blocking chains and idle blockers with open transactions catches the most dangerous leak-adjacent failure before users report it.
• Test retry behavior under brownouts. Inject short transient faults in staging and observe whether retries create a second outage.
• Keep DMV history outside SQL Server. DMVs reset on restart and ring buffers roll off. Persist samples so post-incident review can distinguish leak from storm.

How Netdata helps

• Correlation in one timeline: Netdata helps line up User Connections with Batch Requests/sec, active requests, waits, CPU, and I/O so you can tell idle accumulation from real work arrival.
• Worker exhaustion context: tracking scheduler backlog, worker pressure, and THREADPOOL alongside connection count shows when a medium signal is becoming an availability event.
• Ownership drilling: session breakdowns by login, host, and program make it faster to name the app tier, deployment, or instance group driving growth.
• Blocking cascade detection: combining lock waits, blocked session counts, and head blocker state explains the low CPU plus unresponsive pattern that often follows connection growth.
• Storm reconstruction: per-second collection preserves the spike shape and the preceding error burst that longer scrape intervals smooth away.

Netdata’s Microsoft SQL Server monitoring with Netdata brings these signals together with per-second metrics and ML anomaly detection.

Related guides

• SQL Server CPU utilization high: telling query load apart from a bad plan
• SQL Server CXPACKET and CXCONSUMER waits: parallelism, MAXDOP, and what is actually wrong
• SQL Server high compilations per second: plan cache pollution and CPU burn
• SQL Server runnable tasks backlog: the in-engine CPU queue OS metrics miss