SQL Server: Removing a specific plan from the plan cache using DBCC FREEPROCCACHE

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:

USE AdventureWorks
GO

select * from Person.Contact
WHERE LastName = 'Kim'

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:

select * from sys.dm_exec_cached_plans

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:

SELECT qtext.text,qstats.plan_handle
FROM sys.dm_exec_query_stats AS qstats
    CROSS APPLY sys.dm_exec_sql_text(qstats.plan_handle) as qtext
where text like 'select * from Person%';

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:

1_SQL_Server_Removing_a_specific_plan_from_the_plan_cache_using_DBCC_FREEPROCCACHE

   

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:

DBCC FREEPROCCACHE (0x06000900C843C80140A17287000000000000000000000000)

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:

SELECT qtext.text,qstats.plan_handle
FROM sys.dm_exec_query_stats AS qstats
    CROSS APPLY sys.dm_exec_sql_text(qstats.plan_handle) as qtext
where text like 'select * from Person%';

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

2_SQL_Server_Removing_a_specific_plan_from_the_plan_cache_using_DBCC_FREEPROCCACHE

Hope you enjoyed reading this.

 

Regards

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

   

2 Comments on “SQL Server: Removing a specific plan from the plan cache using DBCC FREEPROCCACHE”

  1. Also if you do not specify a specific plan to remove from the cache you will effect the performance of all other queries which can have a significant impact on others.

Leave a Reply

Your email address will not be published.