View Active Connections

With SQL Server 2005+, it is very easy to view the specifics of connection information. This is very useful because when troubleshooting slowdowns. Luckily there are a few dynamic management views that provide insight into connection and session information.

The following query groups the connections according the program that is connected to SQL Server. This information can be spoofed however using a connection string. When running this query, you will find how important it is to add the application name to the query string. The query also shows the number of connections opened by each application.

[cc lang=”sql”]
— By Application
SELECT
CPU = SUM(cpu_time)
,WaitTime = SUM(total_scheduled_time)
,ElapsedTime = SUM(total_elapsed_time)
,Reads = SUM(num_reads)
,Writes = SUM(num_writes)
,Connections = COUNT(1)
,Program = program_name
FROM sys.dm_exec_connections con
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = con.session_id
GROUP BY program_name
ORDER BY cpu DESC
[/cc]

This next query groups the same information by user:

[cc lang=”sql”]
— Group By User
SELECT
CPU = SUM(cpu_time)
,WaitTime = SUM(total_scheduled_time)
,ElapsedTime = SUM(total_elapsed_time)
,Reads = SUM(num_reads)
,Writes = SUM(num_writes)
,Connections = COUNT(1)
,[login] = original_login_name
from sys.dm_exec_connections con
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = con.session_id
GROUP BY original_login_name
[/cc]

By using sp_who3, you can also find the exact statements running, along with more detailed information.