What are the caches that DBCC FREESYSTEMCACHE frees?

Question

Post reply

What are the caches that DBCC FREESYSTEMCACHE frees?

john.danley

SSC Eights!

Points: 906
More actions
July 14, 2008 at 11:06 am

#381660

So I've read that DBCC FREESYSTEMCACHE(CacheName) can be used to clear out other caches. If I were to use DBCC FREESYSTEMCACHE(ALL) what am I really clearing out. If I ran this at the same time as a disc defrag job would there be repercussions?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

I am pretty sure that you can find the cache stores with the following query:

select distinct name from sys.dm_os_memory_clerks

There are alot of them there, but you can run any of the names as the value for Parameter1 of the FREESYSTEMCACHE command.

john.danley SSC Eights! Points: 906 More actions · Answer 2

Thanks. That reminds me that I really need to become more familiar with all those views.

For the second half of the question. Do you have any idea if DBCC FREESYSTEMCACHE(ALL) would interfere with a disc defrag? To be more specific, could running DBCC FREESYSTEMCACHE(ALL) and performing a disc defrag simultaneously cause torn pages?

chudman SSCrazy Points: 2452 More actions · Answer 3

I recommend that you consider only running using the following parameters instead of 'ALL'.

DBCC FREESYSTEMCACHE('SQL Plans')

DBCC FREESYSTEMCACHE('TokenAndPermUserStore')

SQL Plans will clear your ad-hoc bloat, and unless your server is configured for ad-hoc queries, this will help performance a lot.

If you are running SQL 2005 sp2 or older, then TokenAndPermUserStore will clear out a lot of memory consumed by the security cache for creation of objects within tempdb.

Using ALL will introduce some performance hiccups until the plans that should be used repeatedly are re-cached.

Jeff Bennett

SQL Monkey

Saint Louis, MO