A crowded connection list becomes useful when its sessions are grouped. Counting connections by program name shows which application labels are occupying the instance right now.
Start With User Sessions, Not Connections
A session describes a SQL Server login context and its activity. A connection describes a communication channel to that session. They are related, but they are not interchangeable inventory units.
I begin with user sessions rather than every internal task on the instance. I also keep the login beside the program and host labels. The same application label can belong to several different access paths.
Filter is_user_process to remove internal sessions from this application inventory. This still includes sessions created by administrators and monitoring. The query window running the inventory also appears as a user session.
A busy connection list does not prove a busy workload. Connection pools retain idle sessions to avoid reconnecting for every operation. Investigate what those sessions are doing before changing the pool size.
Use the instance diagnostic permissions appropriate to your SQL Server version. SQL Server 2022 and later require VIEW SERVER PERFORMANCE STATE for the session view. Without adequate visibility, an apparently small inventory is incomplete.
Count Connections by Program Name and Host
The first query answers a narrow question: how many visible user sessions share each label combination? It uses the session view alone, so connection joins cannot multiply the count. Run it before adding more detail.
SELECT s.program_name, s.host_name, s.login_name,
COUNT_BIG(*) AS SessionCount
FROM sys.dm_exec_sessions AS s
WHERE s.is_user_process = 1
GROUP BY s.program_name, s.host_name, s.login_name
ORDER BY SessionCount DESC, s.program_name, s.host_name;Read the largest groups as investigation candidates rather than violations. An application serving many concurrent users has a different baseline from a scheduled utility. The count needs a workload and configuration context.
Blank program or host labels remain useful findings. Do not discard them because the report looks untidy. They identify connections whose client identification provides little help during an incident.
Grouping connections by program name also exposes inconsistent naming. Two deployments of one application can send different labels. Agree on a clear application name in the connection configuration when the application supports it.
Do not rewrite an unknown label into a confident application identity. Preserve the supplied value and annotate your interpretation separately. That keeps the evidence usable when the client configuration later changes.
Inspect the Connection Details
Join the session identifier to the connection view for address and connection time. This is a detail report, so repeated session identifiers remain visible. Multiple active result sets can introduce additional connection relationships.
SELECT s.session_id, s.program_name, s.host_name, s.login_name,
c.connection_id, c.parent_connection_id,
c.client_net_address, c.connect_time,
s.status, s.last_request_start_time, s.last_request_end_time
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.program_name, s.host_name, s.session_id, c.connect_time;A left join retains visible sessions without a matching connection row. A null address does not prove that the session came from an unknown remote computer. Local transports and connection types affect available address information.
Connect time describes channel establishment, not the most recent business request. A healthy pool can keep a connection alive for a long period. Compare activity timestamps when the question concerns inactivity.
Client addresses also reflect routing and network architecture. A gateway or shared application host can represent many users. Treat an address as the observed connection endpoint, not a complete user identity.
Count Idle Connections by Program Name
Define idle before calculating it. Here it means a sleeping session whose last completed request ended more than an hour ago. A session with no completed request uses its earliest visible connection time as the fallback.
The connection aggregation below produces one row per session. That keeps group counts accurate even when multiple connection records exist. It intentionally omits individual addresses from the grouped result.
DECLARE @CapturedAt datetime = GETDATE();
WITH ConnectionStart AS
(
SELECT session_id, MIN(connect_time) AS FirstConnectTime
FROM sys.dm_exec_connections
GROUP BY session_id
)
SELECT s.program_name, s.host_name, s.login_name,
COUNT_BIG(*) AS SessionCount,
SUM(CONVERT(bigint, CASE
WHEN s.status = N'sleeping'
AND COALESCE(s.last_request_end_time, c.FirstConnectTime)
< DATEADD(hour, -1, @CapturedAt)
THEN 1 ELSE 0 END)) AS IdleOverOneHour
FROM sys.dm_exec_sessions AS s
LEFT JOIN ConnectionStart AS c ON c.session_id = s.session_id
WHERE s.is_user_process = 1
GROUP BY s.program_name, s.host_name, s.login_name
ORDER BY IdleOverOneHour DESC, SessionCount DESC;The comparison uses the server's local clock because these activity columns use local datetime values. Capture time once so every group shares the same boundary. Do not compare them directly with an unrelated UTC monitoring timestamp.
A sleeping session can still hold an open transaction. Idle therefore does not mean harmless or safe to terminate. Inspect its transaction state and locks when the session also appears in a blocking investigation.
Sessions can begin or end while these queries run. Different result sets therefore represent nearby snapshots rather than one perfectly frozen inventory. Keep the capture times when comparing reports across a longer incident.
Verify the Labels Before Acting
Program name and host name are supplied by the client. They can be blank, stale, copied from another deployment, or deliberately misleading. They are operational hints rather than security evidence.
The host label is not a reliable machine ownership record. Confirm it with the application configuration and the observed network endpoint. A label that says production does not earn a production passport.
Which group changed compared with the application's normal connection pattern? That is more useful than deciding every idle session is wrong. A growing idle population and a stable pool require different responses.
Also distinguish concurrent requests from connected sessions. One application can retain many sessions while sending few requests. Use the active request view for current execution rather than inferring execution from connectivity.
Review the login against the expected deployment account as well. Shared credentials reduce the detail available from this report. Application labels improve investigation, while proper authentication and authorization remain separate responsibilities.
Turn the Snapshot Into a Useful Baseline
Save the grouped counts and capture time when the question recurs. Include program, host, login, total sessions, and your idle definition. Preserve the raw labels so later comparisons remain reproducible.
Review the collection's permissions and storage before scheduling it. Login names and client addresses reveal operational details. Retain only the fields needed to answer the connection question.
Connections by program name become useful when their trend is connected with application behavior. Check deployment changes, pool settings, and failed cleanup paths beside the database evidence. The session count alone does not select a fix.
Finish with the specific group that needs attention and the evidence supporting that decision. Keep healthy pooled sessions separate from sessions holding unwanted transactions. A clear inventory makes the next investigation smaller and more precise.
Related reading on this blog: Representing sp_who2 with DMVs and Capturing sp_WhoIsActive Snapshots to a Table Every Minute.
A connection label is not verified identity, it is a starting point for investigation.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
