Saturday, March 7, 2020

Top Procedures By Avg Elapsed Time

 Top  Procedures By Avg Elapsed Time

Ever wondered which stored procedures are hogging your SQL Server's resources? Identifying slow-performing stored procedures is crucial for database optimization. This handy SQL query helps you pinpoint those resource-intensive procedures by analyzing their execution statistics.

Understanding Slow Stored Procedures with SQL

This query provides a comprehensive look at your stored procedures' performance, ordered by average elapsed time. It's a great starting point for identifying bottlenecks and understanding where you might need to focus your optimization efforts.

SELECT TOP (25) p.name AS [SP Name]
    ,qs.min_elapsed_time
    ,qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time]
    ,qs.max_elapsed_time
    ,qs.last_elapsed_time
    ,qs.total_elapsed_time
    ,qs.execution_count
    ,ISNULL(qs.execution_count / DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute]
    ,qs.total_worker_time / qs.execution_count AS [AvgWorkerTime]
    ,qs.total_worker_time AS [TotalWorkerTime]
    ,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 avg_elapsed_time DESC
OPTION (RECOMPILE);

How This Query Helps You:

  • SP Name: Clearly identifies the stored procedure.
  • avg_elapsed_time: This is key! It shows the average time taken for each execution, helping you spot the slowest procedures.
  • execution_count: Indicates how frequently the procedure runs. A high count with a high average elapsed time is a red flag.
  • Calls/Minute: Gives you an idea of the procedure's invocation rate.
  • Has Missing Index: A quick indicator if the procedure's query plan suggests missing indexes, which can significantly impact performance.

By running this query, you can get a snapshot of your database's procedure performance, enabling you to proactively address potential performance issues. Remember to run this in the context of the database you want to analyze.

at

Popular Posts