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.
--====================================== -- 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
Before deleting the plan:
DBCC FREEPROCCACHE (0x050004007E7D3C25D042C1777500000001000000000000000000000000000000000000000000000000000000)
And now to see if that got deleted, run the first query again.
Leave a Reply