What are the caches that DBCC FREESYSTEMCACHE frees?

  • john.danley

    SSC Eights!

    Points: 846

    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?

  • Jonathan Kehayias

    One Orange Chip

    Points: 26667

    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.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • john.danley

    SSC Eights!

    Points: 846

    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: 2369

    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

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

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