SQL Server CHECKPOINT 機制詳解：從緩衝區到資料檔的持久化流程

核心概念

CHECKPOINT（檢查點） 是 SQL Server 將記憶體緩衝區（Buffer Pool）中「髒頁」——即已修改但未寫入磁碟的資料頁——強制刷新至資料檔（.mdf/.ndf）的關鍵機制。它是資料庫恢復模型、備份效能、關機速度的核心影響因子。

為什麼需要 CHECKPOINT？

• WAL（預寫日誌）協議：交易提交時只寫入記憶體中的日誌緩衝區與資料頁，不立即寫資料檔
• 效能考量：隨機 IO 寫資料檔極慢，合併批次寫入大幅提升吞吐量
• 恢復時間目標（RTO）：Checkpoint 頻率直接決定資料庫啟動/還原時的 REDO 階段耗時

語法與參數

CHECKPOINT [ checkpoint_duration ]
-- checkpoint_duration：int，>0，單位秒。指定嘗試完成檢查點的目標持續時間。
-- 省略時：引擎自動調整持續時間，平衡 IO 衝擊與恢復時間

自動觸發 CHECKPOINT 的四大場景

關鍵設定：恢復間隔

-- 檢視目前設定（預設 0 = 自動，約 1 分鐘）
EXEC sp_configure 'recovery interval';

-- 設定為 2 分鐘（需進階選項）
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'recovery interval', 2; RECONFIGURE;

• 值越小 → Checkpoint 越頻繁 → 恢復越快 → 平時 IO 壓力越大
• 值越大 → Checkpoint 越稀疏 → 恢復越慢 → 平時 IO 壓力越小
• 生產環境建議：1~5 分鐘，視 RTO 需求與 IO 子系統能力調整

手動執行與監控

-- 手動觸發當前資料庫檢查點
CHECKPOINT;

-- 監控檢查點相關效能計數器
SELECT * FROM sys.dm_os_performance_counters 
WHERE counter_name LIKE '%checkpoint%';

間接檢查點（Indirect Checkpoint，SQL 2012+）

針對特定資料庫設定目標恢復時間（TARGET_RECOVERY_TIME），引擎動態調整 Checkpoint 頻率以滿足 SLA，比實例層級 recovery interval 更精準：

ALTER DATABASE MyDB SET TARGET_RECOVERY_TIME = 30 SECONDS;

最佳實務建議

1. 不要手動頻繁執行 CHECKPOINT，信任自動機制
2. 監控 Log Flushes/sec、Checkpoint pages/sec 觀察 IO 模式
3. 大量載入資料時（BULK INSERT、bcp、SELECT INTO），使用簡單恢復模式或最小日誌模式減少 Checkpoint 壓力
4. 關機維護窗口前，可手動 CHECKPOINT 加速後續啟動