The sys.dm_exec_connections DMV (Dynamic Management View) is represented by Books Online (BOL) as follows : http://msdn.microsoft.com/en-us/library/ms181509.aspx
Returns information about the connections established to this instance of SQL Server and the details of each connection.
sys.dm_exec_connections is the most common DMV used to get connection details. We will get details info regarding every connection like protocol user, last read time, last write time, last executed SQL query, connection time etc.
Query 1 : Complete details of every SQL Connection
SELECT
C.SESSION_ID,
C.MOST_RECENT_SESSION_ID,
C.CONNECT_TIME,
C.LAST_READ,
C.LAST_WRITE,
C.NUM_READS,
C.NUM_WRITES,
C.NET_TRANSPORT,
C.ENCRYPT_OPTION,
C.AUTH_SCHEME,
C.PROTOCOL_TYPE,
C.PROTOCOL_VERSION,
C.NET_PACKET_SIZE,
C.ENDPOINT_ID,
C.CLIENT_NET_ADDRESS,
C.CLIENT_TCP_PORT,
C.LOCAL_NET_ADDRESS,
C.LOCAL_TCP_PORT,
C.NODE_AFFINITY,
C.CONNECTION_ID,
C.PARENT_CONNECTION_ID,
C.MOST_RECENT_SQL_HANDLE,
CASE WHEN ST.DBID = 32767 THEN ‘RESOURCEDB’ ELSE DB_NAME(ST.DBID) END AS DATABASE_NAME,
CASE WHEN ST.DBID IS NULL THEN NULL ELSE OBJECT_SCHEMA_NAME(ST.OBJECTID, ST.DBID) END AS OBJECT_SCHEMA_NAME,
CASE WHEN ST.DBID IS NULL THEN NULL ELSE OBJECT_NAME(ST.OBJECTID, ST.DBID) END AS OBJECT_NAME,
ST.TEXT AS QUERY_TEXT
FROM
SYS.DM_EXEC_CONNECTIONS C
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(C.MOST_RECENT_SQL_HANDLE) ST
Query 2 : Sample Query Get a count of SQL connections by IP address
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 EC.CLIENT_NET_ADDRESS ,
ES.[PROGRAM_NAME] ;
Permissions : User required VIEW SERVER STATE permission on the server, to use this DMV.
If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun
Reference : Rohit Garg (http://mssqlfun.com/)
