Subscribe to this blog
Briefcase
Print
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;
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......