Search Wiki:

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

Arnie Rowland, April 22, 2008

Often it is useful to determine which procedures (or code) have the greatest Total Impact on the Server. Sometimes, that is determined by examining the I/O cost, sometimes by the Exectution Duration. The suggestion presented here will examine a SQL Server 2005 Server and return a list of the Procedures (or Queries) that have the greatest total impact on the Server. In this example, Total Impact is determined by examining the length of execution and the frequency of execution.

When attempting to determine how best to allocate resouces in order to tune a Server, it is sometimes assumed that finding the longest running Procedures, or Queries, and then working to tune those Procedures or Queries will have the greatest impact in performance tuning. In the example below, it is suggested that greater benefit may be gained by examining the Total Impact of Procedures or Queries, that is, BOTH execution time and frequency. For example, tuning a long running query so that execution time is shortened by 45 seconds, but only executed one time per minute, will have less Return On Investment (ROI) than shaving one second off of a query that executes 120 times per second.


SQL Server 2005 / SQL Server 2008 Solution
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
-- WHERE ...
ORDER BY 
   s.total_elapsed_time DESC

ProcedureName ExecutionCount AvgExecutionTime AvgWorkerTime TotalWorkerTime MaxLogicalReads MaxLogicalWrites CreationDateTime CallsPerSecond
Exec Proc1 1 3723204 3596939 3596939 36249 112 2008-03-25 00:04:15.950 0
Exec Proc2 1 2512181 2389573 2389573 703 0 2008-03-24 23:59:15.980 0
Exec Proc3 1 1080008 971041 971041 443 0 2008-03-25 00:16:07.820 0
Exec Proc4 1 1032135 935483 935483 223 0 2008-03-25 00:13:55.193 0
Exec Proc5 1 955338 912818 912818 223 0 2008-03-25 00:15:06.617 0
Exec Proc6 1 946446 906498 906498 346 0 2008-03-25 00:15:33.227 0
Exec Proc7 1 635835 634566 634566 561 0 2008-03-24 23:22:00.280 0
Exec Proc8 1 481766 475023 475023 36202 105 2008-03-25 00:13:55.180 0
Exec Proc9 1 349026 343143 343143 36202 105 2008-03-25 00:15:06.600 0
Exec Proc10 1 346581 337336 337336 36202 105 2008-03-25 00:15:33.227 0

You may filter for a specific database by adding a filtering criteria in the WHERE clause, filtering for dbID in the dynamic management view sys.dm_exec_sql_text, something like this for the current database:

WHERE t.dbid = db_id()

Return to Top

___________________________________________________________________________________________________________________
Page Created By: Arnie Rowland, Apr 22, 2008
Last edited Apr 27 2008 at 4:08 AM  by ArnieRowland, version 11
Comments
BloodBaz wrote  Aug 29 2012 at 10:33 AM  
What units are the time based values in? microseconds?

Updating...
Page view tracker