SQL Server Checkpoints: Which Type, Why It Matters, and How to Configure It

Why Checkpoints Exist

SQL Server changes pages in the buffer pool and writes the change to the transaction log first (write-ahead logging). The data page itself stays in memory as a dirty page and is written to disk later. That is efficient: a page that is changed 500 times is written once, not 500 times.

The price is paid after a crash. Everything that was committed but only exists in dirty pages has to be replayed from the log during the redo phase of recovery. A checkpoint writes dirty pages to disk and records how far the data files are up to date. Redo then starts at that point instead of at the beginning of the active log. A checkpoint therefore controls three things:

• Recovery time after a crash, restart or failover: the less log since the last checkpoint and the fewer dirty pages, the shorter the redo.
• The write pattern on your storage: a few large bursts or a continuous stream.
• Log truncation: in SIMPLE recovery the log can only be reused up to the last checkpoint. In FULL recovery a log backup is required as well, but the log still cannot be truncated past the start of the last checkpoint.

The Four Checkpoint Types

The important rule: a database uses indirect checkpoints as soon as its TARGET_RECOVERY_TIME is greater than 0. For that database the server option recovery interval is ignored. Only databases with a target recovery time of 0 still use automatic checkpoints.

What a Fresh Instance Uses

SELECT name, target_recovery_time_in_seconds, recovery_model_desc
FROM   sys.databases;

The catch is not new databases. It is old ones: a database created on SQL Server 2014 or earlier keeps TARGET_RECOVERY_TIME = 0 through every upgrade, restore and migration. Many production instances run a mix of both types without anyone having decided it.

Automatic vs. Indirect, Measured

Same workload, same instance, same time window. Top: dirty pages per database. Bottom: instance-level page writes by checkpoint (automatic) and by the background writer (indirect), capped at 4,000 pages per second so the indirect line stays visible.

Finding 1: Same Volume, Completely Different Pattern

Both databases wrote almost exactly the same number of pages: 98,174 against 99,364. Indirect checkpoint does not save I/O. What it changes is when the I/O happens. The automatic checkpoint wrote everything in three bursts, one of them over 30,000 pages (about 236 MB) inside a single one-second sample, and was completely silent in between. The background writer for the indirect database was active in 211 of 219 seconds and never exceeded 2,148 pages per second.

On the container's storage, a burst was absorbed by the Linux page cache. On a shared SAN or a busy VM datastore, a burst of that size competes with log writes, and log write latency is commit latency. The one checkpoint in the test that took 24 seconds shows what a burst looks like when the storage cannot swallow it at once. This is the main reason to prefer indirect checkpoints: predictable, flat write I/O instead of a periodic spike.

Finding 2: TRT 60 Does Not Mean Fewer Dirty Pages

A common assumption is that indirect checkpoint keeps the buffer pool "cleaner". At the default of 60 seconds it did not: on average 22,191 dirty pages against 23,648. Indirect checkpoint does not aim for a low number of dirty pages; it aims for an estimated redo time. On fast storage with a small database, 60 seconds of estimated redo allows a lot of dirty pages. The dirty page count only drops if you lower the target, see below.

Finding 3: In SIMPLE Recovery, the Log Needs More Room

The background writer flushes pages, but it does not truncate the log. That still takes a real checkpoint. With indirect checkpoints, real checkpoints were rare, and the log since the last checkpoint grew to 669 MB, twice as much as with automatic checkpoints. In this run the checkpoint came when about two thirds of the 1 GB log were in use. If your SIMPLE databases have tightly sized logs from the old automatic-checkpoint days, switching them to indirect checkpoints can trigger log growth. Size the log with headroom, or watch log_reuse_wait_desc = 'CHECKPOINT' in sys.databases after the change.

Tuning TARGET_RECOVERY_TIME: 15 vs. 60 Seconds

Second run, same workload, 180 seconds. This time both databases used indirect checkpoints, one with 15 seconds and one with 60 seconds:

The lower target did what it promises: half the dirty pages, a quarter of the log since the last checkpoint, so a much shorter redo after a crash. Now the price. The only difference between run 1 and run 2 was that one database changed from automatic checkpoints to TRT 15. The instance write rate went from 817 to 2,027 pages per second, while both databases executed the same number of statements as before (about 110,000 each). With the TRT 60 database writing about the same as in run 1 (around 410 pages per second), the TRT 15 database wrote roughly 1,600 pages per second, about four times as many page writes for the same work.

The reason is the trade-off from the beginning of this article. A hot page that is updated over and over is written once per checkpoint interval when the engine is allowed to keep it dirty. With an aggressive target, the background writer writes it again and again. Shorter recovery is paid for with write amplification, and on flash storage also with wear.

Manual Checkpoints and the Duration Parameter

A manual CHECKPOINT writes all dirty pages of the current database as fast as possible. With a duration parameter it spreads the work across the requested number of seconds:

USE MyDb;
CHECKPOINT;      -- as fast as possible
CHECKPOINT 20;   -- spread over about 20 seconds

Both reach the same result; the second one simply throttles itself to the time you ask for. Two good uses:

• Before a planned AG failover or service restart: run a CHECKPOINT in the large, busy databases a few minutes before. The restart or failover then has less redo to do, and the downtime window shrinks.
• On weak storage: use a duration so the manual checkpoint does not create exactly the burst you wanted to avoid.

There is no reason to schedule manual checkpoints as a regular job. Automatic and indirect checkpoints already do that work.

How to Find and Fix Databases on Automatic Checkpoints

-- Databases still using automatic checkpoints
SELECT name, target_recovery_time_in_seconds, recovery_model_desc, create_date
FROM   sys.databases
WHERE  target_recovery_time_in_seconds = 0
  AND  database_id > 4;   -- master stays on automatic, that is fine

-- Switch one database to indirect checkpoints (online, no restart)
ALTER DATABASE [MyDb] SET TARGET_RECOVERY_TIME = 60 SECONDS;

The same with dbatools across many instances:

Get-DbaDatabase -SqlInstance SQL01, SQL02 -ExcludeSystem |
    Where-Object TargetRecoveryTime -eq 0 |
    Select-Object SqlInstance, Name, TargetRecoveryTime, RecoveryModel

# fix
Get-DbaDatabase -SqlInstance SQL01 -ExcludeSystem |
    Where-Object TargetRecoveryTime -eq 0 |
    ForEach-Object { $_.TargetRecoveryTime = 60; $_.Alter() }

After a migration, Invoke-sqmDatabaseStandardization from sqmSQLTool sets TARGET_RECOVERY_TIME to 60 seconds together with compatibility level, owner and orphaned users in one step.

Leave recovery interval Alone

The server option recovery interval (min) only affects databases with a target recovery time of 0. Raising it to "save I/O" makes automatic checkpoints rarer and therefore larger: bigger bursts and longer recovery. Keep it at 0 and solve the problem by moving the databases to indirect checkpoints.

Monitoring Checkpoints

Both performance counters are cumulative in sys.dm_os_performance_counters; take two samples and divide the difference by the elapsed seconds. You can also count dirty pages per database with sys.dm_os_buffer_descriptors WHERE is_modified = 1, but that view scans the whole buffer pool. On a server with hundreds of GB of memory, run it once for analysis, not every second in a monitoring job.

SELECT d.name,
       d.target_recovery_time_in_seconds AS trt,
       ls.log_since_last_checkpoint_mb,
       d.log_reuse_wait_desc
FROM   sys.databases d
CROSS APPLY sys.dm_db_log_stats(d.database_id) ls
WHERE  d.state_desc = 'ONLINE'
ORDER BY ls.log_since_last_checkpoint_mb DESC;

Recommended Configuration

1. All user databases on indirect checkpoints with TARGET_RECOVERY_TIME = 60 SECONDS. Find the leftovers with 0 and switch them. The change is online and immediate.
2. model at 60, so every new database inherits it. That is the default; check that nobody changed it.
3. recovery interval (min) stays at 0.
4. Log files of SIMPLE databases sized with headroom after the switch, and log_reuse_wait_desc watched for the first days.
5. Lower targets only per database and only with measurements. In the test, TRT 15 halved the dirty pages and roughly quadrupled the page writes.
6. Manual CHECKPOINT before planned failovers and restarts of large, busy databases, with a duration on weak storage.
7. Monitoring for checkpoint bursts via Checkpoint pages/sec: if that counter still spikes regularly, some database is still on automatic checkpoints.

Related reading: SQL Server Recovery Models: SIMPLE, FULL and BULK_LOGGED Compared, Log Truncation vs. Log Shrinking and Server Buffer Pool: Understanding Pages in Memory.

The Bottom Line

Checkpoints do not decide how much SQL Server writes; they decide when. Automatic checkpoints wrote the same 98,000 pages as the indirect ones, but in bursts of up to 30,000 pages per second instead of a steady stream of a few hundred. That is why indirect checkpoints with 60 seconds are the right default, and why old databases still sitting at TARGET_RECOVERY_TIME = 0 are worth a five-minute fix. Going lower than 60 seconds is a real trade-off: shorter recovery, paid for with several times the write volume. Make that trade only where a measured recovery time demands it.