-- Top Cached SPs By Execution Count (SQL 2008)
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/1000 AS [AvgWorkerTime_MSecs], qs.total_worker_time/1000 AS [TotalWorkerTime_MSecs],
qs.total_elapsed_time/1000 AS [Total_Elapsed_Time_MSecs], qs.total_elapsed_time/qs.execution_count/1000 AS [avg_elapsed_time_MSecs],
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);
-- Tells you which cached stored procedures are called the most often
-- This helps you characterize and baseline your workload
--Following is similar script from Pinal Dave:
SELECT DB_NAME(qt.[dbid]) AS 'DatabaseName', OBJECT_SCHEMA_NAME(qt.[objectid], qt.[dbid]) AS 'SchemaName', OBJECT_NAME(qt.[objectid], qt.[dbid]) AS 'SPName', qs.execution_count AS 'Execution Count', qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime', qs.total_worker_time AS 'TotalWorkerTime', qs.total_logical_reads AS 'TotalLogicalReads', qs.execution_count/DATEDIFF(Second, qs.creation_time, GETDATE()) AS 'Calls/Second', qs.creation_time 'CreationTime' FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE DB_NAME(qt.[dbid]) IS NOT NULL --remove to see all queries ORDER BY qs.total_logical_reads DESC
Comments