SQL Server 2005 Stored Procedure Execution Statistics

Here is a slightly improved DMV query that lets you look at the sys.dm_exec_query_stats and sys.dm_exec_sql_text views to find out which cached stored procedures are being executed most frequently. The times are in microseconds, while the Age in Cache is in minutes. These stats are completely cleared out when SQL Server 2005 is restarted, and individual stored procedures are cleared out when they are recompiled (whether explicitly or due to a statistics change).

This is a lot easier than trying to use SQL Profiler to gather this type of information.

— Get Top 200 executed SP’s ordered by calls/minute
SELECT TOP 200 qt.text AS ‘SP Name’, qs.execution_count AS ‘Execution Count’,
qs.total_worker_time/ISNULL(qs.execution_count, 1) AS ‘AvgWorkerTime’,
qs.total_worker_time AS ‘TotalWorkerTime’,
qs.total_elapsed_time/ISNULL(qs.execution_count, 1) AS ‘AvgElapsedTime’,
qs.max_logical_reads, qs.max_logical_writes, qs.creation_time,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS ‘Age in Cache’,
qs.execution_count/ISNULL(DATEDIFF(Minute, qs.creation_time, GetDate()), 1) AS ‘Calls/Minute’,
qs.execution_count/ISNULL(DATEDIFF(Second, qs.creation_time, GetDate()), 1) AS ‘Calls/Second’
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = 5 — Change this for the database you are interested in
ORDER BY qs.execution_count/ISNULL(DATEDIFF(Minute, qs.creation_time, GetDate()), 1) DESC