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);
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.
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?
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
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.