For Day 21 of this series, we start out with Query #48, which is SP Avg Elapsed Time. This query retrieves information from the sys.procedures object catalog view and the sys.dm_exec_procedure_stats dynamic management view about the cached stored procedures that have the highest average elapsed time in the current database. Query #48 is shown in Figure 1.

   1: -- Top Cached SPs By Avg Elapsed Time (Query 48) (SP Avg Elapsed Time)

   2: SELECT TOP(25) p.name AS [SP Name], qs.min_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], 

   3: qs.max_elapsed_time, qs.last_elapsed_time, qs.total_elapsed_time, qs.execution_count, 

   4: ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute], 

   5: qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], 

   6: qs.total_worker_time AS [TotalWorkerTime], qs.cached_time

   7: FROM sys.procedures AS p WITH (NOLOCK)

   8: INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)

   9: ON p.[object_id] = qs.[object_id]

  10: WHERE qs.database_id = DB_ID()

  11: ORDER BY avg_elapsed_time DESC OPTION (RECOMPILE);

  12:  

  13: -- This helps you find high average elapsed time cached stored procedures that

  14: -- may be easy to optimize with standard query tuning techniques

Figure 1: Query #48 SP Avg Elapsed Time

This query gives you a chance to look like a super hero. It shows you the cached stored procedures that have the highest average elapsed time in the current database. This basically gives you a list of stored procedures to look at much more closely, to see if you can do any query optimization or index tuning to make them dramatically faster. If you are able to do your DBA magic and make a long-running stored procedure run much, much faster, people are going to notice, and perhaps think you are some sort of evil genius.

 

Query #49 is SP Worker Time. This query retrieves information from the sys.procedures object catalog view and the sys.dm_exec_procedure_stats dynamic management view about the cached stored procedures that have the highest cumulative worker time in the current database. Query #49 is shown in Figure 2.

   1: -- Top Cached SPs By Total Worker time. Worker time relates to CPU cost  (Query 49) (SP Worker Time)

   2: SELECT TOP(25) p.name AS [SP Name], qs.total_worker_time AS [TotalWorkerTime], 

   3: qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count, 

   4: ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],

   5: qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count 

   6: AS [avg_elapsed_time], qs.cached_time

   7: FROM sys.procedures AS p WITH (NOLOCK)

   8: INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)

   9: ON p.[object_id] = qs.[object_id]

  10: WHERE qs.database_id = DB_ID()

  11: ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

  12:  

  13: -- This helps you find the most expensive cached stored procedures from a CPU perspective

  14: -- You should look at this if you see signs of CPU pressure

Figure 2: Query #49 SP Worker Time

This query shows you which cached stored procedures have the highest cumulative total worker time in the current database. Worker time means CPU cost. If your instance or server is under CPU pressure, than looking at the stored procedures that show up at the top of this diagnostic query should be a high priority. Even if you are not under sustained CPU pressure, keeping an eye on the top offenders on this query is a good idea. Quite often, you will find the same stored procedures showing up on several of these different “Top SP cost” queries, which means that the SP in question is expensive from multiple perspectives.