Using DMV's to Find SQL Server Connection and Session Info

It is often times useful to understand how your application is connecting to SQL Server and any other connections that may be present to your application's database.  This can be understood by lookng at the sys.dm_exec_sessions view.  Here is a sample query to use:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

SELECT database_id, -- SQL Server 2012 and after only session_id, status, login_time, cpu_time, memory_usage, reads, writes, logical_reads, host_name, program_name, host_process_id, client_interface_name, login_name as database_login_name, last_request_start_time FROM sys.dm_exec_sessions WHERE is_user_process = 1 ORDER BY cpu_time DESC;

This simply lists all of the current connections to SQL Server, but we also get some useful information with each connection.  We see we have fields like cpu_time (in milliseconds), memory_usage (in 8 KB pages), reads, writes and logical_reads.  So if we have a session that is consuming a lot of resources in SQL Server, we can immediately see that by looking at these fields.

We also have columns like host_name, program_name and host_process_id which can help us identify where a connection is coming from.  You would be surprised how many times you look at a production database and see users connecting directly to the database from applications like Management Studio or even Excel.

We can write a little bit different query to get a different look at this data:

1 2 3 4 5 6 7 8 9 10 11

SELECT login_name, host_name, host_process_id, COUNT(1) As LoginCount FROM sys.dm_exec_sessions WHERE is_user_process = 1 GROUP BY login_name, host_name, host_process_id;

This counts up all of the logins by login name and client process.  So if you have an ASP.NET web application, you would see each worker process with the number of connections the worker process has to the database, plus any other processes that might be connected up.  Sometimes this is a useful view, because you can validate that indeed all of the machines in your web cluster are indeed talking to the database and you can get a feel for how many connections they have in their connection pools.

It might seem simple to know who is logged in, but what this can help you do is understand any connectivity issues you may be having and see if there is a session using a lot of resources, so for those reasons, these simple queries against the dm_exec_sessions view can tell you quite a bit.