SQL Server DMV script for searching the plan cache by query text, plan handle or execution time

  • Article

I guess everyone has their own set of DMV scripts to hand when working with SQL Server nowadays. I thought I’d post a few of mine. Pretty much as the title says: comment in whichever clauses suit you and then run the query. Returns a result set with an XML column that can be clicked on if you want, to view the plan itself in SSMS. Works with 2005 and 2008.

 select 
 bucketid,
 a.plan_handle,
 refcounts, 
 usecounts,
 execution_count,
 size_in_bytes,
 cacheobjtype,
 objtype,
 text,
 query_plan,
 creation_time,
 last_execution_time,
 execution_count,
 total_elapsed_time,
 last_elapsed_time
  
 from sys.dm_exec_cached_plans a 
       inner join sys.dm_exec_query_stats b on a.plan_handle=b.plan_handle
     cross apply sys.dm_exec_sql_text(b.sql_handle) as sql_text
     cross apply sys.dm_exec_query_plan(a.plan_handle) as query_plan
 where 1=1
 and text like '%mybadproc%'
 -- and a.plan_handle = 0x06000B00C96DEC2AB8A16D06000000000000000000000000
 and b.last_execution_time between '2009-09-24 09:00' and '2009-09-25 17:00'
 order by last_execution_time desc