Retrieve the Query Plan from the Cache for a Given Stored Procedure

--retrieve the query plan from the cache for a given stored procedure

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 OBJECT_NAME(st.objectid,st.dbid) IN ('<Stored Proc Name>')

AND qp.query_plan IS NOT NULL

Dave Wentzel 2012-07-04 CONTENT
sql server performance