Top Cached SPs By Execution Count (SQL 2008)

[sourcecode language=”sql”]

SELECT TOP(250) p.name AS [SP Name], qs.execution_count,

ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],

qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.total_worker_time AS [TotalWorkerTime],

qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],

qs.cached_time

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]

WHERE qs.database_id = DB_ID()

ORDER BY qs.execution_count DESC OPTION (RECOMPILE);

[/sourcecode]

– This script tells you which cached stored procedures are called the most often

– This helps you characterize and baseline your workload

 
comments powered by Disqus