CHECKPOINT Duration: Can You Speed Up a Manual CHECKPOINT?

The CHECKPOINT duration is a target time in seconds. It does not make a checkpoint faster on a quiet server. A manual CHECKPOINT accepts an optional number. A test on a database with 130 MB of dirty pages shows what that number does, and what it costs.

Build 130 MB of Dirty Pages

A checkpoint writes the changed pages of a database to its data file. The demo needs many of them. The script below creates a database, fills a table with 300,000 rows and keeps the changed pages in memory. A new database writes them in the background, because indirect checkpoints use a 60 second target. The script sets the target to 0, so the pages wait for your CHECKPOINT.

USE master;
GO
IF DB_ID(N'CheckpointTimingDemo') IS NULL CREATE DATABASE CheckpointTimingDemo;
GO
ALTER DATABASE CheckpointTimingDemo SET RECOVERY SIMPLE;
ALTER DATABASE CheckpointTimingDemo SET TARGET_RECOVERY_TIME = 0 SECONDS;
GO
USE CheckpointTimingDemo;
GO
DROP TABLE IF EXISTS dbo.Seeds;
CREATE TABLE dbo.Seeds (SeedID int IDENTITY(1,1) PRIMARY KEY, Variety char(400) NOT NULL, Packets int NOT NULL);
INSERT INTO dbo.Seeds (Variety, Packets)
SELECT TOP (300000) 'Basil', 1 FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;
CHECKPOINT;

Time Three Kinds of CHECKPOINT

The next script changes every row, counts the dirty pages, and times one checkpoint. It does that for a plain CHECKPOINT, for CHECKPOINT 1 and for CHECKPOINT 30, and it repeats the round once. The loop is bounded: two rounds of three. Run it on a test server. It takes about a minute, because the 30 second request takes most of it.

SET NOCOUNT ON;
DECLARE @results TABLE (Id int IDENTITY(1,1), Round int, Request varchar(20), DirtyPages int, Ms int);
DECLARE @round int = 1, @mode int, @t datetime2, @dirty int;
WHILE @round <= 2
BEGIN
    SET @mode = 1;
    WHILE @mode <= 3
    BEGIN
        UPDATE dbo.Seeds SET Packets = Packets + 1;
        SELECT @dirty = COUNT(*) FROM sys.dm_os_buffer_descriptors WHERE database_id = DB_ID() AND is_modified = 1;
        SET @t = SYSDATETIME();
        IF @mode = 1 CHECKPOINT;
        IF @mode = 2 CHECKPOINT 1;
        IF @mode = 3 CHECKPOINT 30;
        INSERT @results (Round, Request, DirtyPages, Ms)
        VALUES (@round, CHOOSE(@mode, 'CHECKPOINT', 'CHECKPOINT 1', 'CHECKPOINT 30'), @dirty, DATEDIFF(MILLISECOND, @t, SYSDATETIME()));
        SET @mode += 1;
    END;
    SET @round += 1;
END;
SELECT Round, Request, DirtyPages, Ms FROM @results ORDER BY Id;

These numbers come from one run on a PC with an SSD. Yours will differ, but the pattern holds. The plain checkpoint wrote about 123 MB in under a second. A request for 1 second did not beat it. A request for 30 seconds took 28 and 29 seconds here. Another run took 15 seconds. SQL Server slowed the writes toward the target.

What the Duration Does

The number is a goal. SQL Server paces its writes so the checkpoint ends close to that time. A long goal gives you a gentle checkpoint that takes its time. A short goal can ask for more speed than the disk offers. Once the disk is the limit, it does not help.

A checkpoint with no number adjusts itself to protect the workload. On a busy server with slow storage, it can run long for that reason. The documentation says that a shorter goal then uses more I/O and can finish sooner. This test ran on an idle SSD, so it did not measure that case. A test on your own server must prove it.

A small checkpoint shows the cost of a long goal. With about 20 dirty pages, a plain CHECKPOINT took 4 milliseconds. CHECKPOINT 10 took 6,274 milliseconds, because SQL Server stretched it toward 10 seconds. Other runs of the same test took 3 to 8 seconds. A long goal slows a checkpoint that was already fast.

SET NOCOUNT ON;
DECLARE @t datetime2;
UPDATE dbo.Seeds SET Packets = 9 WHERE SeedID <= 200;
SET @t = SYSDATETIME();
CHECKPOINT;
SELECT N'CHECKPOINT' AS Request, DATEDIFF(MILLISECOND, @t, SYSDATETIME()) AS Ms;
UPDATE dbo.Seeds SET Packets = 10 WHERE SeedID <= 200;
SET @t = SYSDATETIME();
CHECKPOINT 10;
SELECT N'CHECKPOINT 10' AS Request, DATEDIFF(MILLISECOND, @t, SYSDATETIME()) AS Ms;

Spread the Work With a Recovery Target

A better lever than the duration is the recovery target of the database. A target above 0 turns on indirect checkpoints. SQL Server then writes dirty pages steadily, so few of them wait when you run a manual CHECKPOINT. A target of 0 means automatic checkpoints, which write in larger bursts. This query shows the setting for every database.

SELECT name, target_recovery_time_in_seconds
FROM sys.databases
ORDER BY name;

A new database starts at 60 seconds. The demo database is the exception, because the script set it to 0. A database that came from an old server can still sit at 0. Check that one first when a checkpoint writes too much at once.

Does a Backup Need a Checkpoint First?

A common idea is to run CHECKPOINT before a backup to make the backup faster. A backup flushes the dirty pages itself, so you move the same work to an earlier moment. The next test shows it. The backup goes to the NUL device, so it writes no file. That is only for a test. A backup to NUL is never a real backup. The cleanup script removes its history row.

SET NOCOUNT ON;
UPDATE dbo.Seeds SET Packets = Packets + 1;
SELECT COUNT(*) AS DirtyBeforeBackup FROM sys.dm_os_buffer_descriptors WHERE database_id = DB_ID() AND is_modified = 1;
BACKUP DATABASE CheckpointTimingDemo TO DISK = N'NUL' WITH COPY_ONLY;
SELECT COUNT(*) AS DirtyAfterBackup FROM sys.dm_os_buffer_descriptors WHERE database_id = DB_ID() AND is_modified = 1;

The first count shows 15,790 dirty pages in this run. The second count shows none. The backup wrote them out on its own, so the checkpoint ran as part of it.

You could argue that a manual checkpoint before the backup shortens the backup window itself. It can, a little, if the checkpoint runs earlier in a quiet moment. It does not remove the work.

What to Remember

Leave the CHECKPOINT duration alone unless a test on your server shows a gain. A short request does not beat the disk. A long request slows a fast checkpoint, as the 6,274 millisecond result shows.

If checkpoints take too long, look at the write speed of the disk. Look at how many pages change between checkpoints as well. Indirect checkpoints spread the writing over time. When you finish, run the cleanup script.

USE master;
GO
IF DB_ID(N'CheckpointTimingDemo') IS NOT NULL
BEGIN
    ALTER DATABASE CheckpointTimingDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE CheckpointTimingDemo;
END;
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'CheckpointTimingDemo';

A checkpoint duration is not an accelerator, it is a speed limit you set yourself.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.