Flushing Dirty Page To Disk

SQL Server utilizes a technique that write modified data page to the disk asynchronously. There are multiple ways to initiate this process; lazy writing, eager writing and checkpoint.

SQL server stores data in 8 KB pages. When user submits a query asking for data, SQL Server locates the data and retrieves the data pages that contain those information. The process involves retrieving the data pages from the disk into the buffer cache (memory), and returns the result to the user. If user submits a update query on these data, SQL server makes modification on the corresponding data pages in the buffer cache, without updated the data pages on the disk immediately. These modified pages on memory that have not written disk are called dirty pages. Just a quick note, transaction logs are generated during this data page modification but we will be focusing mainly on data page for this blog post.

The dirty pages are written to disk at a later time. There are mechanisms that initiate this process.

- Lazy Writing
- Eager Writing
- Checkpoint

For quick description, lazy writer is a system process that inspects and removes infrequently used pages in memory with the goal of keeping free buffer (memory) available. After the dirty pages are written to the disk, they are then removed from the memory.

There is another system process called eager writer. This process writes dirty pages into disk during non-logged operations (e.g. minimally logged bulk insert) and allows update operation not having to wait for its completion before dirty pages could be written to disk.

As for Checkpoint, SQL server periodically issues a checkpoint to scan the buffer cache and write dirty pages into the disk (It also writes the log in the cache to the disk). There are four different types of checkpoints

- Automatic
- Indirect
- Manual
- Internal

Automatic checkpoint is issued to write the dirty page to the disk as it tries to meet the upper limit of recovery interval set in server configuration option (sp_configure 'recovery interval'). In addition of trying to meet recovery interval, automatic checkpoint is also generated when the log becomes 70% full when the database is set as SIMPLE recovery model. The time interval of automatic checkpoint depends on the amount of log and its last checkpoint.

SQL Server 2012 introduces a new TARGET_RECOVERY_TIME setting on database level. When TARGET_RECOVERY_TIME is set more than the default 0, SQL Server issues indirect checkpoint trying to meet this user specified recovery time for the corresponding database. The idea is, the fewer the dirty pages, the less transaction logs have to be processed (roll forward) during recovery.

T-SQL CHECKPOINT command can be executed to manually issue a checkpoint.

The last type of checkpoint is issued internally by various server operations e.g. shutting down SQL server, some ALTER DATABASE command, database backup, database-snapshot creation and etc. For example, change database to READ_ONLY mode initiates internal checkpoint as shown in the DBCC LOG result below,

ALTER DATABASE TEST
SET READ_ONLY;