Summary: Quickly audit your SQL Server environment with this T-SQL script that groups active connections by IP address, application name, and login to identify resource usage and connection leaks.
Monitoring Active SQL Server Connections and Sessions
Managing server load requires a clear understanding of who is connected to your database and what applications they are using. Here's a handy SQL query to get an overview of your active SQL Server connections and sessions. It helps you quickly see the source IP, the client hostname, and the total connection count per user. This is an essential tool for monitoring server activity or troubleshooting connection leaks.
SELECT
ec.client_net_address AS [IP Address],
es.[program_name] AS [Application],
es.[host_name] AS [Client Host],
es.login_name AS [Login],
COUNT(ec.session_id) AS [Connection Count]
FROM sys.dm_exec_sessions AS es WITH (NOLOCK)
INNER JOIN sys.dm_exec_connections AS ec WITH (NOLOCK)
ON es.session_id = ec.session_id
GROUP BY
ec.client_net_address,
es.[program_name],
es.[host_name],
es.login_name
ORDER BY
ec.client_net_address,
es.[program_name]
OPTION (RECOMPILE);
How the Query Works
This script performs an inner join between two critical Dynamic Management Views (DMVs):
- sys.dm_exec_connections: Provides information about the physical transport layer and the client IP addresses.
- sys.dm_exec_sessions: Contains high-level metadata about each session, such as the authenticated user and the application name.
By grouping the data, you can pinpoint exactly which application or user is consuming the most connection slots. The OPTION (RECOMPILE) hint ensures that SQL Server generates a fresh execution plan every time you run the script, which is ideal for querying volatile system views that change every second.
Conclusion
Regularly auditing your connections helps identify unauthorized access and application-level bugs where connections aren't being closed properly. For a deeper dive into SQL Server management, ensure your applications are utilizing connection pooling correctly to avoid hitting session limits.
Found this SQL monitoring script useful? Share it with your DBA team or subscribe for more T-SQL performance tuning guides!
