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. 


 


 

Comments (0)

Skip to main content