(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.)
My DBA Mythbusters session went *really* well at SQL Connections yesterday, and now I'm half way through my debunking month!
Myth #15: checkpoint only writes pages from committed transactions.
This myth has persisted for *ages* and is linked to a misunderstanding of how the overall logging and recovery system works. A checkpoint always writes out all pages that have changed (known as being marked dirty) since the last checkpoint, or since the page was read in from disk. It doesn't matter whether the transaction that changed a page has committed or not – the page is written to disk regardless. The only exception is for tempdb, where data pages are not written to disk as part of a checkpoint. Here are some blog post links with deeper information:
TechNet Magazine article on Understanding Logging and Recovery in SQL Server
Blog post: How do checkpoints work and what gets logged
Blog post: What does checkpoint do for tempdb?
You can watch what a checkpoint operation is doing using a few traceflags:
3502: writes to the error log when a checkpoint starts and finishes
3504: writes to the error log information about what is written to disk
To use these traceflags, you must enable them for all threads using DBCC TRACEON (3502, 3504, -1) otherwise you won't see any output.
Here's a quick script that proves that dirty pages from uncommitted transactions are written out during a checkpoint. First the setup:
CREATE DATABASE CheckpointTest;
CREATE TABLE t1 (c1 INT IDENTITY, c2 CHAR (8000) DEFAULT 'a');
CREATE CLUSTERED INDEX t1c1 on t1 (c1);
SET NOCOUNT ON;
DBCC TRACEON (3502, 3504, -1);
And now an uncommitted transaction that causes 10MB of pages to be dirtied, followed by a checkpoint:
INSERT INTO t1 DEFAULT VALUES;
And in the error log we see:
2010-04-15 13:31:25.09 spid52 DBCC TRACEON 3502, server process ID (SPID) 52. This is an informational message only; no user action is required.
2010-04-15 13:31:25.09 spid52 DBCC TRACEON 3504, server process ID (SPID) 52. This is an informational message only; no user action is required.
2010-04-15 13:31:25.09 spid52 Ckpt dbid 8 started (0)
2010-04-15 13:31:25.09 spid52 About to log Checkpoint begin.
2010-04-15 13:31:25.09 spid52 Ckpt dbid 8 phase 1 ended (0)
2010-04-15 13:31:25.71 spid52 FlushCache: cleaned up 1297 bufs with 50 writes in 625 ms (avoided 0 new dirty bufs)
2010-04-15 13:31:25.71 spid52 average throughput: 16.21 MB/sec, I/O saturation: 70
2010-04-15 13:31:25.71 spid52 last target outstanding: 2
2010-04-15 13:31:25.71 spid52 About to log Checkpoint end.
2010-04-15 13:31:25.71 spid52 Ckpt dbid 8 complete
Clearly all the pages were written out, even though the transaction had not committed.