Great minds discuss ideas !


Find cached execution plan

Find cached execution plan

An execution plan, simply put, is the result of the query optimizer’s attempt to calculate the most efficient way to implement the request represented by the T-SQL query you submitted. Execution plans can tell you how a query will be executed, or how a query was executed. Ref:

Execute the below query to find the cached execution plan.

-- Owner:
-- This will give you the list of execution plans cached

PC.plan_handle as [Token for Plan]
,ST.text as [Query in Text Format]
,QP.query_plan [Plan in XML format, click on it to open]
,PC.cacheobjtype as [Chached Plan Type]
,PC.objtype as [Type]
FROM sys.dm_exec_cached_plans PC
CROSS APPLY sys.dm_exec_sql_text(plan_handle) as ST
CROSS APPLY sys.dm_exec_query_plan(PC.plan_handle) QP;
SQL Execution plan

SQL Execution plan

Now if you want to delete a particular execution plan from the cache then you will need copy the token from the result and pass to DBCC FREEPROCCACHE



Before deleting  the plan:

Execution Plan before deleting

Execution Plan before deleting

Then run

DBCC FREEPROCCACHE (0x050004007E7D3C25D042C1777500000001000000000000000000000000000000000000000000000000000000)

And now to see if that got deleted, run the first query again.

After deleting the cache plan

After deleting the cached plan


Leave a Reply