Monitoring Cached Stored Procedure Performance in SQL Server
SQL Server provides a dynamic management view called sys.dm_exec_procedure_stats that helps you analyze the performance of cached stored procedures. This view returns one row for each stored procedure currently held in the plan cache. Once a procedure is removed from the cache, its corresponding row is also removed from the view.
Accessing this information requires the VIEW SERVER STATE permission on the SQL Server instance.
Sample Query: Analyze Cached Procedure Statistics
The following SQL query retrieves performance metrics for each cached stored procedure. It includes information such as the procedure name, database name, cache time, execution count, and average execution duration.
-- Procedure Performance Statistics
SELECT
d.object_id,
d.database_id,
OBJECT_NAME(object_id, database_id) AS [proc name],
DB_NAME(database_id) AS dbname,
d.cached_time,
d.last_execution_time,
d.total_elapsed_time,
d.total_elapsed_time / d.execution_count AS [avg_elapsed_time],
d.last_elapsed_time,
d.execution_count
FROM
sys.dm_exec_procedure_stats AS d
ORDER BY
[total_worker_time] DESC;
This query is particularly useful for identifying procedures with high resource consumption or long execution times, helping DBAs optimize performance by focusing on the most impactful routines.
Further Reading
-
Microsoft Documentation on
sys.dm_exec_procedure_stats:
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-procedure-stats-transact-sql?view=sql-server-ver15 -
Brent Ozar’s article on cached times for stored procedures vs. SQL statements:
https://www.brentozar.com/archive/2016/02/stored-procedure-cached-time-vs-sql-statement-cached-time/
