Top Procedures By Total Worker time

 Top  Procedures By Total Worker time

This SQL query is a handy tool for database administrators to identify the top 25 most resource-intensive stored procedures in their SQL Server database. It helps pinpoint performance bottlenecks by showing which procedures consume the most worker time, their average execution time, and even if they're suffering from missing indexes.

Here's a breakdown of what the query provides:

• SP Name: The name of the stored procedure.
• TotalWorkerTime: The cumulative CPU time (in microseconds) consumed by the procedure across all its executions. This is a key indicator of how much work a procedure is doing.
• AvgWorkerTime: The average CPU time per execution, giving you an idea of the typical cost of a single run.
• Execution_count: The total number of times the procedure has been executed since it was last cached.
• Calls/Minute: How frequently the procedure is being called, calculated by dividing the execution count by the minutes since it was cached.
• Total_elapsed_time: The total wall-clock time (in microseconds) spent executing the procedure.
• avg_elapsed_time: The average wall-clock time per execution.
• Has Missing Index: A flag (1 for yes, 0 for no) that indicates if the procedure's query plan suggests that a missing index could improve performance. This is crucial for optimization!
• Last Execution Time: When the procedure was last run.
• Plan Cached Time: When the execution plan for the procedure was cached. This is useful for understanding how fresh the statistics are.
• Query Plan: (Commented out by default) If uncommented, this would show the XML-formatted execution plan, which provides detailed information about how SQL Server executes the procedure.

The query joins `sys.procedures` with `sys.dm_exec_procedure_stats` to get performance metrics and uses `sys.dm_exec_query_plan` to check for missing indexes. It filters for procedures in the current database and those with a cached plan that's not brand new, ordering the results by `TotalWorkerTime` in descending order to show the heaviest hitters first. The `OPTION (RECOMPILE)` hint ensures that the query itself gets a fresh execution plan each time it's run, which can be helpful for queries used in performance monitoring.

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(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute]
 ,qs.total_elapsed_time
 ,qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time]
 ,CASE WHEN CONVERT(NVARCHAR(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index]
 ,FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Last Execution Time]
 ,FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]
-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan
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]
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.database_id = DB_ID()
 AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0
ORDER BY qs.total_worker_time DESC
OPTION (RECOMPILE);

This script is an essential tool for any SQL Server DBA looking to proactively identify and address performance issues within their stored procedures. By regularly running and analyzing its output, you can ensure your database remains optimized and responsive.