DMV-1 : Who All Are connected ? …. sys.dm_exec_connections

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/)