sys.dm_exec_procedure_stats is a SQL Server dynamic management view for investigating stored-procedure performance. It exposes statistics for procedures currently in the plan cache, helping identify candidates for tuning. section 10.1
-- Top 10 worst-performing stored procedures based on total elapsed time
SELECT TOP 10
DB_NAME(ps.database_id) AS DatabaseName,
OBJECT_NAME(ps.[object_id], ps.database_id) AS StoredProcedure,
ps.execution_count AS ExecutionCount,
-- Avg elapsed time in microseconds
ps.total_elapsed_time / ps.execution_count AS AvgElapsedTime,
-- Avg CPU time in microseconds
ps.total_worker_time / ps.execution_count AS AvgCPU,
-- Avg logical reads
ps.total_logical_reads / ps.execution_count AS AvgReads
FROM sys.dm_exec_procedure_stats AS ps
ORDER BY ps.total_elapsed_time / ps.execution_count DESC;
This query lists the top 10 procedures by average elapsed time and includes database, execution-count, CPU, and logical-read columns. section 10.1
Use the results as a starting point for investigation:
- Compare average elapsed time, average CPU, and average logical reads to find procedures that may be slowing the database. section 10.3
- Treat the ranking as an indicator rather than proof: procedures with highly variable execution times may need a different analysis. section 10.3
- Remember that cache eviction and SQL Server service restarts remove or reset the available statistics. section 10.1 section 10.3
- Obtain VIEW SERVER STATE permission before querying the DMV. section 10.1
