Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

Comments

Posted by Steve Jones on 29 December 2009

That's great knowledge in terms of removing a specific plan. I'd be curious to know how this would work on a busy system and if you've tried it. I would tend to agree that compilations should be quick, but I wonder if you'd have any contention on the cached plans as multiple users might try to recompile all at once, perhaps even duplicating efforts and causing two plans for a single query/parameter combination.

Posted by Glenn Berry on 30 December 2009

I actually have a monitoring tool that I wrote for our operations staff that displays the results of a bunch of different DMV queries. That tool allows them (or me) to run DBCC FREEPROCCACHE on any SQL instance by clicking on a button.

We have about 1100 stored procedures in our largest, busiest OLTP database. Running DBCC FREEPROCCACHE causes a nearly unnoticeable blip for a a couple of seconds. THe recompile overhead is very small.

Posted by Knight on 31 December 2009

Excellent info and this debugs the myth that you shouldn't do this type of thing. Personally I've worked on very busy servers and I put a proc cache clear down in my overnight maintenance routine as standard with no ill effect.

Posted by TheSQLGuru on 31 December 2009

Good stuff there Glenn!

Posted by smalik on 31 December 2009

Thanks Glenn for sharing so useful information.

Posted by SanjayAttray on 31 December 2009

Thanks Glen.

Interesting. Never thought of why we cannot flush a particular plan before.  Always executed DBCC FREEPROCCACHE to flush everything.  After executing this I always felt that the first execution of any procedure took more time than normal.

But, this is interesting. I'll definitely try next time in maintenance window.

Posted by Jason Rowland on 31 December 2009

When I try removing a specific plan I get the following error:

Msg 2583, Level 16, State 3, Line 1

An incorrect number of parameters was given to the DBCC statement.

Posted by Jason Edward Rowland on 31 December 2009

I just tried it in 2008 and it worked fine. I was trying to do this in 2005 when I got the error message.

Posted by sbarber-1129865 on 4 January 2010

After I run DBCC FLUSHPROCINDB  on a db. Lets say I want to advoid the users seeing the slight slowdowns on first runs of the stored procedures. Would I advoid that by just recompling the stored procedures after FLUSHPROCINDB or would there be additional steps that I would need to take.

Thanks

Posted by belal360 on 9 January 2010

Dropping a specific plan I found very useful; however this option is NOT available in Sql 2005! Anyhow same could be accomplished in 2005 ? Thanks

Posted by Mike Good on 26 January 2010

We have some poorly defined error conditions that occur sporadically where this is the best solution we've found (another alternative that works is to bounce SQL).  Symptoms include general unresponsiveness and high CPU.  

I'm wondering...if you took the time to research this, was this the case for you too?  And if so, has anything made the problem go away (service pack, cumulative update, hotfix, specific practices)?

Posted by Glenn Berry on 27 January 2010

belal360: You are correct, you can only drop a specific plan with DBCC in SQL Server 2008. I would try using sp_recompile in SQL 2005.

Posted by Glenn Berry on 27 January 2010

Mike Good: Are you running on SQL Server 2005 or 2008 ?  Early builds of SQL 2005 had a major issue with the TokenUserPermStore cache that could cause what you are seeing.  It is also possible that you maight have a "bad" query plan in the cache (maybe caused by parameter sniffing) that is causing very high CPU. DBCC FREEPROCCACHE may fix that temporarily, but you ned to look for the root cause.

Posted by forsqlserver on 11 May 2011

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.

Thanks for the article...

May I know How Total Worker Time and Total Logical reads effect in total?

Posted by bajabero on 12 September 2011

I got this error:

An incorrect number of parameters was given to the DBCC statement.

here're the steps I followed:

1)

SELECT plan_handle

FROM sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE text LIKE N'select * from mqprdailyattendence000 where payrollcalid = ''2011Jul::acc'' '

It gave me this value:

0x0600070090C86C39B881260C000000000000000000000000

I copied it and pasted it here as it is:

DBCC FREEPROCCACHE(0x0600070090C86C39B881260C000000000000000000000000)

Please help me :)

Posted by bajabero on 12 September 2011

I think I've got it!

It was SQL 2005!

According to this link:

msdn.microsoft.com/.../ms174283(v=SQL.90).aspx

FREEPROCCACHE does not take any parameters in SQL Server 2005

Thank you Jason Edward Rowland :-)

Posted by ananda.murugesan on 14 September 2012

which condition needs to fire DBCC Freeproccache? in OLTP production system.

Posted by ananda.murugesan on 14 September 2012

which condition needs to fire DBCC Freeproccache? in OLTP production system.

Leave a Comment

Please register or log in to leave a comment.