To Check Active Connections to SQL Server Database grouped by User

Below query is very helpful in finding the Total CPU usage, Waiting Time, Elapsed Time, Number of Reads, Number of Writes, connections grouped by Login name related which are active in SQL Server Database.

To check Active Connections to SQL Server Database grouped by Login name


SELECT SUM(s.cpu_time) AS CPU,
SUM(s.total_scheduled_time) AS WaitTime,
SUM(s.total_elapsed_time) AS ElapsedTime,
SUM(c.num_reads) AS Reads,
SUM(c.num_writes) AS Writes,
COUNT(1) AS Connections,
s.original_login_name AS login
FROM sys.dm_exec_connections AS c
LEFT OUTER JOIN sys.dm_exec_sessions AS s
ON s.session_id = c.session_id
GROUP BY s.original_login_name


Below query is very helpful in finding the Total CPU usage, Waiting Time, Elapsed Time, Number of Reads, Number of Writes, connections Grouped by application name related which are active in SQL Server Database.

To check Active Connections to SQL Server Database grouped by Application or Client


SELECT SUM(s.cpu_time) AS CPU,
SUM(s.total_scheduled_time) AS WaitTime,
SUM(s.total_elapsed_time) AS ElapsedTime,
SUM(c.num_reads) AS Reads,
SUM(c.num_writes) AS Writes,
COUNT(1) AS Connections,
s.program_name AS Application
FROM sys.dm_exec_connections AS c
LEFT OUTER JOIN sys.dm_exec_sessions AS s
ON s.session_id = c.session_id
GROUP BY s.program_name
ORDER BY cpu DESC


Below query is very helpful in finding the Total CPU usage, Waiting Time, Elapsed Time, Number of Reads, Number of Writes, connections Grouped by application name with much more details like protocols, Client interface, Encrypted or not etc…related which are active in SQL Server Database.

The Query below gives summarized information of active connections with more information like
1. Protocols: TCP/IP, named pipes or shared memory
2. Client interface like ODBC, OleDB or Ado.Net
3. To TSql or Soap endpoints
3. Authentification with SQL Account, Kerberos or NTLM

WITH con AS
(SELECT SES.host_name AS HostName
,CON.client_net_address AS ClientAddress
,SES.login_name AS LoginName
,SES.program_name AS ProgramName
,EP.name AS ConnectionTyp
,CON.net_transport AS NetTransport
,CON.protocol_type AS ProtocolType
,CONVERT(VARBINARY(9), CON.protocol_version) AS TDSVersionHex
,SES.client_interface_name AS ClientInterface
,CON.encrypt_option AS IsEncryted
,CON.auth_scheme AS Auth
FROM sys.dm_exec_connections AS CON
LEFT JOIN sys.endpoints AS EP
ON CON.endpoint_id = EP.endpoint_id
INNER JOIN sys.dm_exec_sessions as SES
ON CON.session_id = SES.session_id)
-- Detailed list
SELECT *
FROM con
-- Optional filter
--WHERE con.ClientInterface = 'ODBC'
ORDER by con.TDSVersionHex,con.HostName
,con.LoginName
,con.ProgramName;