Everyone,

Today, I would like to share a T-SQL script to identify the cached plan for a procedure in SQL Server. This script would become very handy to gather the cached plan, especially, when you work to troubleshoot any performance issue related to the procedure.

In most cases, we would not be allowed to run the procedure in the production server and gather the actual execution plan. This script would really help for such scenario to collect the cached plan and further analyse the plan.

SELECT TOP 500 DB_NAME(B.dbid),B.objectid,OBJECT_NAME(B.objectid,B.dbid) ObjectName,usecounts,size_in_bytes,
cacheobjtype,objtype,plan_handle,C.encrypted,query_plan, TEXT
FROM sys.dm_exec_cached_plans A
cross apply sys.dm_exec_query_plan(plan_handle) B
CROSS APPLY sys.dm_exec_sql_text(plan_handle) C
WHERE cacheobjtype ='Compiled Plan' and objtype in ('proc') 
/*
*********************************************************
SEARCH SCENARIOS
****************
/* if you need to search that contains the proc name*/
and text like '%proc_name%'
/* if you need to search for exact object as below*/
and OBJECT_NAME(B.objectid,B.dbid)='proc_name'
*********************************************************
*/
ORDER BY SIZE_IN_BYTES DESC , Usecounts asc

Hope you will see the script useful, please share your thoughts on the same.