App Query Duration

A query that reports how long each of a specific application's queries has been executing.

Published Dec 24, 2009

How long has each of your application's queries been running? This query groups sysprocesses by SPID for a specific program name and reports the command, thread count, start time and elapsed execution time in minutes - sorted longest first. Change the program_name filter at the bottom to match your application.

select
      cast(spid as varchar),
      MAX(cmd) as Command,
      count(0) as Threads,
      max(last_batch) as [Start Time],
      cast(DATEDIFF(s, max(last_batch), GETDATE()) / 60.0 as Decimal(16,2)) as [Execution Time (m)]
from
      sys.sysprocesses
where
      program_name = 'Infield_Reports'
      and cmd <> 'AWAITING COMMAND'
group by
      spid
order by
      cast(DATEDIFF(s, max(last_batch), GETDATE()) / 60.0 as Decimal(16,2)) desc