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…