What the heck is a checkpoint, and why should I care?
summary
Explains exactly what a SQL Server checkpoint does: writes dirty pages after flushing log records so crash recovery can redo/undo from the LSN/boot page marker, and how that affects IO and recovery time. Demos use SQL Query Stress and traceflags (3605, 3502, 3504) to show checkpoints beginning/ending and per-checkpoint dirty-page counts. Notes chunk size change from 256 KB pre-2016 to 1 MB in 2016+, differences between automatic, indirect (default since 2016) and manual checkpoints, and practical tuning (example: changing indirect target from 60s to 15s) to smooth IO 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?
53mSQL Server Internals: The Practical Angle
52mACID in SQL Server: what it is, how it works, and how to live more adventurously.
50mDebugging SQL Server- 52mACID in SQL Server: what it is, how it works, and how to live more adventurously.
57mYour Performance Tuning Check List
57mDurability in Postgres