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