How do you find cached plan for a procedure in SQL Server


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') 
/* 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'

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

One thought on “How do you find cached plan for a procedure in SQL Server”

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s