sunil.lath 98534 (1/31/2014)
Very handy query to have but what is the best way to filter the result set. For example you are interested in plans related to a certain table? I think that could be very useful to know.
This is how I do that:
SELECT cp.objtype AS ObjectType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.usecounts AS ExecutionCount,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE st.TEXT like '%xxxxxx%' <-- Table Name here