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

A DMV a Day – Day 29

The DMV for Day 29 is sys.dm_exec_connections, which is described by BOL as:

Returns information about the connections established to this instance of SQL Server and the details of each connection.

This DMV can be used to help identify who and what is connecting to an instance of SQL Server, with some useful information about each connection.  This DMV works with SQL Server 2005, SQL Server 2008, and 2008 R2. It requires VIEW SERVER STATE permission.

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

This particular query gives you the IP address, program or data access type name, the machine name, and login name for machine machine that is connected to your SQL Server instance, along with the number of open database connections for each one. I find this to be extremely useful information in several ways. It lets you see if anyone is using SQL Server Management Studio (SSMS) to connect to your instance. It lets you see which middle-tier servers are connecting to your server, and how many connections each one of them have (which is very helpful when you are trying to help your developers debug application or connectivity issues).

One more day in the series, then I get to write a recap of the entire series…

Comments

Posted by Dukagjin Maloku on 29 April 2010

Nice one, thanks!

Posted by Anonymous on 29 April 2010

Pingback from  Dew Drop – April 29, 2010 | Alvin Ashcraft's Morning Dew

Posted by Jason Brimhall on 29 April 2010

Thanks Glenn.  29 down and 1 to go.

Posted by Anonymous on 30 April 2010

Pingback from  Twitter Trackbacks for                 SQL Server Central, A DMV a Day ??? Day 29 - Glenn Berry's SQL Server Performance         [sqlservercentral.com]        on Topsy.com

Posted by Anonymous on 2 May 2010

Pingback from  File Sharing

Leave a Comment

Please register or log in to leave a comment.