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

 

This entry was posted in SQL Server 2005. Bookmark the permalink.

One Response to SQL Server 2005 Stored Procedure Execution Statistics

  1. Scott says:

    Very nice – thanks for sharing :)http://www.learnsqlserver.com/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s