Find TOP n longest running Procedures and Queries ...

Find the TOP n Longest Running Procedures (or Queries), Ordered by Total Impact on Server






SQL Server 2005 / 2008:

SELECT TOP 10
   ProcedureName    = t.text,
   ExecutionCount   = s.execution_count,
   AvgExecutionTime = isnull( s.total_elapsed_time / s.execution_count, 0 ),
   AvgWorkerTime    = s.total_worker_time / s.execution_count,
   TotalWorkerTime  = s.total_worker_time,
   MaxLogicalReads  = s.max_logical_reads,
   MaxLogicalWrites = s.max_logical_writes,
   CreationDateTime = s.creation_time,
   CallsPerSecond   = isnull( s.execution_count / datediff( second, s.creation_time, getdate()), 0 )
FROM sys.dm_exec_query_stats s
   CROSS APPLY sys.dm_exec_sql_text( s.sql_handle )  t
ORDER BY
   s.total_elapsed_time DESC