SQL SERVER – Checkpoint

Checkpoint is the process that writes all dirty pages to disk for the current database. Checkpoint process help to minimize time during a later recovery by creating a point where all dirty pages have been written to disk.

• Checkpoints can be performed concurrently on the databases
• If a checkpoint fails or is interrupted and a recover is required, the database engine can not recover from the “failure” checkpoint, is necessary to recover from the last successful checkpoint.
• The database engine performs any modification to database pages in memory (for performance reasons) it cause dirty pages, those dirty pages are not written to disk on each modification performed, those are written to disk just when a checkpoint occur.
• When checkpoints occur?
• Before a backup, the database engine performs a checkpoint, in order that all the changes to database pages (dirty pages) are contained in the backup.
• Stopping the server using any of the following methods, they it cause a checkpoint.
• Using Shutdown statement,
• Stopping SQL Server through SQL Server configuration, SSMS, net stop mssqlserver and ControlPanel-> Services -> SQL Server Service.
• When the “SHUTDOWN WITH NOWAIT” is used, it does not execute checkpoint on the database.
• When the recovery internal server configuration is accomplished. This is when the active portion of logs exceeds the size that the server could recover in amount of time defined on the server configuration (recovery internal).
• When the transaction log is 70% full and the database is in truncation mode.
• The database is in truncation mode, when is in simple recovery model and after a backup statement has been executed.

• The time required to perform a checkpoint depends directly of the amount of dirty pages that the checkpoint must write.

[Questions & Answers]

Q: What is Dirty Page?.

A: Dirty pages are the data pages that exists on the buffer cache and have been modified, but not yet written to disk.

Q: What is the checkpoint syntax?

A:

Checkpoint [duration_time]

Duration_time:

• Time desired to perform the checkpoint process.
• Is specified in seconds.
• Must be greater than 0.
• When is omitted, the database engine adjust automatically the duration time to minimize the performance impact.
• Depending of the duration defined, is the amount of resources that SQL Servers assign to checkpoint process. Ex. If the duration_time is defined in 10 seconds but the checkpoints normally is going to take 20 seconds, SQL Server assigns more resources with performance impact in order to accomplish the 10 seconds defined. In the other hand, if the checkpoint is going to take 5 seconds, SQL Server assigns fewer resources than would be assigned by default. Checkpoint process usually takes more/less time than the specified.