何謂 Dirty Page

SQL SERVER的IO，是以Page為單位。我們知道SQL SERVER在處理AP傳來的SQL命令時，會把需要用到的page從disk file中載入到一塊記憶體所配置的空間（稱為 buffer cache），所有的異動都將在buffer cache中完成。

dirty page是指在buffer cache中已被修改過的頁面，這些頁面由於尚未寫入disk file中，此時頁面在buffer cach跟disk file中的內容是不同的；所以形象的將此頁面稱為dirty。
dirty page它並不指是uncommitted的頁面。
PS：
容易混淆的一點是所謂「dirty read」，是否可以dirty read並不是指是否允許讀dirty page，而是指是否允許讀uncommitted的資料。有人會將uncommitted page稱為 dirty page，這是一個誤解。

我們知道當checkpoint發生時，會將所有的dirty page寫入disk file；即使是uncommitted的頁面。
底下測試，我們開啟一個交易去update資料，但不做 commit，在未commit前，手動執行一次checkpoint，將dirty page 寫入disk file。觀察dirty page的變化，便可得知uncommitted的頁面，是否仍算是dirty page。

以下列指令檢查目前DB中，有沒有任何的 dirty page
SELECT

database_name = d.name,
OBJECT_NAME =
CASE au.TYPE
WHEN 1 THEN o1.name
WHEN 2 THEN o2.name
WHEN 3 THEN o1.name
END,
OBJECT_ID =
CASE au.TYPE
WHEN 1 THEN p1.OBJECT_ID
WHEN 2 THEN p2.OBJECT_ID
WHEN 3 THEN p1.OBJECT_ID
END,
index_id =
CASE au.TYPE
WHEN 1 THEN p1.index_id
WHEN 2 THEN p2.index_id
WHEN 3 THEN p1.index_id
END,
bd.FILE_ID,
bd.page_id,
bd.page_type,
bd.page_level
FROM sys.dm_os_buffer_descriptors bd
INNER JOIN sys.databases d
ON bd.database_id = d.database_id
INNER JOIN sys.allocation_units au
ON bd.allocation_unit_id = au.allocation_unit_id
LEFT JOIN sys.partitions p1
ON au.container_id = p1.hobt_id
LEFT JOIN sys.partitions p2
ON au.container_id = p2.partition_id
LEFT JOIN sys.objects o1
ON p1.OBJECT_ID = o1.OBJECT_ID
LEFT JOIN sys.objects o2
ON p2.OBJECT_ID = o2.OBJECT_ID
WHERE is_modified = 1
AND d.name = 'DirtyPagesDB'
AND
(
o1.name = 't1'
OR o2.name = 't1'
);

目前DB中，沒有任何的 dirty page

先暫停自動checkoint，然後update Table某筆資料

再檢查DB是否有 dirty page，可以看到已經有一頁dirty page了

接著不做commit，手動做checkpoint，接著再查，如下圖，執行完checkpoint後，已經沒有dirty page了

如果這時我再做rollback，那麼buffer cache的資料會被改回原來樣子，跟disk file的頁面資料又變得不一致，它又會變成dirty page了。

Dirty Pages:
Data that has been modified and Stored in the buffer cache and has not yet been written in to the hard disk.