SQL Server 記憶體髒頁寫入磁碟三大機制對比：Lazy Writer、Checkpoint、Eager Writer

前言

SQL Server 採用「先改記憶體、再寫磁碟」策略，被修改但未寫入磁碟的頁面稱為「髒頁」。同步髒頁至磁碟的三大機制——Lazy Writer (惰性寫入器)、Checkpoint (檢查點)、Eager Writer (積極寫入器)——觸發時機、行為特徵與效能影響迥異。本文逐一解析並對比。

1. Lazy Writer (惰性寫入器)

核心職責

1. 管理空閒緩衝區：定期檢查空閒列表大小，不足時掃描緩衝池，依 LRU 算法淘汰長期未用頁面；若為髒頁則先寫入磁碟再標記為空閒。
2. 協調 Windows 與 SQL Server 記憶體：
   • OS 物理記憶體緊張 → 從空閒列表釋放頁面歸還 OS。
   • SQL Server 負載高、未達 Max Server Memory → 擴大空閒列表以適應負載。

特點：背景非同步、批次處理、不影響用戶查詢回應。

2. Checkpoint (檢查點)

核心職責

建立一個「所有已提交交易的修改均已持久化至磁碟」的時間點標記，作為資料庫復原起點。約每分鐘自動觸發一次，確保啟動復原在 1 分鐘內完成。若該間隔寫入記錄 < 10 MB 則不自動觸發。

行為細節

• 將髒頁寫入磁碟，並在頁首標記為「乾淨頁」但不從緩衝池移除 (關鍵差異)。
• 追蹤旗標 3502 可在錯誤日誌記錄檢查點起訖位置。

3. Eager Writer (積極寫入器)

觸發場景

大量資料載入操作：BCP、SELECT INTO、WRITETEXT/UPDATETEXT、BULK INSERT。

行為特徵

• 高優先級邊建立頁面邊寫入資料，不等全部頁面配置完成再寫入。
• 確保緩衝池有足夠空間支撐大批次操作，避免中途因記憶體不足阻塞。

三大機制對比總結

實務啟示

• 觀察 PLE (Page Life Expectancy) 與 Lazy Writes/sec 判斷記憶體壓力。
• 手動 CHECKPOINT 可加速後續資料庫備份/還原。
• 大量匯入時若遇寫入延遲，檢查磁碟子系統能否滿足 Eager Writer 的高優先級 I/O 需求。