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

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.

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s