SQL Server Checkpoint 機制詳解：復原間隔與刷新機制

Checkpoint 是 SQL Server 將記憶體中「髒頁」 (已修改未寫入磁碟) 刷新至資料檔的過程，並在記錄檔留下標記，代表此點前所有異動已持久化。

Checkpoint 觸發時機

1. 正常關閉服務 (SHUTDOWN / 服務停止)
2. 復原間隔：預估從記錄檔復原所需時間超過 Recovery Interval (預設 1 分鐘，實際約 1 分鐘內)
3. 記憶體壓力：可用記憶體不足，需釋放緩衝區
4. 手動執行：CHECKPOINT 指令
5. 記錄檔使用率 > 70% (簡單復原模式下)
6. 資料庫備份/建立快照/ALTER DATABASE 新增檔案 等作業

Recovery Interval 設定

-- 查看目前值 (秒)
EXEC sp_configure 'recovery interval';

-- 設定為 5 分鐘 (300 秒)
EXEC sp_configure 'recovery interval', 300;
RECONFIGURE;

• 值越大 → 髒頁留在記憶體越久 → Checkpoint 頻率降低 → 寫入 IO 減少 → 故障復原時間變長。
• 值越小 → Checkpoint 較頻繁 → 復原較快 → 但增加背景寫入負載。
• 一般建議保持預設或略微調大 (如 5-10 分鐘)，配合間接檢查點 (Indirect Checkpoint, SQL 2012+) 針對個別資料庫設定目標復原時間 (Target Recovery Time)。

間接檢查點

SQL Server 2012+ 引入資料庫層級 TARGET_RECOVERY_TIME，取代執行個體層級 recovery interval，提供更精細控制：

ALTER DATABASE YourDB SET TARGET_RECOVERY_TIME = 60 SECONDS;