Using DBCC FREEPROCCACHE to Clear SQL Server Procedure Cache

It is possible to clear out the entire SQL Server procedure cache using DBCC FREEPROCCACHE

The procedure cache is where SQL Server will cache execution plans after they have been compiled. The benefit of this caching is that there is no need for the execution plans to be compiled at run time. This compiling operation typically consumes resource and slows down the execution time of the query.

The obvious disadvantage of clearing out the plan cache is that all execution plans for your SQL Server instance are recompiled upon execution which may slow things down temporarily until the cache is re-populated. Great for development, give thought before executing in production.

You may be under pressure to quickly get performance back to normal but it’s better to laser target the offending queries if you have time and address accordingly. You can clear out the procedure cache for specific queries as we will see further down the post.

Running DBCC FREEPROCCACHE


DBCC FREEPROCCACHE;

…and you should be presented with the following informational message:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

This is optional – WITH NO_INFOMSGS and will simply suppress the informational message above.


DBCC FREEPROCCACHE WITH NO_INFOMSGS;

Upon executing, messages with be written to the SQL Server error log for each cache store in the plan cache.

Here is an example:

SQL Server has encountered 1 occurrence(s) of cachestore flush for the ‘Object Plans’ cachestore (part of plan cache) due to ‘DBCC FREEPROCCACHE’ or ‘DBCC FREESYSTEMCACHE’ operations.

Using DBCC FREEPROCCACHE to clear specific execution plans from the cache

You first need to pinpoint the identifier of the execution plan which you want to clear out of the cache. This is known as a “plan handle” and you can find the plan handles and the cached SQL by issuing a query against sys.dm_exec_cached_plans and sys.dm_exec_sql_text


SELECT cp.plan_handle, st.text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE st.text LIKE N'%ThePlanYouAreLookingFor%'

So here I will clear a plan from the cache, firstly by running this query to get the plan inserted to the plan cache.


SELECT TOP 1 * FROM Person.Person;

Now I will attempt to find the plan_handle for the execution plan.


SELECT cp.plan_handle, st.text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE st.text LIKE '%Person.Person%'

dbcc freeproccache clear sql server plan cache

So I can see that I want to clear out the cache for the second row as displayed in the screenshot.

So add the plan_handle and say bye to that query plan. 🙂 (The plan handle has been deliberately shortened in the code below otherwise my CMS has trouble displaying it 🙂 )


DBCC FREEPROCCACHE (0x060006002FE61B1D40FDAFF501000000010000....);

It's only fair to share...Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInPin on PinterestBuffer this pageShare on RedditShare on StumbleUpon
About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on

Speak Your Mind

*


*