Find cached execution plan

169 views 19:04 0 Comments 21 November 2019

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.

SQL
-- T-SQL Query to retrieve details of cached query plans
-- Author: SOYELUDDIN BISWAS
SELECT
    PC.plan_handle AS [Token for Plan],
    ST.text AS [Query in Text Format],
    QP.query_plan AS [Plan in XML format, click on it to open],
    PC.cacheobjtype AS [Cached 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) AS QP;
SQL
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

SQL
-- Remove the specific plan from the cache.
DBCC FREEPROCCACHE
(0x0500040036CD403B402ADA965702000001000000000000000000000000000000000000000000000000000000);
GO
SQL

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

Leave a Reply

Your email address will not be published. Required fields are marked *