SYSK 333: What Query Plans Are Cached in SQL Server?

  • Article

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.