Контрольная точка записывает только страницы из зафиксированных транзакций?

Автор: Paul Randal, A SQL Server DBA myth a day: (15/30) checkpoint only writes pages from committed transactions

Контрольная точка записывает только страницы из зафиксированных транзакций

ЛОЖЬ

Этот миф существует целую вечность и связан с непониманием того, как работает общая система журналирования и восстановления. Контрольная точка всегда записывает все страницы, которые были изменены (так называемые «грязные» страницы) с момента последней контрольной точки или с момента считывания страницы с диска. Не имеет значения, зафиксирована транзакция, изменившая страницу, или нет – страница записывается на диск в любом случае. Единственным исключением является tempdb, где страницы данных не записываются на диск в рамках контрольной точки. 

Вот несколько ссылок на посты в блогах с более глубокой информацией:

• Статья в TechNet Magazine: Ведение журнала и восстановление в SQL Server
• Как работают контрольные точки и что записывается в журнал транзакций
• Что делает контрольная точка для tempdb?

Вы можете наблюдать за тем, что делает операция контрольной точки, используя несколько флагов трассировки:

• 3502: записывает в журнал ошибок момент начала и завершения контрольной точки.
• 3504: записывает в журнал ошибок информацию о том, что записывается на диск.

Чтобы использовать эти флаги трассировки, вы должны включить их для всех потоков с помощью DBCC TRACEON (3502, 3504, -1), иначе вы не увидите никакого вывода.

Вот небольшой скрипт, который доказывает, что грязные страницы от незафиксированных транзакций записываются во время контрольной точки. Сначала настройка:

CREATE DATABASE [CheckpointTest];
GO
USE [CheckpointTest];
GO
CREATE TABLE [t1] ([c1] INT IDENTITY, [c2] CHAR (8000) DEFAULT 'a');
CREATE CLUSTERED INDEX [t1c1] on [t1] ([c1]);
GO

SET NOCOUNT ON;
GO

CHECKPOINT;
GO

DBCC TRACEON (3502, 3504, -1);
GO

А теперь незафиксированная транзакция, которая делает грязными 10 МБ страниц, после чего следует контрольная точка:

BEGIN TRAN;
GO
INSERT INTO [t1] DEFAULT VALUES;
GO 1280

CHECKPOINT;
GO

И в журнале ошибок я вижу:

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

Очевидно, что все страницы были записаны, даже несмотря на то, что транзакция не была зафиксирована.