Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQL Server plan cache bloat
with RedundantQueries as
(select top 20
query_hash as QueryHash,
count(query_hash) as PlansCached,
count(distinct(query_hash)) as DistinctPlansCached,
sum(execution_count) as TotalExecutions,
min(creation_time) as FirstPlanCreationTime,
max(creation_time) as LastPlanCreationTime,
max(s.last_execution_time) as LastExecutionTime,
statement_start_offset as StatementStartOffset,
statement_end_offset as StatementEndOffset
from sys.dm_exec_query_stats s
group by query_hash, statement_start_offset, statement_end_offset
order by 2 desc)
select
r.QueryHash,
r.PlansCached,
(select sum(size_in_bytes)
from sys.dm_exec_cached_plans p
inner join sys.dm_exec_query_stats s on s.plan_handle = p.plan_handle and s.query_hash = r.QueryHash
) as TotalPlanSize,
r.DistinctPlansCached,
r.TotalExecutions,
q.SampleQueryText,
q.SampleQueryPlan,
r.FirstPlanCreationTime,
r.LastPlanCreationTime,
r.LastExecutionTime
from RedundantQueries r
cross apply (select top 1 st.text as SampleQueryText, qp.query_plan as SampleQueryPlan
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply sys.dm_exec_query_plan(qs.plan_handle) as qp
where r.QueryHash = qs.query_hash
and r.StatementStartOffset = qs.statement_start_offset
and r.StatementEndOffset = qs.statement_end_offset
order by qs.total_elapsed_time desc) q
order by r.PlansCached desc
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment