Find Resource Usage by Application

Often times when troubleshooting, it is helpful to know how much resources a particular application is requesting from your SQL Server. The easiest way to get this information is by using the connections and sessions DMV. These DMV’s when combined are powerful.

Here we will query the aggregate connection information. The values below represent an aggregate value of all connections that are currently established by that application.

[cc lang=”sql”]

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]

Note that the above query does not break down the values per user. In order to do that, we need to create an additional grouping condition (login_name).

[cc lang=”sql”]
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
,LoginName = ses.login_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, ses.login_name
ORDER BY cpu DESC
[/cc]