Checkpoint operations

 

Factors Affecting the Duration of Checkpoint Operations

 

In general, the amount time required for a checkpoint operation increases with the number of dirty pages that the operation must write. By default, to minimize the performance impact on other applications, SQL Server adjusts the frequency of writes that a checkpoint operation performs. Decreasing the write frequency increases the time the checkpoint operation requires to complete. SQL Server uses this strategy for a manual checkpoint unless a checkpoint_duration value is specified in the CHECKPOINT command.

The performance impact of using checkpoint_duration depends on the number of dirty pages, the activity on the system, and the actual duration specified. For example, if the checkpoint would normally complete in 120 seconds, specifying a checkpoint_duration of 45 seconds causes SQL Server to devote more resources to the checkpoint than would be assigned by default. In contrast, specifying a checkpoint_duration of 180 seconds would cause SQL Server to assign fewer resources than would be assigned by default. In general, a short checkpoint_duration will increase the resources devoted to the checkpoint, while a long checkpoint_duration will reduce the resources devoted to the checkpoint. SQL Server always completes a checkpoint if possible, and the CHECKPOINT statement returns immediately when a checkpoint completes. Therefore, in some cases, a checkpoint may complete sooner than the specified duration or may run longer than the specified duration.

 

Checkpoint

For performance reasons, the Database Engine performs modifications to database pages in memory—in the buffer cache—and does not write these pages to disk after every change. Rather, the Database Engine periodically issues a checkpoint on each database. A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk and, also, records information about the transaction log.

The Database Engine supports several types of checkpoints: automatic, indirect, manual, and internal. The following table summarizes the types of checkpoints


Automatic: EXEC sp_configure 'recovery interval','seconds'
Description: Issued automatically in the background to meet the upper time limit suggested by the recovery interval server configuration option. Automatic checkpoints run to completion. Automatic checkpoints are throttled based on the number of outstanding writes and whether the Database Engine detects an increase in write latency above 20 milliseconds.


Indirect: ALTER DATABASE … SET TARGET_RECOVERY_TIME =target_recovery_time{ SECONDS | MINUTES }
Description: Issued in the background to meet a user-specified target recovery time for a given database. The default target recovery time is 0, which causes automatic checkpoint heuristics to be used on the database. If you have used ALTER DATABASE to set TARGET_RECOVERY_TIME to >0, this value is used, rather than the recovery interval specified for the server instance.


Manual: CHECKPOINT [ checkpoint_duration ]
Description: Issued when you execute a Transact-SQL CHECKPOINT command. The manual checkpoint occurs in the current database for your connection. By default, manual checkpoints run to completion. Throttling works the same way as for automatic checkpoints. Optionally, the checkpoint_durationparameter specifies a requested amount of time, in seconds, for the checkpoint to complete.


Internal: None
Description: Issued by various server operations such as backup and database-snapshot creation to guarantee that disk images match the current state of the log.