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.
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
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