How to find expensive stored procedures in SQL Server

There may be a time when you find your database server(s) is/are utilising a high percentage of CPU resources. If this is the case, the first step in diagnosing the issue is to find out exactly what SQL Server is doing. One way you can do this, is to run the following query in SQL Server Management Studio:

SELECT TOP (25) 
  p.name AS [SP Name], 
  qs.total_worker_time AS [TotalWorkerTime], 
  qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], 
  qs.execution_count, 
  ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) 
    AS [Calls/Second],
  qs.total_elapsed_time, 
  qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], 
  qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

The output is ordered by TotalWorkerTime, however you may wish to change this to avg_elapsed_time, to see which SP’s are taking the longest to execute. Don’t forget to take into account execution_count, as you may be able to find a way to reduce this within your applications for better performance.