Table of Contents
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
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
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;
Learn about SQL Server detecting logical consistency based issues and how to resolve them.
Learn about SQL Server error 1222 and how to resolve the lock request time out…
Discover the new features of Microsoft SQL Server 2022 and how they compare to previous…
SQL Server Error 1222 lock request time out period exceeded Lock request time out…
SQL Server Error : 427, Severity: 20. Could not load the definition for constraint ID…
SQL Server Error : 204, Severity: 20. Normalization error in node %ls.
This website uses cookies.