You can use a data page or an index page only if
it exists in memory. Therefore, a buffer in the data cache must be available
for the page to be read into.
Keeping a supply of buffers available for immediate
use is an important performance optimization. If a buffer isn’t readily
available, many memory pages might have to be searched simply to locate a
buffer to free up for use as a workspace.
In SQL Server 2008, a single mechanism is
responsible both for writing changed pages to disk and for marking as free
those pages that have not been referenced for some time. SQL Server maintains a
linked list of the addresses of free pages, and any worker needing a buffer
page uses the fi rst page of this list.
Every buffer in the data
cache has a header that contains information about the last two times the page
was referenced and some status information, including whether the page is dirty
(that is, it has been changed since it was read into disk). The reference information
is used to implement the page replacement policy for the data cache pages,
which uses an algorithm called LRU-K. An LRU-K
algorithm keeps track of the last K times a page was referenced
and can differentiate between types of pages, such as index and data pages,
with different levels of frequency. It can actually simulate the effect of
assigning pages to different buffer pools of specifi cally tuned sizes. SQL
Server 2008 uses a K value of 2, so it keeps track of the two most
recent accesses of each buffer page.
The data cache is periodically scanned from the
start to the end. Because the buffer cache is all in memory, these scans are
quick and require no I/O. During the scan, a value is associated with each
buffer based on its usage history. When the value gets low enough, the dirty
page indicator is checked. If the page is dirty, a write is scheduled to write
the modifications to disk. Instances of SQL Server use a write-ahead log so the
write of the dirty data page is blocked while the log page recording the
modification is fi rst written to disk. After the modified page has been flushed to
disk, or if the page was not dirty to start with, the page is freed. The
association between the buffer page and the data page that it contains is
removed by deleting information about the buffer from the hash table, and the
buffer is put on the free list.
