資料庫檢查點 (SQL Server)Database Checkpoints (SQL Server)

適用於: 是SQL Server是Azure SQL Database否Azure Synapse Analytics (SQL DW)否平行處理資料倉儲APPLIES TO: YesSQL Server YesAzure SQL Database NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

「檢查點」 (Checkpoint) 會建立一個已知的恰當起點, SQL Server Database EngineSQL Server Database Engine 可以從這個點開始套用發生非預期的關機或損毀之後,於復原期間包含在記錄檔中的變更。A checkpoint creates a known good point from which the SQL Server Database EngineSQL Server Database Engine can start applying changes contained in the log during recovery after an unexpected shutdown or crash.

概觀Overview

基於效能的考量,Database EngineDatabase Engine會在緩衝區快取的記憶體內修改資料庫頁面,但並不會在每次變更之後都將這些頁面寫入磁碟中。For performance reasons, the Database EngineDatabase Engine performs modifications to database pages in memory-in the buffer cache-and does not write these pages to disk after every change. 而是由 Database EngineDatabase Engine 定期在每一個資料庫上發出檢查點。Rather, the Database EngineDatabase Engine periodically issues a checkpoint on each database. 「檢查點」 會將目前記憶體內部已修改的頁面 (稱為「中途分頁」 ) 和交易記錄資訊從記憶體寫入至磁碟,也會在交易記錄中記錄該資訊。A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk and, also records the information in the transaction log.

Database EngineDatabase Engine 支援幾種類型的檢查點:自動、間接、手動和內部。The Database EngineDatabase Engine supports several types of checkpoints: automatic, indirect, manual, and internal. 下表彙總 檢查點的類型:The following table summarizes the types of checkpoints:

名稱Name Transact-SQLTransact-SQL 介面Interface 描述Description
自動Automatic EXEC sp_configure ' recovery interval ',' seconds 'EXEC sp_configure ' recovery interval ','seconds' 在背景自動發出,以符合 recovery interval 伺服器組態選項所建議的時間上限。Issued automatically in the background to meet the upper time limit suggested by the recovery interval server configuration option. 自動檢查點會執行到完成為止。Automatic checkpoints run to completion. 自動檢查點的調節是根據未完成的寫入數目以及 Database EngineDatabase Engine 是否偵測到超過 50 毫秒的寫入延遲有增加。Automatic checkpoints are throttled based on the number of outstanding writes and whether the Database EngineDatabase Engine detects an increase in write latency above 50 milliseconds.

如需詳細資訊,請參閱 Configure the recovery interval Server Configuration OptionFor more information, see Configure the recovery interval Server Configuration Option.
間接Indirect ALTER DATABASE ...SET TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUTES }ALTER DATABASE ... SET TARGET_RECOVERY_TIME =target_recovery_time { SECONDS | MINUTES } 在背景發出,以符合使用者對給定資料庫所指定的目標復原時間。Issued in the background to meet a user-specified target recovery time for a given database. SQL Server 2016 (13.x)SQL Server 2016 (13.x)開始,預設值為 1 分鐘。Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x), the default value is 1 minute. 舊版的預設值為 0,指示資料庫將使用自動檢查點,其頻率取決於伺服器執行個體的復原間隔設定。The default is 0 for older versions, which indicates that the database will use automatic checkpoints, whose frequency depends on the recovery interval setting of the server instance.

如需詳細資訊,請參閱 變更資料庫的目標復原時間 (SQL Server)伺服器組態選項。For more information, see Change the Target Recovery Time of a Database (SQL Server).
手動Manual CHECKPOINT [checkpoint_duration]CHECKPOINT [checkpoint_duration] 當您執行 Transact-SQLTransact-SQL CHECKPOINT 命令時發出。Issued when you execute a Transact-SQLTransact-SQL CHECKPOINT command. 手動檢查點會發生在連接的目前資料庫中。The manual checkpoint occurs in the current database for your connection. 根據預設,手動檢查點會執行到完成為止。By default, manual checkpoints run to completion. 調節的運作方式與自動檢查點相同。Throttling works the same way as for automatic checkpoints. checkpoint_duration 參數可選擇性地指定要求的時間量 (以秒為單位),好讓檢查點得以完成。Optionally, the checkpoint_duration parameter specifies a requested amount of time, in seconds, for the checkpoint to complete.

如需詳細資訊,請參閱 CHECKPOINT (Transact-SQL)For more information, see CHECKPOINT (Transact-SQL).
內部Internal 無。None. 由各種伺服器作業 (例如備份和資料庫快照集建立) 發出,以保證磁碟映像符合目前的記錄檔狀態。Issued by various server operations such as backup and database-snapshot creation to guarantee that disk images match the current state of the log.

注意

資料庫管理員可使用 -k SQL ServerSQL Server 進階安裝選項,根據某些檢查點類型的 I/O 子系統輸送量來調節檢查點 I/O 行為。The -k SQL ServerSQL Server advanced setup option enables a database administrator to throttle checkpoint I/O behavior based on the throughput of the I/O subsystem for some types of checkpoints. -k 安裝選項會套用到自動檢查點以及任何未調節的手動與內部檢查點。The -k setup option applies to automatic checkpoints and any otherwise unthrottled manual and internal checkpoints.

如果是自動、手動和內部檢查點,只有在最新檢查點之後所做的修改才需要在資料庫復原期間向前復原。For automatic, manual, and internal checkpoints, only modifications made after the latest checkpoint need to be rolled forward during database recovery. 這會減少復原資料庫所需的時間。This reduces the time required to recover a database.

重要

長時間執行且未認可的交易會讓所有檢查點類型的復原時間增加。Long-running, uncommitted transactions increase recovery time for all checkpoint types.

TARGET_RECOVERY_TIME 和 'recovery interval' 選項的互動Interaction of the TARGET_RECOVERY_TIME and 'recovery interval' Options

下表摘要全伺服器 sp_configure ' recovery interval ' 設定與資料庫特定的 ALTER DATABASE ... TARGET_RECOVERY_TIME 設定間的互動。The following table summarizes the interaction between the server-wide sp_configure ' recovery interval ' setting and the database-specific ALTER DATABASE ... TARGET_RECOVERY_TIME setting.

target_recovery_timeTARGET_RECOVERY_TIME 'recovery interval''recovery interval' 使用的檢查點類型Type of Checkpoint Used
00 00 目標復原間隔為 1 分鐘的自動檢查點。automatic checkpoints whose target recovery interval is 1 minute.
00 >0>0 目標復原間隔是透過使用者定義之 sp_configure 'recovery interval' 選項設定來指定的自動檢查點。Automatic checkpoints whose target recovery interval is specified by the user-defined setting of the sp_configure 'recovery interval' option.
>0>0 不適用。Not applicable. 由 TARGET_RECOVERY_TIME 設定決定目標復原時間 (以秒鐘表示) 的間接檢查點。Indirect checkpoints whose target recovery time is determined by the TARGET_RECOVERY_TIME setting, expressed in seconds.

自動檢查點Automatic checkpoints

每當記錄檔記錄數目到達 Database EngineDatabase Engine 預估它在 recovery interval 伺服器組態選項指定的期間內可以處理的數目時,都會發生自動檢查點。An automatic checkpoint occurs each time the number of log records reaches the number the Database EngineDatabase Engine estimates it can process during the time specified in the recovery interval server configuration option. 如需詳細資訊,請參閱 Configure the recovery interval Server Configuration OptionFor more information, see Configure the recovery interval Server Configuration Option.

在沒有使用者定義之目標復原時間的每個資料庫中, Database EngineDatabase Engine 都會產生自動檢查點。In every database without a user-defined target recovery time, the Database EngineDatabase Engine generates automatic checkpoints. 自動檢查點的頻率取決於 recovery interval 進階伺服器組態選項,該選項會指定給定伺服器執行個體在系統重新啟動期間應該用於復原資料庫的最長時間。The frequency depends on the recovery interval advanced server configuration option, which specifies the maximum time that a given server instance should use to recover a database during a system restart. Database EngineDatabase Engine 會預估它在復原間隔內可以處理的記錄檔記錄數目上限。The Database EngineDatabase Engine estimates the maximum number of log records it can process within the recovery interval. 當使用自動檢查點的資料庫到達這個記錄檔數目上限時, Database EngineDatabase Engine 會發出資料庫的檢查點。When a database using automatic checkpoints reaches this maximum number of log records, the Database EngineDatabase Engine issues an checkpoint on the database.

自動檢查點之間的時間間隔可能會有 很大 的變化。The time interval between automatic checkpoints can be highly variable. 具有大量交易工作負載的資料庫所擁有的檢查點會比主要用於唯讀作業的資料庫更頻繁。A database with a substantial transaction workload will have more frequent checkpoints than a database used primarily for read-only operations. 在簡單復原模式下,如果記錄檔已填滿 70%,則自動檢查點也會排入佇列。Under the simple recovery model, an automatic checkpoint is also queued if the log becomes 70 percent full.

在簡單復原模式下,除非某些因素延遲了記錄截斷,否則自動檢查點會截斷交易記錄的未使用區段。Under the simple recovery model, unless some factor is delaying log truncation, an automatic checkpoint truncates the unused section of the transaction log. 相反地,在完整復原模式和大量記錄復原模式下,一旦建立了記錄備份鏈結,自動檢查點就不會導致記錄截斷。By contrast, under the full and bulk-logged recovery models, once a log backup chain has been established, automatic checkpoints do not cause log truncation. 如需詳細資訊,請參閱 交易記錄 (SQL Server)For more information, see The Transaction Log (SQL Server).

當系統損壞時,復原給定資料庫所需的時間長度大部分取決於重做損壞時已變更之頁面所需的隨機 I/O 數量。After a system crash, the length of time required to recover a given database depends largely on the amount of random I/O needed to redo pages that were dirty at the time of the crash. 這表示 recovery interval 設定不可靠。This means that the recovery interval setting is unreliable. 它無法判斷精確的復原持續時間。It cannot determine an accurate recovery duration. 此外,當自動檢查點正在進行時,資料的一般 I/O 活動會大幅增加而且無法預測。Furthermore, when an automatic checkpoint is in progress, the general I/O activity for data increases significantly and quite unpredictably.

復原間隔對復原效能的影響Impact of recovery interval on recovery performance

如果是使用簡短交易的線上交易處理 (OLTP) 系統, recovery interval 是決定復原時間的主要因素。For an online transaction processing (OLTP) system using short transactions, recovery interval is the primary factor determining recovery time. 但是, recovery interval 選項並不會影響重做長時間執行之交易所需的時間。However, the recovery interval option does not affect the time required to undo a long-running transaction. 如果資料庫包含長時間執行的交易,則復原此資料庫所花費的時間可能要比 recovery interval 設定中指定的時間還長。Recovery of a database with a long-running transaction can take much longer than the time specified in the recovery interval setting.

例如,如果長時間執行的交易在伺服器執行個體停用之前,花了兩個小時執行更新,則實際的復原花在復原長交易的時間要比 recovery interval 值長得多。For example, if a long-running transaction took two hours to perform updates before the server instance became disabled, the actual recovery takes considerably longer than the recovery interval value to recover the long transaction. 如需長時間執行的交易對復原時間之影響的詳細資訊,請參閱 交易記錄 (SQL Server)For more information about the impact of a long running transaction on recovery time, see The Transaction Log (SQL Server). 如需復原流程的詳細資訊,請參閱還原和復原概觀 (SQL Server)For more information about the recovery process, see Restore and Recovery Overview (SQL Server).

一般來說,預設值會提供最佳復原效能。Typically, the default values provides optimal recovery performance. 但是在以下情況下,變更復原間隔可能會提升效能:However, changing the recovery interval might improve performance in the following circumstances:

  • 如果未回復長時間執行的交易,復原通常花的時間大幅多於 1 分鐘時。If recovery routinely takes significantly longer than 1 minute when long-running transactions are not being rolled back.

  • 如果您注意到頻繁的檢查點損害了資料庫的效能。If you notice that frequent checkpoints are impairing performance on a database.

如果您決定增加 recovery interval 設定,我們建議您逐漸少量增加此設定,並評估每次累加對於復原效能的影響。If you decide to increase the recovery interval setting, we recommend increasing it gradually by small increments and evaluating the effect of each incremental increase on recovery performance. 這個方法非常重要,因為當 recovery interval 設定增加時,要多花許多倍的時間才能完成資料庫復原。This approach is important because as the recovery interval setting increases, database recovery takes that many times longer to complete. 例如,如果您將 recovery interval 變更為 10 分鐘,則完成復原所花費的時間要比將 recovery interval 設定為 1 分鐘時大約多 10 倍的時間。For example, if you change recovery interval to 10 minutes, recovery takes approximately 10 times longer to complete than when recovery interval is set to 1 minute.

間接檢查點Indirect checkpoints

間接檢查點是在 SQL Server 2012 (11.x)SQL Server 2012 (11.x)中引進,它會提供替代自動檢查點的可設定資料庫層級。Indirect checkpoints, introduced in SQL Server 2012 (11.x)SQL Server 2012 (11.x), provide a configurable database-level alternative to automatic checkpoints. 指定 [目標復原時間] 資料庫設定選項可設定此項目。This can be configured by specifying the target recovery time database configuration option. 如需詳細資訊,請參閱 變更資料庫的目標復原時間 (SQL Server)伺服器組態選項。For more information, see Change the Target Recovery Time of a Database (SQL Server). 萬一系統損壞,間接檢查點可能會提供比自動檢查點更快而且更可以預測的復原時間。In the event of a system crash, indirect checkpoints provide potentially faster, more predictable recovery time than automatic checkpoints. 間接檢查點會提供以下優點:Indirect checkpoints offer the following advantages:

  • 設定間接檢查點的資料庫線上交易式工作負載可能會導致效能降低。An online transactional workload on a database configured for indirect checkpoints can experience performance degradation. 間接檢查點可確定中途分頁的數目,低於特定臨界值,如此即可在目標復原時間內完成資料庫的復原。Indirect checkpoints ensure that the number of dirty pages are below a certain threshold so the database recovery completes within the target recovery time.

相對於使用中途分頁數目的間接檢查點復原間隔設定選項會使用交易數目來判斷復原時間。The recovery interval configuration option uses the number of transactions to determine the recovery time, as opposed to indirect checkpoints which makes use of the number of dirty pages. 在收到大量 DML 作業的資料庫上啟用間接檢查點時,背景寫入器可開始積極排清磁碟的中途緩衝區,以確保執行復原所需的時間,落在資料庫所設的目標復原時間內。When indirect checkpoints are enabled on a database receiving a large number of DML operations, the background writer can start aggressively flushing dirty buffers to disk to ensure that the time required to perform recovery is within the target recovery time set of the database. 如此會在某些系統上造成額外的 I/O 活動,而若磁碟子系統的運作超過或接近 I/O 臨界值,就會形成效能瓶頸。This can cause additional I/O activity on certain systems which can contribute to a performance bottleneck if the disk subsystem is operating above or nearing the I/O threshold.

  • 間接檢查點可讓您考量 REDO 期間的隨機 I/O 成本來可靠控制資料庫復原時間。Indirect checkpoints enable you to reliably control database recovery time by factoring in the cost of random I/O during REDO. 如此可讓伺服器執行個體維持在指定資料庫的復原時間上限內 (除非長時間執行的交易造成過多的復原次數)。This enables a server instance to stay within an upper-bound limit on recovery times for a given database (except when a long-running transaction causes excessive UNDO times).

  • 間接檢查點會持續在背景中將中途分頁寫入磁碟,以減少與檢查點相關的 I/O 峰值。Indirect checkpoints reduce checkpoint-related I/O spiking by continually writing dirty pages to disk in the background.

但是,設定間接檢查點的資料庫線上交易式工作負載可能會導致效能降低。However, an online transactional workload on a database configured for indirect checkpoints can experience performance degradation. 這是因為,間接檢查點使用的背景寫入器有時會讓伺服器執行個體的寫入負載總量增加。This is because the background writer used by indirect checkpoint sometimes increases the total write load for a server instance.

重要

間接檢查點是在 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 中建立之新資料庫的預設行為,包括模型和 TempDB 資料庫。Indirect checkpoint is the default behavior for new databases created in SQL Server 2016 (13.x)SQL Server 2016 (13.x), including the Model and TempDB databases.
除非明確地改變成使用間接檢查點,否則已就地升級或從舊版 SQL ServerSQL Server 還原的資料庫會使用先前的自動檢查點行為。Databases that were upgraded in-place, or restored from a previous version of SQL ServerSQL Server, will use the previous automatic checkpoint behavior unless explicitly altered to use indirect checkpoint.

改善的間接檢查點延展性Improved indirect checkpoint scalability

SQL Server 2019 (15.x)SQL Server 2019 (15.x) 之前,您可能會在資料庫產生大量中途分頁 (例如 tempdb) 時遇到沒有產量的排程器錯誤。Prior to SQL Server 2019 (15.x)SQL Server 2019 (15.x), you may experience non-yielding scheduler errors when there is a database that generates a large number of dirty pages, such as tempdb. SQL Server 2019 (15.x)SQL Server 2019 (15.x) 引進改善的間接檢查點延展性,有助於避免在具備大量 UPDATE/INSERT 工作負載的資料庫上發生這類錯誤。introduces improved scalability for indirect checkpoint, which should help avoid these errors on databases that have a heavy UPDATE/INSERT workload.

內部檢查點Internal checkpoints

內部檢查點是由各種伺服器元件產生,可保證磁碟映像符合目前的記錄檔狀態。Internal Checkpoints are generated by various server components to guarantee that disk images match the current state of the log. 產生內部檢查點是為了回應以下事件:Internal checkpoint are generated in response to the following events:

  • 使用 ALTER DATABASE 加入或移除資料庫檔案。Database files have been added or removed by using ALTER DATABASE.

  • 取得資料庫備份。A database backup is taken.

  • 針對 DBCC CHECKDB 建立資料庫快照集,不論是明確或內部方式均可。A database snapshot is created, whether explicitly or internally for DBCC CHECKDB.

  • 執行需要關閉資料庫的活動。An activity requiring a database shutdown is performed. 例如,AUTO_CLOSE 為 ON,且上次使用者與資料庫的連接已經關閉,或是已變更資料庫選項,因此需要重新啟動資料庫。For example, AUTO_CLOSE is ON and the last user connection to the database is closed, or a database option change is made that requires a restart of the database.

  • 經由停止 SQL ServerSQL Server (MSSQLSERVER) 服務,來停止 SQL ServerSQL Server 的執行個體。An instance of SQL ServerSQL Server is stopped by stopping the SQL ServerSQL Server (MSSQLSERVER) service . 任何一項動作都會導致在 SQL ServerSQL Server執行個體的每個資料庫中產生檢查點。Either action causes a checkpoint in each database in the instance of SQL ServerSQL Server.

  • SQL ServerSQL Server 容錯移轉叢集執行個體 (FCI) 離線。Bringing a SQL ServerSQL Server failover cluster instance (FCI) offline.

若要變更伺服器執行個體的復原間隔To change the recovery interval on a server instance

若要設定資料庫的間接檢查點To configure indirect checkpoints on a database

若要發出資料庫的手動檢查點To issue a manual checkpoint on a database

另請參閱See also

交易記錄 (SQL Server) The Transaction Log (SQL Server)
SQL Server 交易記錄架構與管理指南SQL Server Transaction Log Architecture and Management Guide