Dear All,

It is possible to remove a single, specific plan from the plan cache. DBCC FREEPROCCACHE optionally accepts a parameter, plan_handle, which uniquely identifies a particular plan. But before you supply a plan_handle, you need to extract one from the plan cache for your query in question. Let us see:

Suppose you run this query:

You get the results. Now, an execution plan for the above query resides in the plan cache. Now if you want to remove the plan from the plan cache, you need to first figure out the plan handle for this plan.

When you run the following code:

You see a hundreds of plans in the cache, each having a plan handle. You need to figure out the plan handle for your query in question. In order to extract the plan handle for your query, you need to filter on the query text. There is another DMV (rather DMF) that gives you the SQL/Query text; sys.dm_exec_sql_text. But this DMF accepts a parameter, either a sql_handle or plan_handle.

There is another DMV (Dynamic Management View), sys.dm_exec_query_stats which has exhaustive statistics related to query execution and also include the plan_handle and sql_handle.

We can now APPLY the DMF to the DMV and extract the plan_handle as follows:

We are using the APPLY operator that was introduced in SQL 2K5, to apply the DMF to every row from the outer query. Also observe that I am applying a filter on the ‘text’ column by putting down a substring from my query. I get the following results:


Now that I have the plan handle for my query, I can use the value in DBCC FREEPROCCACHE statement and remove the plan from the cache. See below:

Please note that your binary value will be different.

After you run this, the plan is removed from the cache. You can check this by re-running the earlier query:

This time, I get only one record as the other one is removed.


Hope you enjoyed reading this.



Rahul Sharma

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook