資料庫檢查點 (SQL Server)

文章
05/04/2023

檢查點會建立一個已知的恰當起點，SQL Server 資料庫引擎可以在發生非預期關機或損毀之後的復原期間，從這個點開始套用記錄中包含的變更。

概觀

基於效能的考量，資料庫引擎會在緩衝區快取的記憶體內修改資料庫頁面，但並不會在每次變更之後都將這些頁面寫入磁碟中。而是由資料庫引擎定期在每一個資料庫上發出檢查點。檢查點會將目前記憶體內部已修改的頁面 (稱為「中途分頁」) 和交易記錄資訊從記憶體寫入磁碟，也會在交易記錄中記錄該資訊。

資料庫引擎支援幾種類型的檢查點：自動、間接、手動和內部。下表彙總檢查點的類型。

名稱	Transact-SQL 介面	描述
自動	EXEC sp_configure 'recovery interval', 'seconds'	在背景自動發出，以符合 recovery interval 伺服器組態選項所建議的時間上限。自動檢查點會執行到完成為止。自動檢查點的調節是根據未完成的寫入數目以及資料庫引擎是否偵測到超過 50 毫秒的寫入延遲有增加。如需詳細資訊，請參閱 Configure the recovery interval Server Configuration Option。
間接	ALTER DATABASE ... SET TARGET_RECOVERY_TIME = target_recovery_time { SECONDS \| MINUTES }	在背景發出，以符合使用者對給定資料庫所指定的目標復原時間。從 SQL Server 2016 (13.x) 開始，預設值為 1 分鐘。舊版的預設值為 0，指示資料庫將使用自動檢查點，其頻率取決於伺服器執行個體的復原間隔設定。如需詳細資訊，請參閱變更資料庫的目標復原時間 (SQL Server)。
手動	CHECKPOINT [ checkpoint_duration ]	在您執行 Transact-SQL CHECKPOINT 命令時發出。手動檢查點會發生在連接的目前資料庫中。根據預設，手動檢查點會執行到完成為止。調節的運作方式與自動檢查點相同。 checkpoint_duration 參數可選擇性地指定要求的時間量 (以秒為單位)，好讓檢查點得以完成。如需詳細資訊，請參閱 CHECKPOINT (Transact-SQL)。
內部	無	由各種伺服器作業 (例如備份和資料庫快照集建立) 發出，以保證磁碟映像符合目前的記錄檔狀態。

資料庫管理員可使用 -k SQL Server 進階安裝選項，根據某些類型的檢查點之 I/O 子系統輸送量，來調節檢查點 I/O 行為。 -k 安裝選項會套用到自動檢查點以及任何未調節的手動與內部檢查點。

如果是自動、手動和內部檢查點，只有在最新檢查點之後所做的修改才需要在資料庫復原期間向前復原。這會減少復原資料庫所需的時間。

重要

長時間執行且未認可的交易會讓所有檢查點類型的復原時間增加。

TARGET_RECOVERY_TIME 和 'recovery interval' 選項的互動

下表摘要全伺服器 sp_configure 'recovery interval' 設定與資料庫特定的 ALTER DATABASE ... TARGET_RECOVERY_TIME 設定間的互動。

target_recovery_time	'recovery interval'	使用的檢查點類型
0	0	目標復原間隔為 1 分鐘的自動檢查點。
0	> 0	目標復原間隔是透過使用者定義之 `sp_configure 'recovery interval'` 選項設定來指定的自動檢查點。
> 0	不適用	由 TARGET_RECOVERY_TIME 設定決定目標復原時間 (以秒鐘表示) 的間接檢查點。

自動檢查點

每當記錄檔記錄數目到達資料庫引擎預估它在 recovery interval 伺服器設定選項指定的期間內可以處理的數目時，都會發生自動檢查點。如需詳細資訊，請參閱 Configure the recovery interval Server Configuration Option。

在沒有使用者定義之目標復原時間的每個資料庫中，資料庫引擎都會產生自動檢查點。自動檢查點的頻率取決於 recovery interval 進階伺服器組態選項，該選項會指定給定伺服器執行個體在系統重新啟動期間應該用於復原資料庫的最長時間。資料庫引擎會預估在復原間隔內可以處理的記錄檔記錄數目上限。使用自動檢查點的資料庫到達這個記錄檔數目上限時，資料庫引擎會發出資料庫的檢查點。

自動檢查點之間的時間間隔可能會有很大的變化。具有大量交易工作負載的資料庫所擁有的檢查點會比主要用於唯讀作業的資料庫更頻繁。在簡單復原模式下，如果記錄檔已填滿 70%，則自動檢查點也會排入佇列。

在簡單復原模式下，除非某些因素延遲了記錄截斷，否則自動檢查點會截斷交易記錄的未使用區段。相反地，在完整復原模式和大量記錄復原模式下，一旦建立了記錄備份鏈結，自動檢查點就不會導致記錄截斷。如需詳細資訊，請參閱交易記錄 (SQL Server)。

當系統損壞時，復原給定資料庫所需的時間長度大部分取決於重做損壞時已變更之頁面所需的隨機 I/O 數量。這表示 recovery interval 設定不可靠。自動檢查點無法判斷精確的復原持續時間。此外，當自動檢查點正在進行時，資料的一般 I/O 活動會大幅增加而且無法預測。

復原間隔對復原效能的影響

如果是使用簡短交易的線上交易處理 (OLTP) 系統， recovery interval 是決定復原時間的主要因素。但是，recovery interval 選項並不會影響重做長時間執行之交易所需的時間。如果資料庫包含長時間執行的交易，則復原此資料庫所花費的時間可能要比 recovery interval 設定中指定的時間還長。

例如，如果長時間執行的交易在伺服器執行個體停用之前，花了兩個小時執行更新，則實際的復原花在復原長交易的時間要比 recovery interval 值長得多。如需長時間執行的交易對復原時間之影響的詳細資訊，請參閱交易記錄 (SQL Server)。如需復原流程的詳細資訊，請參閱還原和復原概觀 (SQL Server)。

一般來說，預設值會提供最佳復原效能。但是在以下情況下，變更復原間隔可能會提升效能：

未復原長時間執行的交易時，復原需花費的時間是否通常大幅多於 1 分鐘。
如果您注意到頻繁的檢查點損害了資料庫的效能。

如果您決定增加 recovery interval 設定，我們建議您逐漸少量增加此設定，並評估每次累加對於復原效能的影響。這個方法非常重要，因為當 recovery interval 設定增加時，要多花許多倍的時間才能完成資料庫復原。例如，如果您將 recovery interval 變更為 10 分鐘，則完成復原所花費的時間要比將 recovery interval 設定為 1 分鐘時大約多 10 倍的時間。

間接檢查點

間接檢查點是在 SQL Server 2012 (11.x) 中引進，它會提供替代自動檢查點的可設定資料庫層級。指定 [目標復原時間] 資料庫設定選項可設定此項目。如需詳細資訊，請參閱變更資料庫的目標復原時間 (SQL Server)。萬一系統損壞，間接檢查點可能會提供比自動檢查點更快而且更可以預測的復原時間。

間接檢查點會提供以下優點：

間接檢查點可確定中途分頁的數目，低於特定臨界值，如此即可在目標復原時間內完成資料庫的復原。

相對於使用中途分頁數目的間接檢查點，復原間隔設定選項會使用交易數目來判斷復原時間。在收到大量 DML 作業的資料庫上啟用間接檢查點時，背景寫入器可開始積極排清磁碟的中途緩衝區，以確保執行復原所需的時間，落在資料庫所設的目標復原時間內。這會在某些系統上造成額外的 I/O 活動，如果磁碟子系統的運作超過或接近 I/O 閾值，就會形成效能瓶頸。
間接檢查點可讓您考量 REDO 期間的隨機 I/O 成本來可靠控制資料庫復原時間。如此可讓伺服器執行個體維持在指定資料庫的復原時間上限內 (除非長時間執行的交易造成過多的復原次數)。
間接檢查點會持續在背景中將中途分頁寫入磁碟，以減少與檢查點相關的 I/O 峰值。

但是，設定間接檢查點的資料庫線上交易式工作負載可能會導致效能降低。這是因為，間接檢查點使用的背景寫入器有時會讓伺服器執行個體的寫入負載總量增加。

重要

間接檢查點是在 SQL Server 2016 (13.x) 中建立之新資料庫的預設行為，包括 model 和 tempdb 資料庫。

除非明確地改變成使用間接檢查點，否則已就地升級或從舊版 SQL Server 還原的資料庫會使用先前的自動檢查點行為。

改善間接檢查點延展性

在 SQL Server 2019 (15.x) 之前，您可能會在資料庫產生大量中途分頁 (例如 tempdb) 時遇到沒有產量的排程器錯誤。 SQL Server 2019 (15.x) 引進改善的間接檢查點可擴縮性，有助於避免在具備大量 UPDATE/INSERT 工作負載的資料庫上發生這類錯誤。

內部檢查點

內部檢查點是由各種伺服器元件產生，可保證磁碟映像符合目前的記錄檔狀態。產生內部檢查點是為了回應以下事件：

使用 ALTER DATABASE 加入或移除資料庫檔案。
取得資料庫備份。
針對 DBCC CHECKDB 建立資料庫快照集，不論是明確或內部方式均可。
執行需要關閉資料庫的活動。例如，AUTO_CLOSE 為 ON，且上次使用者與資料庫的連接已經關閉，或是已變更資料庫選項，因此需要重新啟動資料庫。
透過停止 SQL Server (MSSQLSERVER) 服務來停止 SQL Server 執行個體。此動作會導致在 SQL Server 執行個體的每個資料庫中產生檢查點。
讓 SQL Server 容錯移轉叢集執行個體 (FCI) 離線。

分享方式：