SQL script to lookup connection count

From a DBAs perspective it is always important to monitor the number of connections that are targeting the database from a specific session id. This can be achieved by joining the DMV’s sys.dm_exec_sessions and sys.dm_exec_connections. The interesting part of the query is it captures the end client IP address as well as the client’s hostname. Below is the sql:


SELECT DMEC.CLIENT_NET_ADDRESS as "Client's IP Address",
DMES.[PROGRAM_NAME]as 'Program Name' ,
DMES.[HOST_NAME]as 'Client HostName' ,
DMES.LOGIN_NAME 'Login',
COUNT(DMEC.SESSION_ID) AS [Number of Connections]
FROM SYS.DM_EXEC_SESSIONS AS DMES
INNER JOIN SYS.DM_EXEC_CONNECTIONS AS DMEC
ON DMES.SESSION_ID = DMEC.SESSION_ID
GROUP BY DMEC.CLIENT_NET_ADDRESS ,
DMES.[PROGRAM_NAME] ,
DMES.[HOST_NAME] ,
DMES.LOGIN_NAME
ORDER BY DMEC.CLIENT_NET_ADDRESS ,
DMES.[PROGRAM_NAME]

Hope this helps.

Advertisements
This entry was posted in SQL Server DBA Stuff and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s