What are the Different Types of SQL Server CHECKPOINT? – Interview Question of the Week #230

Question: What are the Different Types of SQL Server CHECKPOINT?

Answer: There are four different kinds of checkpoints in SQL Server.

CHECKPOINT is the process where the SQL Server engine takes the in-memory dirty pages (the pages which are modified from their original value) and writes them back from memory to disk.

4 Types of SQL Server CHECKPOINT

Automatic: This is automatically executed in background when SQL Server meets certain conditions related to the recovery interval specified the server configurations. You can specify the recovery interval at the instance level for SQL Server. Automatic Checkpoints are throttled if the database engine detects write latency of above 50 milliseconds.

Here is the script to configure to Automatic CHECKPOINT value to 1 min.

EXEC sp_configure'recovery interval','1'

Indirect: This is executed in background when any SQL Server database meets certain conditions related to the recovery time interval specified in the database configuration. You can specify the recovery interval at the database level for SQL Server. As of SQL Server 2016, this is by default enabled for your database and the value is 1 minute.

Here is the script to configure Indirect CHECKPOINT value to 60 seconds.

ALTER DATABASE CURRENT
SET TARGET_RECOVERY_TIME = 60 SECONDS

Manual: This is executed when the user executes CHECKPOINT command for any database. User can increase or decrease the performance of this operation if needed with the help of additional parameter checkpoint_duration.

Here is the script to run manual CHECKPOINT.

CHECKPOINT

Internal: This is executed by SQL Server automatically when it completes certain operations like backups or shutdown or managing isolations to make sure that disk contains the same database as the log.

There is no script to run this internal operation.

Here are few additional blog posts which are related to this topic and one should read them to understand how CHECKPOINTs works.

• SQL SERVER – Increasing Speed of CHECKPOINT and Best Practices
• Dirty Pages – How to List Dirty Pages From Memory in SQL Server?
• What is Clean Buffer in DBCC DROPCLEANBUFFERS?
• SQL SERVER – Impact of CHECKPOINT and DBCC DROPCLEANBUFFERS on Memory.

Reference: Pinal Dave (https://blog.sqlauthority.com)