Sunday, March 8, 2020

Top Procedure by execution count

Top Procedure by execution count

Ever wondered which stored procedures are hogging resources or running most frequently in your SQL Server database? This handy SQL query can help you pinpoint those performance bottlenecks by leveraging dynamic management views (DMVs) like `sys.dm_exec_procedure_stats` and `sys.dm_exec_query_plan`.

Unveiling Stored Procedure Performance

This script gives you a quick overview of your top 100 stored procedures based on their execution count. It's a great way to identify frequently used procedures that might benefit from optimization. 

SELECT TOP (100) p.name AS [SP Name]
 ,qs.execution_count AS [Execution Count]
 ,ISNULL(qs.execution_count / DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute]
 ,qs.total_elapsed_time / qs.execution_count AS [Avg Elapsed Time]
 ,qs.total_worker_time / qs.execution_count AS [Avg Worker Time]
 ,qs.total_logical_reads / qs.execution_count AS [Avg Logical Reads]
 ,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.execution_count DESC
OPTION (RECOMPILE);

What the Query Reveals:

  • **SP Name:** The name of your stored procedure.
  • **Execution Count:** How many times the procedure has been executed since it was cached.
  • **Calls/Minute:** The execution rate – a good indicator of how busy a procedure is.
  • **Avg Elapsed Time:** The average total time taken for the procedure to complete.
  • **Avg Worker Time:** The average CPU time consumed by the procedure.
  • **Avg Logical Reads:** The average number of logical reads performed, indicating I/O activity.
  • **Has Missing Index:** A crucial flag that tells you if the query plan suggests a missing index, which could significantly boost performance if addressed.
  • **Last Execution Time:** When the procedure was last run.
  • **Plan Cached Time:** When the execution plan for this procedure was cached.

This script is built upon two powerful SQL Server DMVs:

By understanding these metrics, you can make informed decisions about where to focus your performance tuning efforts!

at

Popular Posts