Post reply

Cache

  • February 4, 2008 at 11:35 pm

    #182457

    Could any tell me the exact difference between cleaning cache and refreshing cache from SQL server?

    if there are differences then how exactly we clean or refresh cache? I am thinking DBCC commands

    Thanks in advance

  • February 4, 2008 at 11:44 pm

    #774995

    I'm not sure what you mean by refreshing cache.

    DBCC DROPCLEANBUFFERS will clear all unmodified pages out of the data cache.

    DBCC FREEPROCCACHE will remove all entries from the stored procedure cache

    DBCC FREESYSTEMCACHE(CacheName) can be used to clear out other caches.

    Naturally, none of these should be run on a production server without a very good reason.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 5, 2008 at 3:32 am

    #775028

    Hey,

    You have two cache types, which are data and procedure. SQL Server automatically manages the cache clean-up or refresh by probing each buffer via the lazy writer process or aging the execution plans and deallocating if execution plan cost factor is 0 and not being referenced by a connection. This basically means, you shouldn't have to do to much in terms of interacting with the cache.

    Here are some commands for interacting with cache:

    1. CHECKPOINT = Flush dirty pages from cache to disk

    2. DBCC DROPCLEANBUFFERS = Release all data pages from cache

    3. DBCC BUFFER = Print all buffer hearders and pages from cache

    4. DBCC PROCCACHE = Display contents of stored procedure cache

    5. DBCC FREEPROCCACHE = Flush stored procedure cache and cause re-compilation on re-execute

    6. DBCC MEMORYSTATUS = View memory usage.

    Hope this is what you require.

    Thanks,

    Phillip Cox

  • July 11, 2008 at 5:12 am

    #841350

    Hi Phillip

    If I ran a query in QA and wanted to clear out any caching before executing the query again, would 5) DBCC FREEPROCCACHE also apply in that case or is really just for stored procedures ?

    Cheers

    Preet

  • July 11, 2008 at 5:39 am

    #841370

    Freeproccache clears out all cached plans, for both stored procs and adhoc queries.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 14, 2008 at 2:09 pm

    #842677

    Thanks Gail

Viewing 6 posts - 1 through 5 (of 5 total)

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