SYSK 333: What Query Plans Are Cached in SQL Server?
It’s well known that, to improve performance, SQL Server caches query execution plans in memory. But would you like to know what query execution plans are currently in the cache?
The following query will yield that information:
select substring(st.text,
(qs.statement_start_offset/2) + 1,
((case qs.statement_end_offset when -1 then datalength(st.text) else qs.statement_end_offset end
- qs.statement_start_offset)/2) + 1) as query,
qs.execution_count, qs.last_worker_time, qs.max_worker_time, qs.last_execution_time
from sys.dm_exec_cached_plans cp
inner join sys.dm_exec_query_stats qs on cp.plan_handle = qs.plan_handle
cross apply sys.dm_exec_sql_text(qs.sql_handle) st
On a different, but related note, to clear up procedure cache from SQL Server, run DBCC FREEPROCCACHE command.
