SQL Server Checkpoint: Top 5 Best Usage

This article covers all information about SQL Server Checkpoints, such as what Checkpoints are, the types of Checkpoints, the advantages of Checkpoints, how we can run the Checkpoint manually, and the applications and best practices of SQL Server Checkpoints.

Introduction

Microsoft SQL Server Checkpoint is an important database feature that improves performance, helps in optimization, and increases efficiency and data consistency. In the event of unforeseen failures, it expedites database recovery.

A Glimpse into History

The Checkpoint method was implemented to increase database robustness. SQL Server has improved checkpoint processes to decrease downtime and increase system recovery effectiveness.

What is the SQL Server Checkpoint?

In Microsoft SQL Server, a checkpoint is a process that writes dirty pages (that is, pages that have been altered in memory) from the buffer cache to disk. It guarantees that the database engine won’t have to handle too many transactions during recovery.

Important attributes of SQL Server checkpoints include:

Saves changed pages from memory to the database file by writing dirty pages to disk.
Cuts down on the time needed for recovery by minimizing the number of transactions that must be performed.
Both Automatically and Manually Triggered: SQL Server conducts checkpoints regularly but can also be carried out manually.
Enhances Performance: Maintaining the data file up to date lessens the impact of unexpected system failures.

Why Do We Need the SQL Server Checkpoint?

A Checkpoint in SQL Server is crucial for database performance, recovery, and consistency. It ensures that changes made to the database are safely written to disk, minimizing data loss in case of system failure. Below are the key reasons why checkpoints are essential:

1. It Reduces Database Recovery Time

SQL Server must replay a long list of transaction log records without checkpoints during recovery.
Checkpoints ensure that only the most recent transactions must be recovered, reducing downtime.

2. It Ensures Data Durability

SQL Server follows the Write-Ahead Logging (WAL) protocol, meaning changes are first recorded in the transaction log before being applied to the data files.
A checkpoint flushes these changes from memory to disk, making them permanent.

3. It Frees Up Log Space of a database

The transaction log grows as transactions occur.
Once a checkpoint occurs, portions of the log no longer needed for recovery can be truncated, preventing excessive log file growth.

4. It Improves the Performance of the System

Regular checkpoints ensure the system is not overloaded with too many dirty pages in memory.
By writing them to disk in batches, SQL Server reduces sudden bursts of disk I/O during system shutdown or failure.

5. It Supports Database Consistency

Ensures that committed transactions are safely written to disk while leaving uncommitted transactions available for rollback.
Prevents database corruption by maintaining an up-to-date data file.

6. It Helps During Database Backup

SQL Server triggers an internal checkpoint during backups to ensure all committed changes are written to disk before the backup process starts.

Types of SQL Server Checkpoint

Below are the types of checkpoints in SQL Server

Automatic Checkpoint

Started automatically according to the SQL Server recovery interval.
The default value for triggering an Automatic Checkpoint in SQL Server is based on the Recovery Interval setting, which is 1 minute by default.
The Recovery Interval can be modified using the following command.

EXEC sp_configure 'recovery interval', 5; -- Sets the recovery interval to 5 minutes
RECONFIGURE;

Manual Checkpoint

Using the CHECKPOINT command, this was started deliberately.

When to Use a Manual Checkpoint?

Before taking a database backup, ensure all changes are written to disk.
To control when a checkpoint occurs instead of waiting for an Automatic Checkpoint.
To improve performance by reducing the number of dirty memory pages.

Syntax:

--To run checkpoint manually

CHECKPOINT;

Example:

USE AdventureWorks2022;  -- Switch to the user database
CHECKPOINT;  -- trigger a checkpoint in user database manually

Indirect Checkpoint

More control over checkpoint behaviour is possible with an indirect checkpoint that may be configured per database.

An Indirect Checkpoint in SQL Server is a database-level configurable checkpoint introduced in SQL Server 2012.

Key Features of Indirect Checkpoints:

Provides predictable recovery time after a system failure.
Configurable per database using the TARGET_RECOVERY_TIME option.
Ensures that dirty pages are written to disk proactively rather than waiting for automatic checkpoint triggers.

Syntax:

-- Sets recovery time of a database to 60 seconds

ALTER DATABASE <Your Database Name>
SET TARGET_RECOVERY_TIME = 60 SECONDS;

Example:

Check Current Checkpoint Mode

SELECT name AS [Database Name], 
target_recovery_time_in_seconds AS [Check Current Checkpoint Mode]
FROM sys.databases
WHERE name = 'AdventureWorks2022';

Set Indirect Checkpoint with 30 Seconds Recovery Time

ALTER DATABASE [AdventureWorks2022]
SET TARGET_RECOVERY_TIME = 30 SECONDS;

When to Use Indirect Checkpoints?

For high-transaction databases to maintain a predictable recovery time.
In OLTP workloads, fast recovery is crucial.
When the default automatic checkpoint does not provide efficient data flushing.

Internal Checkpoint

It occurs during database activities such as log truncation, backup, and restore.

When Does an Internal Checkpoint Occur?

Internal checkpoints are triggered during the following:

Database Backup – Ensure all committed data is written to disk before a backup starts.

Database Shutdown – When SQL Server shuts down, an internal checkpoint ensures that all transactions are saved.

Database File Operations – When adding, modifying, or removing database files.

Transactional Replication – To ensure data consistency in replicated databases.

Log Truncation – To free up space in the transaction log file after a log backup.

Required Permission to Run SQL Server Checkpoint

The user must have the sysadmin or db_owner role.

How to Run Checkpoint in SQL Server?

To manually trigger a checkpoint, use the following command:

USE AdventureWorks2022; 
CHECKPOINT;

What Happens in the Background When We Run SQL Server Checkpoint?

Identifies all dirty pages in the buffer cache.
Writes those pages to disk.
Updates the transaction log with checkpoint information.
Notifies the SQL Server database engine that pages have been persisted.

Conclusion

SQL Server Checkpoint helps us minimize database recovery time and guarantees consistency. SQL Server Checkpoint is a crucial feature. DBAs have control over data flushing to disk with a manual checkpoint, which enhances performance and ensures durability before crucial tasks like maintenance or backups.