SQL Server Connection Storms

Why this problem matters

Faulty retries, pooling problems, or mass restarts can create many connections within a short period. The surge can become login overhead, worker pressure, and application timeouts.

How to diagnose it

Group user sessions by program_name, host_name, login_name, and status. Examine connection creation rate, reuse behavior, and application error logs for the same period instead of relying on one total.

The query groups user sessions only at the moment it executes. Regular sampling is required to observe connection rate or short-lived sessions that open and close quickly.

SELECT s.login_name,
       s.host_name,
       s.program_name,
       s.status,
       COUNT(*) AS session_count,
       SUM(CASE WHEN r.session_id IS NOT NULL THEN 1 ELSE 0 END) AS active_request_count
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.login_name, s.host_name, s.program_name, s.status
ORDER BY session_count DESC;

A safe solution approach

Correcting application connection pooling, limits, and exponential-backoff retry policy is the primary approach. Changing a SQL Server connection limit is not a root fix before client behavior is understood.

Why continuous monitoring matters

Connection storms may end before manual investigation begins because they are often brief. moon can continuously track session and resource trends and route alerts through Slack, PagerDuty, or webhooks.