How to find cached execution plan and delete if required.
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: https://www.red-gate.com
Execute the below query to find the cached execution plan.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
--====================================== -- Owner: https://www.ourtechideas.com/ --====================================== -- This will give you the list of execution plans cached SELECT 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; |
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
|
1 |
DBCC FREEPROCCACHE |
Example
Before deleting the plan:
Then run
|
1 |
DBCC FREEPROCCACHE (0x050004007E7D3C25D042C1777500000001000000000000000000000000000000000000000000000000000000) |
And now to see if that got deleted, run the first query again.
