| 437-991-3573 | Data Engineering Services
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance

Procedure Cache Analysis Queries
by BF (Principal Consultant; Architecture; Engineering)

Procedure Cache Analysis Queries:

sys.procedures, sys.dm_exec_procedure_stats

SELECT TOP(100) AS [SP Name],
qs.total_physical_reads AS [TotalPhysicalReads],
qs.total_physical_reads/qs.execution_count AS [AvgPhysicalReads],
qs.total_elapsed_time/1000/1000 as 'total_elapsed_time (sec)' ,
qs.total_elapsed_time/1000/1000/60 as 'total_elapsed_time (min)' ,
qs.total_elapsed_time/1000/1000/60/60 as 'total_elapsed_time (hr)' ,
(qs.total_elapsed_time/qs.execution_count)/1000 AS [avg_elapsed_time (millisec)],
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()
AND qs.total_physical_reads > 0
ORDER BY qs.total_elapsed_time DESC

sys.dm_exec_cached_plans, sys.dm_exec_sql_text, sysobjects

select DB_NAME(dbid), objectid,, objtype, cacheobjtype, usecounts, refcounts, p.size_in_bytes,
plan_handle, LEFT([sql].[text], 150) as [text],
from sys.dm_exec_cached_plans p
outer apply sys.dm_exec_sql_text (p.plan_handle) sql
inner join sysobjects so on = sql.objectid
ORDER BY usecounts desc


SELECT objtype AS [CacheType]
, count_big(*) AS [Total Plans]
, sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
, avg(usecounts) AS [Avg Use Count]
, sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
, sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC