What the heck is a checkpoint, and why should I care?
summary
Explains what a checkpoint actually does: it flushes transaction log records to disk, writes dirty pages back, updates the database boot page with a checkpoint LSN, and bounds crash-recovery work. Covers write-ahead logging, the roles of lazy/eager writers, and how recovery model/log backups affect VLF reuse. Demos show using 3504 trace flag and SQL Query Stress to correlate checkpoints with IO spikes, and changing indirect checkpoint target time (default 60s, more frequent smaller writes or less frequent larger spikes).
AI-written summary · about
chapters (5)
- 0:00Start
- 12:57Write Ahead
- 36:53Automatic "Recovery Interval" default zero • What does this value mean? How reliable is this setting? • When do you change default? Changing to over 60 min require "OVERRIDE"
- 38:26Manual Transact-SQL CHECKPOINT 'checkpoint_duration' seconds to complete • Not guaranteed • Advanced option When do you use a Manual Checkpoint? When do you use 'checkpoint duration'?
- 42:21Checkpoint tempdb
related talks
51mWhat the heck is a checkpoint, and why should I care?- 51mWhat the heck is a checkpoint, and why should I care?
44mUnderstanding Write-Ahead Logging (WAL) in PostgreSQL
48mA Deep Dive Into Database Internals
52mACID in SQL Server: what it is, how it works, and how to live more adventurously.- 52mACID in SQL Server: what it is, how it works, and how to live more adventurously.
54mSoaring to New Heights with Accelerated Database Recovery
26mThe Wonderful World of WAL