When an application cannot connect, start by seeing who already holds sessions. Counting current connections by login, host, program, and database gives a useful snapshot. Compare it with a normal day before calling a high number a leak.
Distinguish Sessions From Connections
sys.dm_exec_sessions describes authenticated sessions. sys.dm_exec_connections describes physical connections and related connection details. Their relationship is not guaranteed to be one-to-one in every feature scenario, so decide which unit you mean before writing COUNT(*). For an application pool problem, both session count and physical connection count can be informative.
SELECT s.session_id,s.login_name,s.host_name,s.program_name,
DB_NAME(s.database_id) AS current_database,
s.status,s.open_transaction_count,
c.connection_id,c.client_net_address
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_connections AS c
ON c.session_id = s.session_id
WHERE s.is_user_process = 1
ORDER BY s.login_name,s.host_name,s.session_id;The current database is session state; it is not proof of every database the application queried. I first look at the raw rows to understand whether an application uses expected program names and hosts. What changed between a healthy day and the time of the alert? One count without that comparison can be misleading.
Start Counting Current Connections by Group
For a simple session count, group sessions without joining connections so a one-to-many relationship cannot inflate it. Use COUNT_BIG(*) for consistency. Keep blank or NULL host and program names visible rather than silently merging them into a reassuring label. Client-supplied program names are useful diagnostics, not a security identity.
SELECT s.login_name,s.host_name,s.program_name,
DB_NAME(s.database_id) AS current_database,
s.status,COUNT_BIG(*) AS sessions
FROM sys.dm_exec_sessions AS s
WHERE s.is_user_process = 1
GROUP BY s.login_name,s.host_name,s.program_name,
s.database_id,s.status
ORDER BY sessions DESC;A high sleeping count can be normal for connection pooling. Compare it with the application's configured pool size and a baseline at similar traffic. I do not terminate sleeping sessions just because they are numerous. I look for open transactions, resource pressure, and whether new logins are actually failing.
Counting Current Connections Apart From Sessions
Join connections to sessions to attribute physical connections to an application. Count connection IDs, and distinguish a missing connection row from a real zero. A dedicated admin connection or internal feature can appear differently from an ordinary application pool. Check permissions and server version if rows seem absent.
SELECT s.login_name,s.host_name,s.program_name,
COUNT_BIG(c.connection_id) AS connection_rows,
COUNT(DISTINCT s.session_id) AS sessions
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_connections AS c
ON c.session_id = s.session_id
WHERE s.is_user_process = 1
GROUP BY s.login_name,s.host_name,s.program_name
ORDER BY connection_rows DESC;COUNT(DISTINCT) here avoids multiplying session count when a session has more than one associated connection row. Keep the raw DMV output when the mapping is unexpected. I compare the top groups with application instance counts; a single host holding far more connections than its peers can point to a leak or uneven routing.
Find Sleeping Open Transactions
A session can be sleeping and still have an open transaction. It can hold locks and delay log reuse while doing no active request work. Filter by status = 'sleeping' and open_transaction_count > 0, then inspect transaction age and locks before taking action. The session DMV count can differ from transaction-specific DMVs in some cases, so use it as a lead.
SELECT s.session_id,s.login_name,s.host_name,s.program_name,
s.open_transaction_count,s.last_request_end_time,
DB_NAME(s.database_id) AS current_database
FROM sys.dm_exec_sessions AS s
WHERE s.is_user_process = 1
AND s.status = 'sleeping'
AND s.open_transaction_count > 0
ORDER BY s.last_request_end_time;A KILL command can roll back a large transaction and extend the incident. Check blocking chains, transaction state, application owner, and rollback cost first. I contact the owner of the specific session with its login, host, and last request time rather than issuing a broad cleanup against every sleeper.
Save a Reusable Snapshot
Create a small monitoring table and insert grouped results at fixed intervals. Include capture time and a source instance identifier if multiple servers send to one store. Compare the same hour and day type; a payroll batch on Monday is not a useful baseline for a quiet Sunday. Set retention so the snapshot table does not become its own growth issue.
CREATE TABLE dbo.ConnectionSnapshot
(
CapturedAt datetime2(0) NOT NULL,
LoginName sysname NULL,
HostName nvarchar(128) NULL,
ProgramName nvarchar(128) NULL,
DatabaseID smallint NULL,
SessionCount bigint NOT NULL
);
INSERT dbo.ConnectionSnapshot
(CapturedAt,LoginName,HostName,ProgramName,DatabaseID,SessionCount)
SELECT SYSDATETIME(),login_name,host_name,program_name,
database_id,COUNT_BIG(*)
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
GROUP BY login_name,host_name,program_name,database_id;Schedule the INSERT only after verifying ownership, permissions, and retention. I keep the raw current query available because a grouped history cannot reconstruct individual session IDs. Snapshot data tells me where to investigate, while a live capture tells me which sessions still exist.
Interpret a Spike Carefully
If connections rise, check failed login counts, application error rate, connection pool configuration, and server resources. A batch deployment can briefly create more app instances without a leak. A low connection count during an outage can indicate that clients never reached the server. Counts alone do not prove CPU, memory, or thread exhaustion.
Compare login, host, program, and database together. One login shared across many applications hides ownership; an application name helps, though it is client controlled. I correlate the spike with deployment time, Agent jobs, and network changes. The question is not merely how many connections exist, but whether the change explains the reported symptom.
Make Counting Current Connections Actionable
Account for permission scope. Depending on SQL Server version, viewing every session and connection requires a server-level performance or state permission; without it, a login can see only its own activity. A small count from a restricted account is not evidence that the server is quiet. Record which principal ran the query and confirm visibility before using the snapshot for incident decisions.
If an application uses a shared login, group by host and program to narrow the owner, but verify those fields with deployment records. They are client-supplied and can be blank or misleading. Save client_net_address from the raw connection rows during an incident when network routing matters. The grouped snapshot is for trend comparison, not a complete forensic trail.
Keep a healthy baseline, an incident snapshot, and a short owner map for top applications. When a group is abnormal, collect a few representative session IDs and inspect active requests or open transactions. Protect the snapshot table because login and host names can reveal operational details.
I use this query as an opening move, not a final answer. It quickly separates one noisy client from a broad rise, and it points to sleeping transactions that deserve attention. The saved baseline is what gives the number meaning when the next alert arrives under normal production load. Keep the capture time and server identity beside every comparison.
Related reading on this blog: Find Total Sessions by Database and How to Find IP Address of All SQL Server Connection? Interview Question of the Week #280.
A connection count is not a diagnosis, it is a baseline comparison that points to sessions.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
