Checkpoint

CHECKPOINT : As per MSDN/BOL , 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 because 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 changes made. Checkpoint is the SQL engine system process that writes all dirty pages to disk for the current database. The benefit of the Checkpoint process is to minimize time during a later recovery by creating a point where all dirty pages have been written to disk.

When CHECKPOINT happen?

• A CHECKPOINT statement is explicitly executed. A checkpoint occurs in the current database for the connection.
  
• A minimally logged operation is performed in the database; for example, a bulk-copy operation is performed on a database that is using the Bulk-Logged recovery model.
  
• Database files have been added or removed by using ALTER DATABASE.
  
• An instance of SQL Server periodically generates automatic checkpoints in each database to reduce the time that the instance would take to recover the database.
  
• A database backup is taken. 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 service 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.
  
• An activity requiring a database shutdown is performed. For example, AUTO_CLOSE is ON and the last user connection to the database is closed, or a database option change is made that requires a restart of the database.
  
• Long-running uncommitted transactions increase recovery time for all types of checkpoints.
  

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

We can monitor checkpoint I/O activity using Performance Monitor by looking at the “Checkpoint pages/sec” counter in the SQL Server:Buffer Manager object.