Fun With DBCC FREEPROCCACHE

Nearly anytime you see the command DBCC FREEPROCCACHE mentioned in a blog post, magazine article or book, you get some sort of a scary warning about how you should not use it on a production system, or else life as we know it will end. For example, Books Online says this:

Use DBCC FREEPROCCACHE to clear the plan cache carefully. Freeing the plan cache causes, for example, a stored procedure to be recompiled instead of reused from the cache. This can cause a sudden, temporary decrease in query performance. For each cleared cachestore in the plan cache, the SQL Server error log will contain the following informational message: "SQL Server has encountered %d occurrence(s) of cachestore flush for the ‘%s’ cachestore (part of plan cache) due to ‘DBCC FREEPROCCACHE’ or ‘DBCC FREESYSTEMCACHE’ operations." This message is logged every five minutes as long as the cache is flushed within that time interval.

I would argue that running DBCC FREEPROCCACHE does not cause that much distress, even on a very busy OLTP system. It will cause a pretty minor CPU spike for a few seconds on most systems as the plans get recompiled. It can actually be pretty useful for resetting the cached_time time for sys.dm_exec_procedure_stats so that it is the same for most of the stored procedures in your normal workload. That makes it easier to pick out your most expensive queries or stored procedures on a cumulative basis when you are looking at things like total worker time or total logical reads.

Having said all that, I want to show a few methods for clearing all or part of the procedure cache that are somewhat less impactful on the system. Running DBCC FREEPROCCACHE is kind of a brute force approach, so if you are concerned about that, you can run one of the variations shown below:

-- Example 1 (Sledgehammer)
-- Remove all elements from the plan cache for the entire instance 
DBCC FREEPROCCACHE;

-- Flush the cache and suppress the regular completion message
-- "DBCC execution completed. If DBCC printed error messages, contact your system administrator." 
DBCC FREEPROCCACHE WITH NO_INFOMSGS;


-- Example 2 (Ballpeen hammer)
-- Remove all elements from the plan cache for one database  
-- Get DBID from one database name first
DECLARE @intDBID INT;
SET @intDBID = (SELECT [dbid] 
                FROM master.dbo.sysdatabases 
                WHERE name = 'AdventureWorks');

-- Flush the procedure cache for one database only
DBCC FLUSHPROCINDB (@intDBID);


-- Example 3 (Scalpel)
-- Remove one plan from the cache
-- Get the plan handle for a cached plan
SELECT cp.plan_handle, st.[text]
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE [text] LIKE N'%/* GetOnlineSearchResultsMonday %';

-- Remove the specific plan from the cache using the plan handle
DBCC FREEPROCCACHE (0x05000800F7BA926C40C15055070000000000000000000000);

This entry was posted in SQL Server 2008. Bookmark the permalink.

4 Responses to Fun With DBCC FREEPROCCACHE

  1. Paul says:

    Thanks for this. I have a system (64 bit, SQL 2005 on a virtual server) where I have to run the DBCC FREEPROCCACHE every 15 mins or the server falls over with memory errors. I cannot get the apps re-written as they are bought-in. At least I can use this to show people that the server is not taking the hit that they think it is.

  2. Stuart says:

    I am trying to apply this the SQL 2005 – but it seems FREEPROCCACHE takes no parameters in SQL 2005 – is there a way in SQL 2005 to achieve a PlanHandle clean up?

  3. Joyce says:

    To add a comment, sign in with your Windows Live ID (if you use Hotmail, Messenger, or Xbox LIVE, you have a Windows Live ID). Sign inDon\’t have a Windows Live ID? Sign up

  4. Jeff Moden says:

    Hi Glenn,

    First, great article. Thanks for taking the time to post it.

    Going back to Stuart’s post of more than 4 years ago, is there any way to clear a single plan from cache in 2005 that you know of? I’ve been Yabingooglehooing for an answer and there doesn’t seem to be one but thought I’d ask.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s