SQL Server: What is a COLD, DIRTY or CLEAN Buffer?
Sent: Tuesday, March 17, 2009 2:05 AM
Subject: what is clean buffer? what is cold buffer cache?
I got these terms from DBCC DROPCLEANBUFFERS , please guide me.
From: Robert Dorr
Sent: Tuesday, March 17, 2009 9:38 AM
Subject: RE: what is clean buffer? what is cold buffer cache?
A clean buffer is a data page in memory that is NOT MODIFIED. Modified buffers that have not been written to disk are termed DIRTY buffers and are typically written to disk by checkpoint processing. DBCC DROPCLEANBUFFERS removes the UNMODIFIED buffers from the buffer pool (no longer hashed) and places them on the free list. This is often used in basic performance testing to clear the data cache and force the data to be retrieved from disk as a physical read instead of a logic read.
The cold buffer cache has a couple of variants within SQL Server. Typically it refers to the buffer pool right after restart. The data cache is not loaded (cold) and requires physical reads to populate the cache. This is often termed the RAMPUP phase. Internally it can also refer to the free list activity. SQL Server understands the time of last access to buffers and when appropriate takes this into account when handling the free buffer lists. Buffers on the free list that have some age to them may be termed cold because the age increases the chance that they accessible with a near memory transfer from a location such as CPU cache.
Bob Dorr - Principal SQL Server Escalation Engineer