There is a new sys.dm_exec_procedure_stats DMV that was added in SQL Server 2008, that lets you get aggregate performance statistics for all cached stored procedures in a particular database. In the sample query below, I do a simple inner join with the sys.procedures table to get the actual stored procedure name (instead of grabbing it from the text of the SP). This DMV is useful because it is aggregated at the procedure level instead of the statement level. This makes it easier to use with stored procedures that have multiple statement level plans (which made the 2005 specific query more confusing).
-- Top SPs By Execution Count (SQL 2008) SELECT TOP (100) 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.last_elapsed_time, qs.cached_time, qs.last_execution_time FROM sys.procedures AS p INNER JOIN sys.dm_exec_procedure_stats AS qs ON p.object_id = qs.object_id ORDER BY qs.execution_count DESC; -- Top SPs By Execution Count (SQL 2005) SELECT TOP (100) qt.text AS 'SP Name', qs.execution_count AS 'Execution Count', qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second', qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime', qs.total_worker_time AS 'TotalWorkerTime', qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime', qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads, DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache' FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE qt.dbid = db_id() -- Filter by current database ORDER BY qs.execution_count DESC
Technorati Tags: SQL Server 2008