Difference between FreeProcCache and FreeSystemCache

All we need is an easy explanation of the problem, so here it is.

Currently I run DBCC FREEPROCCACHE when I want to wipe information out of the buffer pool between running SQL queries. However, I was reviewing this Technet article referencing DBCC FREESYSTEMCACHE. What caches does FREESYSTEMCACHE wipe that FREEPROCCACHE doesn’t?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

What caches does FREESYSTEMCACHE wipe that FREEPROCCACHE doesn’t?

There are many system caches available in SQL Server. I am referring to SQL 2008R2 (as I tested on it).

Below query will return all the caches available :

--- ONLY for Educational purpose. Don't attempt to run on PROD !!

select 'DBCC freesystemcache ('+''''+name+''''+')'  from   sys.dm_os_memory_clerks group by name
-- 155 rows

so for e.g. you want to clear all caches you will run

DBCC FREESYSTEMCACHE ('ALL')

for specific caches to be cleared you can run

DBCC FREESYSTEMCACHE ('userdatabase') -- cleans cache for specific user database
DBCC FREESYSTEMCACHE ('tempdb')  -- clears cache for tempdb
DBCC FREESYSTEMCACHE ('Temporary Tables & Table Variables') -- clears all the temp table and variables
.. and many more (see above 155 rows returned from sql server 2008R2)

Using FREEPROCCACHE, you wont be able to clear all above mentioned at that detailed level.

FREEPROCCACHE ==> from BOL

Removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool.

So essentially, you might think of it as only related to Plan cache with the ability to remove specific plans from the cache with the plan handle and or pool name.

Fun With DBCC FREEPROCCACHE is a blog from Glenn Berry

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply