SQL Server plan cache bloat

queries-with-multiple-plans.sql

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