Top Cached Stored Procedure by Execution Count

-- 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:

-- https://blog.sqlauthority.com/2019/08/05/sql-server-list-all-frequently-ran-stored-procedure-from-server-cache/

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