In Microsoft® SQL Server™ version 7.0, one system is responsible for:
SQL Server 7.0 has a singly-linked list containing the addresses of free buffer pages. Any thread needing a buffer page uses the first page in the free buffer list.
The buffer cache is an in-memory structure. Each buffer page has a header that contains a reference counter and an indicator of whether the page is dirty, which means the page contains modifications that have not yet been written to disk. The reference counter is incremented by 1 each time an SQL statement references the buffer page. The buffer cache is periodically scanned from the start to the end. Because the buffer cache is all in memory, these scans are very quick and require no I/O. During the scan, the reference counter in each buffer page header is divided by 4 and the remainder discarded. When the reference counter goes to 0, the dirty page indicator is checked. If the page is dirty, a write is scheduled to write the modifications to disk. SQL Server uses a write-ahead log, so the write of the dirty data page is blocked while the log page recording the modification is first 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 it contains is removed and the buffer is placed on the free list.
Using this process, frequently referenced pages remain in memory while buffers holding pages that are not referenced eventually return to the free buffer list. The size of the free buffer list is determined internally by SQL Server, based on the size of the buffer cache. The size cannot be configured.
When SQL Server is running on Microsoft Windows NT®, the work of scanning the buffer, writing dirty pages, and populating the free buffer list is mostly done by the individual worker threads. The worker threads perform their scans in the interval of time after they have scheduled an asynchronous read and the read completes. A thread gets the address of the next section of the buffer pool that needs to be scanned from a central data structure, then scans that section of the buffer pool while the read I/O processes asynchronously. If a write must be performed, it is also scheduled asynchronously and does not interfere with the thread’s ability to process the completion of its own read.
There is a separate lazywriter thread that also scans through the buffer cache. The lazywriter process sleeps for an interval of time. When it is restarted, it checks the size of the free buffer list. If the free buffer list is below a certain point (dependent on the size of the cache) the lazywriter process scans the buffer cache to reclaim unused pages and write dirty pages that have a reference count of 0. On Windows NT, most of the work populating the free buffer list and writing dirty pages is done by the individual threads and the lazywriter thread typically finds little to do. Microsoft Windows® 95/98 does not support asynchronous writes, so the lazywriter thread does the work of populating the free buffer list and writing dirty pages.
The checkpoint process also periodically scans the buffer cache for dirty pages and writes to disk any buffer page that remains marked as dirty through two checkpoints. The difference is that the checkpoint process does not place the buffer page back on the free list. The work of the checkpoint process is intended to minimize the number of dirty pages in memory to reduce the length of a recovery if the server fails, not to populate the free buffer list. Checkpoints typically find few dirty pages to write to disk because most dirty pages get written to disk by the worker threads or lazywriter thread in the period between two checkpoints.
Writes of log records are usually scheduled asynchronously by a logwriter thread. The exceptions are when: