数据库检查点 (SQL Server)

项目
04/26/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 设置确定，以秒为单位。

自动检查点

每当日志记录数达到数据库引擎估计在“恢复间隔”服务器配置选项中指定的时间内可以处理的数量时，便会生成一个自动检查点。有关详细信息，请参阅 Configure the recovery interval Server Configuration Option。

在没有用户定义的目标恢复时间的每个数据库中，数据库引擎都会生成自动检查点。频率取决于“恢复间隔”高级服务器配置选项，该选项指定给定的服务器实例在系统重新启动期间应用于恢复数据库的最长时间。数据库引擎将估计它在恢复间隔内可以处理的最大日志记录数。当使用自动检查点的数据库达到此最大日志记录数时，数据库引擎会在数据库上发出一个检查点。

自动检查点之间的时间间隔可以变化很大。具有大量事务工作负荷的数据库的检查点生成频率将高于主要用于只读操作的数据库的检查点生成频率。在简单恢复模式下，如果日志填充 70％，则自动检查点还将排队。

在简单恢复模式下，除非某些因素延迟了日志截断，否则自动检查点将截断事务日志中没有使用的部分。相比之下，在完整和大容量日志恢复模式下，建立日志备份链后，自动检查点将不会导致日志截断。有关详细信息，请参阅事务日志 (SQL Server)。

在系统崩溃后恢复给定数据库所需的时间主要取决于重做崩溃时的脏页所需的随机 I/O 量。这意味着 recovery interval 设置不可靠。它无法确定准确的恢复持续时间。此外，自动检查点正在进行时，数据的常规 I/O 活动会显著增加且不可预知。

恢复间隔对恢复性能的影响

对于使用短事务的联机事务处理 (OLTP) 系统， 恢复间隔 是确定恢复时间的主要因素。但是，recovery interval 选项不会影响撤消长时间运行的事务所需的时间。恢复具有长时间运行事务的数据库所需的时间可能会比“恢复间隔”设置指定的时间要长得多。

例如，如果一个长时间运行的事务在服务器实例禁用前花费了两个小时来执行更新，则实际的恢复将必然花费比 恢复间隔 值更长的时间来恢复该长时间运行的事务。如需详细了解长时间运行的事务对恢复时间的影响，请参阅事务日志 (SQL Server)。有关恢复过程的详细信息，请参阅还原和恢复概述 (SQL Server)。

通常，默认值提供最佳恢复性能。但是，在以下情况下更改恢复间隔可能会提高性能：

例行恢复所需的时间在长时间运行的事务未回滚时超过 1 分钟。
如果您注意到频繁的检查点操作损害了数据库的性能。

如果您决定增大 recovery interval 设置，我们建议一点一点逐渐增大该值并评估每次增大对恢复性能的影响。这种方法很重要，因为随着 recovery interval 设置的增大，数据库恢复需要更长的时间来完成。例如，如果“恢复间隔”更改为 10 分钟，则恢复完成所耗的时间比“恢复间隔”设置为 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) 脱机。