Here's a handy SQL query to get an overview of your active SQL Server connections and sessions. It helps you quickly see who is connected, from where, and how many connections they have open. This can be super useful for monitoring activity or troubleshooting connection issues.
SELECT ec.client_net_address ,es.[program_name] ,es.[host_name] ,es.login_name ,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);
This query joins two dynamic management views (DMVs):
sys.dm_exec_connections: Provides information about the physical connections to the SQL Server.sys.dm_exec_sessions: Gives details about each active session on the SQL Server.
By grouping and counting, we can easily see the number of connections per unique combination of client IP address, program name, host name, and login name. The OPTION (RECOMPILE) ensures the query plan is recompiled each time it runs, which can be beneficial for DMVs that frequently change data.
