The storage array's graph had a comb in it: every sixty seconds, almost to the second, write IOPS tripled for a few seconds and fell back. The storage team noticed first and filed it as "the SQL box misbehaving again." The database was a write-heavy order system on shared flash, and the spikes were big enough to push other tenants' latency from two milliseconds to eleven for the duration. Nothing in the application ran on a minute schedule. What ran on a minute schedule was the checkpoint: the database had been upgraded from SQL Server 2008 R2 years ago, carried recovery interval defaults the whole way, and was flushing its dirty pages in the classic once-a-minute burst that the old checkpoint model produces.
The fix was two lines of ALTER DATABASE and the comb flattened into a steady, slightly lower average. This piece is what a checkpoint actually does, why the classic model spikes, what indirect checkpoints change, and how to verify the behavior instead of guessing. Applies to SQL Server 2012 through 2022, with the defaults split at 2016 called out where it matters.
What does a checkpoint actually do?
A checkpoint writes dirty pages — data pages modified in the buffer pool but not yet hardened to the data files — out to disk, so that crash recovery has less work to redo from the transaction log. It does not commit anything and it does not truncate anything; it is purely a recovery-time optimization. Your committed transactions are already safe in the log the moment commit returns, which is why a busy system can run for long stretches with dirty pages only in memory. The checkpoint's job is to bound how much log the redo phase would have to replay after a crash, and that bound is what the scheduling is all about. The full picture of the log side of this bargain — why the log grows regardless and what truncates it — is in my transaction log and VLF notes.
Why does the classic model spike every minute?
Because the automatic checkpoint interval is derived from sp_configure's recovery interval, whose default of zero means automatic — and automatic targets roughly one minute of estimated recovery work, so a write-heavy database checkpoints about once a minute and flushes everything dirty at once. The burst is not a bug or a leak. It is the engine meeting its recovery-time contract by doing a minute's worth of deferred writes in one concentrated effort. On a database with a large buffer pool and a high write rate, "a minute's worth of dirty pages" is a lot of I/O in a few seconds, and on shared storage that burst is visible to every neighbor — which is exactly the comb pattern from the opening story. The trigger worth knowing alongside the interval: under simple recovery, a checkpoint is also forced when the log crosses roughly seventy percent full, so log pressure and checkpoint cadence are coupled, not independent.
What do indirect checkpoints change?
Indirect checkpoints replace the periodic big flush with a background writer that spreads the same dirty-page writes continuously, targeting a per-database recovery time you set yourself. Enable them with a target recovery time and the classic interval stops applying to that database:
ALTER DATABASE [Orders] SET TARGET_RECOVERY_TIME = 60 SECONDS;
SELECT name, target_recovery_time_in_seconds
FROM sys.databases
WHERE database_id = DB_ID(N'Orders');The mechanics: the engine tracks dirty pages per checkpoint in a prepared list and a background writer flushes them steadily enough that recovery would stay under the target — same recovery contract, continuous instead of bursty delivery. The default story trips people up: databases created new on SQL Server 2016 and later get indirect checkpoints with a sixty-second target out of the box, but databases upgraded from older versions keep the old behavior — target recovery time of zero, meaning "use the server-wide recovery interval," meaning the classic burst. The orders database from the incident was exactly that: upgraded through three versions, still bursting a decade later. Any estate with upgraded databases almost certainly has some of these, and the query above is the way to find them.
Does the recovery-time trade-off have a cost?
Yes: you are choosing where the I/O lands, not eliminating it. Classic checkpoints concentrate writes into bursts that disks often handle efficiently in the moment but that spike latency for everything sharing the array; indirect checkpoints produce a steadier stream of more random writes that never peaks but never fully rests either. On dedicated fast storage the difference may be invisible; on shared or oversubscribed storage the steady pattern is usually kinder to neighbors and p99 latency alike. The tuning dimension is the target itself: a shorter target means more aggressive continuous writing and faster crash recovery, a longer target relaxes the writer and lets recovery take longer. The honest framing is that crash recovery time is a real SLA — the database is unavailable until redo finishes — and the checkpoint settings are how you pay for it. How those dirty pages accumulate and get scavenged between checkpoints connects directly to the lazywriter and free-list behavior in my buffer pool notes.
How do I see checkpoint behavior and prove the spike?
Two instruments, one for rates and one for events. For rates, the perf counters SQLServer:Buffer Manager\Checkpoint pages/sec and Background writer pages/sec, graphed next to disk write IOPS, make the comb visible as a counter pattern — checkpoint pages near zero most of the time with a sharp tooth every minute is the classic model in one picture. For events, trace flag 3502 writes checkpoint start and end, with duration, into the error log:
DBCC TRACEON(3502, -1); -- log checkpoint start/end to the error log
-- then, in the error log you will see entries like:
-- FlushCache: cleaned up 1823400 bufs with 40211 writes in 91241 ms
DBCC TRACEOFF(3502, -1);The FlushCache line is the smoking gun: nearly two hundred thousand buffers and forty thousand writes flushed in a single checkpoint is a burst with a number on it, and quoting that line to the storage team ends the "the box is misbehaving" conversation with facts. For a box that must burst less even under the classic model, the -k startup parameter throttles checkpoint I/O to a fixed number of megabytes per second — a blunt instrument that slows recovery-aligned flushing and stretches each checkpoint longer, so I reach for indirect checkpoints first and -k only when a database cannot move off the classic model for some reason.
What should I actually configure?
My standing posture is simple: put every user database on indirect checkpoints with an explicit target — sixty seconds is the sensible default — and verify target_recovery_time_in_seconds on every database I inherit, because the upgraded-database zero hides in estates for years. Leave the server-wide recovery interval at automatic unless you have measured crash recovery against a hard SLA, and measure that recovery occasionally by timing a restart, because the estimate and the reality can drift apart as data grows. Watch Checkpoint pages/sec and the error log with 3502 briefly enabled whenever storage latency correlates with a minute boundary. And when autogrowth events coincide with checkpoint bursts — a log growing seventy-percent-full checkpoints under simple recovery — the sizing fix from my file autogrowth and instant initialization notes is the other half of the same hygiene.
Watching checkpoint health with MonPG when SQL Server support lands
The counters worth graphing here are checkpoint pages per second and background writer pages per second next to disk write latency, target recovery time per database as a config fact, and crash recovery duration measured at each restart. A checkpoint comb that appears after years of flat lines is a config drift signal, not a storage failure. MonPG monitors PostgreSQL in production today; SQL Server support is on the roadmap and in active development, and the SQL Server monitoring (coming soon) page carries the honest status. Until it ships, the sys.databases query above in your estate audit and the perf counters in whatever collector you run cover the ground.