Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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;

Comments

Posted by Dugi on 28 December 2009

Nice script Glenn!

It was very nice and simple results!

Thnx for sharing!

Posted by radhika on 29 December 2009

very Helpful Glenn

Thanx

Posted by NJ-597368 on 29 December 2009

Very useful script. Thank you.

Posted by Glenn Berry on 29 December 2009

Glad you like the queries.

Posted by Craig Benson on 29 December 2009

Very slick!  This is quite helpful!

Posted by romano.gazzola on 29 December 2009

nice and helpful. Tank You

Posted by madhuri on 29 December 2009

Thank you for sharing with all of us.

Its really very useful and simple as well.

Posted by Richard on 29 December 2009

handy and useful. thanks for sharing !

Posted by nelsonj on 29 December 2009

Any chance there is a version for those of us (sob...) still stuck in SQL 2000-land??

Posted by Rudy Panigas on 29 December 2009

Well done! Keep up the good stuff!

Posted by Deepak on 29 December 2009

Very very handy, thanks Glen!

Posted by Gopal Singh on 29 December 2009

Exceelent query... nice and simple.... greate job glenn keep it up

Posted by pyisoehein on 29 December 2009

Thank you for your queries statement ...very useful

Posted by karthikeyan on 29 December 2009

I need to find out the active/ideal connections from app server.

Please provide the script.

Posted by Atif Shehzad on 29 December 2009

Thanks for sharing this handy monitoring script.

Posted by shelendra.sharma on 29 December 2009

Excellent Queries. Thanks Glenn

Posted by akumar-913757 on 29 December 2009

how can i get the same result in sql 2000

Posted by sandeep singh on 29 December 2009

Simply its excellent query to fetch concurrent connections.

Posted by SULDANEYR on 30 December 2009

THANKS FOR YOUR SUPPORT

Posted by Denis Wilkinson on 30 December 2009

Simple and effective, thanks.

Posted by bcobb2350 on 30 December 2009

Thank you Glenn,

This is very elegant and powerful - very helpful!

Posted by jozwickdb on 30 December 2009

I will add to my toolbox.  Thank you - appreciate the additional tool.

Posted by yusufugur on 31 December 2009

Very Useful..

Posted by rcarrico on 4 January 2010

Great job!  I do have one concern, for some reason, I get hits for the login 'sa'.  The funny thing is, I don't have a login 'sa'.  I renamed the sa login weeks ago.

Posted by Steve Stout on 4 January 2010

Love these queries! Just created a page on our internal application to view these for quick checks when we are having a system slowdown. Thanks!

Posted by anuja-jadhav on 6 January 2010

Simple and best one….

Posted by shejuguru on 5 February 2010

Good Work , Keep going......

Posted by bauermsn on 14 November 2013

powerful, thanks !!!

Leave a Comment

Please register or log in to leave a comment.