How To Get a Count of SQL Connections BY IP Address

These queries (which work on both SQL Server 2005 and 2008) are very handy if you want to know who is connecting to a SQL Server instance and how they are doing it.  It will give you a nice idea how your application or web servers are behaving in terms of their number of open connections.

-- 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];

-- Get a count of SQL connections by login_name
SELECT login_name, COUNT(session_id) AS [session_count] 
FROM  sys.dm_exec_sessions
GROUP BY login_name
ORDER BY login_name;

This entry was posted in SQL Server 2008. Bookmark the permalink.

One Response to How To Get a Count of SQL Connections BY IP Address

  1. Pingback: Number of Connections on SQL SERVER. | Guilherme Morais

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