Top Cached SPs By Avg Elapsed Time (SQL 2008)

  • by
  • 4 Years ago
  • 0

[sourcecode language=”sql”]
SELECT TOP(25) p.name AS [SP Name], qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
qs.total_elapsed_time, 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.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 avg_elapsed_time DESC OPTION (RECOMPILE);
[/sourcecode]

– This helps you find long-running cached stored procedures that may be easy to optimize with standard query tuning techniques

  • facebook
  • googleplus
  • twitter
  • linkedin
  • linkedin