Working with Stored Procedure and Plan Handle

Working with Stored Procedure and Plan Handle

Table of contents

No heading

No headings in the article.

To view cache plan of AdHoc queries :

SELECT cplan.plan_handle, cplan.usecounts, cplan.objtype, qtext.text
,qplan.query_plan FROM sys.dm_exec_cached_plans AS cplan
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qtext
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qplan
where qtext.text like '%SELECT TOP (60)%'
ORDER BY cplan.usecounts DESC

To view cache plan of SP :

select cp.plan_handle from sys.dm_exec_cached_plans cp CROSS APPLY 
sys.dm_exec_sql_text(cp.plan_handle) st where 
OBJECT_NAME(st.objectid, st.dbid) = 'spName'

Sample Plan Hande Id :


To delete plan handle :

DBCC FREEPROCCACHE (plan_handle_id)

To **drop **procedure :

drop procedure spName

To **view ** stored procedure's text :

sp_helptext spName
  • Note : On query analyzer, Ctrl+T regular view, Ctrl+D grid view