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.

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

This next query groups the same information by user:

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

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

6 comments
Farai T 18 Nov 2011 at 7:54 am

i have a domain running on windows server 2000, i also have a pc running on windows 7 acting as the pastel evolution software server, here is my query/ problem

Unable to connect to server ‘ACCOUNTSSQLEXPRESS”. Reason: timeout expired

please help guys, will really appreciate.

ciaran 03 Jul 2011 at 6:34 am

Derek,
I was looking at your code “– 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”
and wondered if I understood you correctly. Are you saying we should put the application name in the program_name variable?

Thanks

Derek Dieter 08 Jul 2011 at 10:17 pm

I Ciaran,

Actually this query will group the queries by application name. And yes that will show under program_name

Thanks

Featured Articles

 Site Author