sys.dm_exec_connections in SQL Server

---

sys.dm_exec_connections : –

sys.dm_exec_connections DMV shows all the connection to SQL Server and sys.dm_exec_connections is the most common DMV used to get connection details.The sys.dm_exec_connections DMV returns detailed information regarding the physical connections to SQL Server, including the protocols and security used, networkpacket information, and so on. sys.dm_exec_connections contains additional information for sessions established by external clients, including protocol details.

Note:- Databsase User required VIEW SERVER STATE permission on the server, to use this DMV in SQL Server

 

---

Sample Query to Get SQL Server Connection Count by IP Address through sys.dm_exec_connections

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
INNER JOIN sys.dm_exec_connections AS ec
ON es.session_id = ec.session_id
GROUP BY ec.client_net_address ,
es.program_name ,
es.host_name,
 es.login_name
ORDER BY es.program_name,
ec.client_net_address ;