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…

This entry was posted in SQL Server 2008. 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 )

Connecting to %s