Skip to main content
Our Tech Ideas

SQL Server Checkpoint Demystification

Introduction

There are numerous background operations that protect data integrity and enhance performance in the world of SQL Server. SQL Server Checkpoint is one example of such a procedure. This article examines the importance of the Checkpoint process, its variations, and how it helps shorten recovery times and enhance I/O performance.

Understanding the Checkpoint Process

All changed or “dirty” pages in SQL Server must be written to disc using the Checkpoint process. Together with the transaction log, it extracts information about modifications and updates the corresponding pages in the data file. By preserving changes made by both committed and uncommitted transactions, this makes sure the database maintains consistency.

Checkpoint Evolution

There was just one checkpoint procedure available in SQL Server 2000 and older versions. However, the idea of multiple checkpoints was introduced with SQL Server 2005, allowing for improved performance and scalability.

Importance of Checkpoints

There are two primary reasons why checkpoints are crucial in SQL Server:

  • Reducing Crash Recovery Time:
    When a server restarts unexpectedly, a significant number of dirty pages in the buffer can lead to an extended recovery process. The checkpoint process helps mitigate this by writing the modified pages to disk, thereby reducing the time required to make the database consistent.
  • Optimizing I/O Performance:
    Checkpoints facilitate batching of I/O operations to disk, which can result in improved performance. By grouping multiple page writes together, the checkpoint process minimizes disk I/O overhead, leading to faster data operations.

Checkpoint Truncation in SIMPLE Recovery Model

If a database in SQL Server uses a SIMPLE recovery architecture, checkpoints have an extra purpose. After the modifications have been written to disc, they truncate the transaction log file to make room. This helps maintain an ideal log size and ensures that the log file does not continue to increase.

Scenarios Triggering Checkpoints

Checkpoints occur in various scenarios, including:

  • Periodically, based on the recovery interval configuration of the SQL Server instance.
  • Before the start of a database backup.
  • During server startup.
  • When the transaction log file reaches 70% full in log truncate mode.
  • After adding a new data or transaction log file to the database.
  • Manually triggered using the CHECKPOINT command.
  • When a database is detached.
  • When changing the recovery model from FULL or BULK_LOGGED to SIMPLE.
  • When a database snapshot is generated.
  • After a bulk operation in the bulk-logged recovery model.

Types of Checkpoints

There are different types of checkpoints in SQL Server:

Automatic Checkpoints

  • These checkpoints run automatically based on the recovery interval configuration.
  • The recovery interval property determines the frequency of automatic checkpoints.
  • It can be checked using the “sp_configure ‘recovery interval (min)'” command. Ref. https://learn.microsoft.com

Manual Checkpoints

  • Checkpoints can be manually forced using the CHECKPOINT command.
  • Manual checkpoints only run for the current database.

Indirect Checkpoints (introduced in SQL Server 2012)

  • These checkpoints are issued in the background to meet a user-specified target recovery time.
  • Indirect checkpoints take precedence over automatic checkpoints.

Internal Checkpoints

  • Internal checkpoints run before starting certain activities.
  • They occur before database backups, server shutdowns, changing recovery models, log file size reaching 70%, adding new data/log files, generating database snapshots, and performing minimally logged operations.

Understanding the Checkpoint Process

The checkpoint process follows a series of steps to ensure data consistency:

  1. Logs the start of the checkpoint in the transaction log file.
  2. Logs checkpoint-related information if necessary.
  3. Writes all dirty pages from memory to disk.
  4. Records the checkpoint’s LSN (Log Sequence Number) in the boot page of the database.
  5. Attempts to clear the log in the SIMPLE recovery model.
  6. Logs the end of the checkpoint in the transaction log file.

Verifying Checkpoint Details

To determine when a checkpoint occurred and view the latest checkpoint details, the following methods can be used:

  • Using the DBCC LOG(‘DbName’, 3) command to check the transaction log. When you execute this statement, it will return information about the transaction log for the specified database, including details such as the log records, log sequence numbers (LSNs), transaction IDs, and other related information.
DBCC LOG('AdventureWorks2022', 3)
  • Executing the query to retrieve the latest checkpoint details. This query retrieves the most recent checkpoint information from the transaction log by selecting the top 1 record where the operation is either the beginning or end of a checkpoint.
SELECT TOP 1 f1.[Checkpoint Begin], f2.[Checkpoint End]
FROM fn_dblog(NULL, NULL) f1
INNER JOIN fn_dblog(NULL, NULL) f2
    ON f1.[Current LSN] = f2.[Previous LSN]
WHERE f2.Operation IN (N'LOP_BEGIN_CKPT', N'LOP_END_CKPT')
ORDER BY 1 DESC
Query to retrieve the latest checkpoint details

Step-by-Step Demo

The blog post includes a step-by-step demonstration to provide hands-on experience with the checkpoint process. It covers creating a sample table, inserting data, checking for clean and dirty pages, forcing a checkpoint, and verifying checkpoint details in the transaction log file.

  • Step 1: Creating the TestDB database
  • Step 2: Creating a sample table in the TestDB database
  • Step 3: Inserting sample data into the table
  • Step 4: Checking for clean and dirty pages of the TestDB database. This code retrieves information about clean and dirty pages in the TestDB database using the sys.dm_os_buffer_descriptors view. The result will include the page status (clean or dirty), the database name, and the count of pages for each status.
  • Step 5: Force a checkpoint
  • Step 6: Check for pages using the previous script to verify there are no dirty pages
  • Step 6: Check checkpoint details in the transaction log file
-- Step 1: Creating the TestDB database
CREATE DATABASE TestDB;
GO

-- Step 2: Creating a sample table in the TestDB database
USE TestDB;
GO

-- Create a table named "MyTable" with two columns: "a" of type INT and "b" of type VARCHAR(40)
CREATE TABLE MyTable(a INT, b VARCHAR(40));
GO

-- Step 3: Inserting sample data into the table
-- Insert a row with values 1 for column "a" and 'xyz' for column "b" into the "MyTable" table
INSERT INTO MyTable VALUES(1, 'xyz');

-- Step 4: Checking for clean and dirty pages of the TestDB database
USE TestDB;
GO

-- Retrieve information about clean and dirty pages in the TestDB database
SELECT
    Page_Status = CASE WHEN is_modified = 1 THEN 'Dirty' ELSE 'Clean' END,
    DBName = CASE WHEN database_id = 32767 THEN 'RESOURCEDB' ELSE DB_NAME(database_id) END,
    Pages = COUNT(1)
FROM sys.dm_os_buffer_descriptors
WHERE database_id = DB_ID()
GROUP BY database_id, is_modified
ORDER BY 2;
-- Step 5: Force a checkpoint
USE TestDB;
GO

-- Force a checkpoint in the TestDB database
CHECKPOINT;

-- Step 6: Check for pages using the previous script to verify there are no dirty pages
USE TestDB;
GO

-- Retrieve information about clean and dirty pages in the TestDB database
SELECT
    Page_Status = CASE WHEN is_modified = 1 THEN 'Dirty' ELSE 'Clean' END,
    DBName = CASE WHEN database_id = 32767 THEN 'RESOURCEDB' ELSE DB_NAME(database_id) END,
    Pages = COUNT(1)
FROM sys.dm_os_buffer_descriptors
WHERE database_id = DB_ID()
GROUP BY database_id, is_modified
ORDER BY 2;


-- Step 6: Check checkpoint details in the transaction log file
SELECT *
FROM fn_dblog(NULL, NULL)
WHERE operation LIKE '%ckpt%';

Conclusion:
The SQL Server Checkpoint process plays a vital role in ensuring database consistency and optimizing performance. By persisting modifications to disk, reducing crash recovery time, and batching I/O operations, checkpoints contribute to the overall stability and efficiency of SQL Server environments. Understanding the checkpoint process and its various types empowers database administrators to effectively manage and optimize their databases.