合 MSSQL的更改跟踪功能
Tags： MSSQL整理自官网更改跟踪（Chang Tracking）
- 简介
- 单向和双向同步应用程序
- 单向同步应用程序
- 双向同步应用程序
- 更改跟踪的工作方式
- 更改跟踪清理
- 启用和禁用更改跟踪 (SQL Server)
- 对数据库启用更改跟踪
- 对表启用更改跟踪
- 为表或数据库禁用更改跟踪
- 管理更改跟踪
- 目录视图
- 安全性
- 了解更改跟踪开销
- 对 DML 的影响
- 对存储的影响
- 使用更改跟踪 (SQL Server)
- 通过使用更改跟踪函数获取更改
- 关于更改跟踪函数
- 获取初始数据
- 使用更改跟踪函数获取更改
- 版本号
- 验证上次同步的版本
- 使用列跟踪
- 获取一致且正确的结果
- 使用快照隔离
- 清除和快照隔离
- 快照隔离的替代方法
- 更改跟踪如何处理对数据库的更改
- 检查冲突
- 设置上下文信息
- 确保一致且正确的结果
- 更改跟踪和数据还原
- 排查更改跟踪自动清理问题
- 症状
- 调试和缓解
- 自动清理状态
- 1.清除已关闭
- 2.清理已打开，但未运行
- 自动清理运行，但没有进度
- 1.评估自动清理积压工作
- 2.检查表锁冲突
- 3.检查其他原因
- 排查 syscommittab 问题
- 清理期间 CPU 使用率高
- 运行清理的频率超过 30 分钟
- 总结
- 参考
简介
更改跟踪是一种轻量型解决方案，它为应用程序提供了一种有效的更改跟踪机制。 通常，若要使应用程序能够查询对数据库中的数据所做的更改和访问与这些更改相关的信息，应用程序开发人员必须实现自定义更改跟踪机制。 这些机制通常涉及多项工作，并且常常涉及触发器、“时间戳”列和新表组合来存储跟踪信息，同时还会涉及使用自定义清理过程。
不同类型的应用程序对其所需的有关更改的信息量有不同的要求。 应用程序可以使用更改跟踪来回答以下有关对用户表所做更改的问题：
- 用户表中有哪些行发生了更改？
- 所需的只是行已更改的事实，而不是行更改的次数或任何中间更改的值。
- 可以从所跟踪的表中直接获取最新的数据。
某行是否已更改？
- 当在同一事务中进行更改时，必须提供并记录行已更改的事实以及有关这一更改的信息。
备注
如果应用程序需要有关所有所做更改的信息以及所更改数据的中间值，则可能适合使用变更数据捕获，而不适合使用更改跟踪。 有关详细信息，请参阅关于变更数据捕获 (SQL Server)。
单向和双向同步应用程序
需要将数据与 SQL Server 数据库引擎实例同步的应用程序必须能够查询更改。 更改跟踪可用作单向和双向同步应用程序的基础。
单向同步应用程序
可以生成使用更改跟踪的单向同步应用程序，如客户端或中间层缓存应用程序。 如下图所示，缓存应用程序要求在 数据库引擎 中存储数据并在其他数据存储区中缓存数据。 应用程序必须能够使用对数据库表所做的任何更改来使缓存保持最新。 没有要传回到的 数据库引擎的更改。
双向同步应用程序
也可以生成使用更改跟踪的双向同步应用程序。 在此方案中， 数据库引擎 实例中的数据与一个或多个数据存储区同步。 可以更新这些存储区中的数据，并且这些更改必须再同步到 数据库引擎中。
偶尔连接的应用程序就是双向同步应用程序的一个很好的示例。 在这种类型的应用程序中，客户端应用程序查询并更新本地存储区。 当客户端与服务器之间存在连接时，应用程序会与服务器同步，并更改两个方向的数据流。
双向同步应用程序必须能够检测冲突。 如果在两次同步之间的时间两个数据存储区中的相同数据发生了更改，则会出现冲突。 有了检测冲突的功能，应用程序可以确保不会丢失这些更改。
更改跟踪的工作方式
要配置更改跟踪，可以使用 DDL 语句或 SQL Server Management Studio。 有关详细信息，请参阅 启用和禁用更改跟踪 (SQL Server)。 若要跟踪更改，必须首先对数据库启用更改跟踪，然后对该数据库内要跟踪的表启用更改跟踪。 表定义无需任何更改，也不会创建任何触发器。
为表配置了更改跟踪后，任何影响该表中的行的 DML 语句都将导致针对每个有所修改的行的更改跟踪信息被记录下来。 若要查询已更改的行并获取有关这些更改的信息，可以使用 更改跟踪功能。
主键列的值是来自所跟踪的并记录更改信息的表中的唯一信息。 这些值用于标识发生更改的行。 要获取这些行的最新数据，应用程序可以使用主键列值联接源表和所跟踪的表。
使用更改跟踪也可以获取与每个行所做更改相关的信息。 例如，导致更改（插入、更新或删除）的 DML 操作的类型或作为更新操作的一部分而更改的列。
更改跟踪清理
所有表（已启用更改跟踪功能）的更改跟踪信息存储在内存中行存储中。 与每个表（已启用更改跟踪功能）关联的更改跟踪数据会在每个检查点从内存中行存储刷新到磁盘上的相应内部表。 检查点的内存中行存储还会在行移到磁盘上的表之后进行清除。
启用更改跟踪功能的每个表都有一个磁盘上的内部表，更改跟踪功能可以使用该表来确定更改版本以及自特定版本以来更改过的行。 每次苏醒时，“自动清除”线程就会扫描 SQL Server 实例上的所有用户数据库，标识启用了更改跟踪的数据库。 磁盘上的每个内部表都会根据数据库的保持期设置清除其过期记录。
在 SQL Server 2014 (12.x) 和 SQL Server 2016 (13.x) 的 Service Pack 中增加了存储过程，用于对更改跟踪内部表执行手动清理。 KB173157 中提供了该存储过程的详细信息。
启用和禁用更改跟踪 (SQL Server)
对数据库启用更改跟踪
你必须先在数据库级别启用更改跟踪，然后才能使用更改跟踪。 下面的示例显示了如何使用 ALTER DATABASE 来启用更改跟踪。
SQL
1 2 3 | ALTER DATABASE AdventureWorks2022 SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON) |
你还可以通过使用数据库属性（“更改跟踪”页）对话框，在 SQL Server Management Studio 中启用更改跟踪。 如果数据库包含内存优化表，则无法使用 SQL Server Management Studio 启用更改跟踪。 若要启用，请使用 T-SQL。
可以在启用更改跟踪时指定 CHANGE_RETENTION 和 AUTO_CLEANUP 选项，并且可以在启用更改跟踪后随时更改这些值。
更改保持期值指定了更改跟踪信息的保留时间。 早于此时间的更改跟踪信息将被定期删除。 设置该值时，应考虑应用程序与数据库中的表进行同步的频率。 指定的保持期必须至少等于最大同步时间间隔。 如果应用程序获取更改的时间间隔过长，则返回的结果可能不正确，因为某些更改信息可能已被删除。 若要避免获取错误的结果，应用程序可以使用 CHANGE_TRACKING_MIN_VALID_VERSION 系统函数来确定同步之间的时间间隔是否已太长。
可使用 AUTO_CLEANUP 选项来启用或禁用删除陈旧的更改跟踪信息的清除任务。 如果出现临时性问题使得应用程序无法同步，并且在问题解决之前必须暂停用于删除早于保持期的更改跟踪信息的进程，则该设置会很有用。
对于使用更改跟踪的任何数据库，请注意以下事项：
- 若要使用更改跟踪，必须将数据库兼容级别设为 90 或更高。 如果数据库的兼容级别低于 90，则可以配置更改跟踪。 但是，用于获取更改跟踪信息的 CHANGETABLE 函数将返回错误。
- 使用快照隔离是帮助确保所有更改跟踪信息保持一致的最简单方式。 因此，我们强烈建议将数据库的快照隔离设为 ON。 有关详细信息，请参阅使用更改跟踪 (SQL Server)。
对表启用更改跟踪
对于要跟踪的每个表都必须启用更改跟踪。 启用更改跟踪后，将会为表中受 DML 操作影响的所有行保留更改跟踪信息。
下面的示例显示了如何使用 ALTER TABLE 来对表启用更改跟踪。
SQL
1 2 3 | ALTER TABLE Person.Contact ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON) |
你还可以通过使用数据库属性（“变更跟踪”页）对话框，在 SQL Server Management Studio 中对表启用更改跟踪。
当 TRACK_COLUMNS_UPDATED 选项设为 ON 时，SQL Server 数据库会将有关哪些列已更新的额外信息存储到内部更改跟踪表中。 列跟踪使应用程序可以只同步那些已更新的列。 这可以提高效率和性能。 但是，由于保留列跟踪信息增加了一些额外的存储开销，因而默认情况下此选项设为 OFF。
为表或数据库禁用更改跟踪
必须首先为所有启用了更改跟踪的表禁用更改跟踪，然后才能将数据库的更改跟踪设为 OFF。 若要确定数据库中哪些表启用了更改跟踪，请使用 sys.change_tracking_tables 目录视图。
下面的示例显示了如何使用 ALTER TABLE 对表禁用更改跟踪。
SQL
1 2 | ALTER TABLE Person.Contact DISABLE CHANGE_TRACKING; |
当数据库中没有用于跟踪更改的表时，便可以禁用数据库的更改跟踪。 下面的示例显示如何使用 ALTER DATABASE 对数据库禁用更改跟踪。
SQL
1 2 | ALTER DATABASE AdventureWorks2022 SET CHANGE_TRACKING = OFF |
管理更改跟踪
以下各节列出了与管理更改跟踪相关的目录视图、权限和设置。
目录视图
若要确定哪些表和数据库启用了更改跟踪，可以使用以下目录视图：
此外， sys.internal_tables 目录视图还列出了对用户表启用更改跟踪时所创建的内部表。
1 2 3 | select * from sys.change_tracking_databases; select * from sys.change_tracking_tables; |
安全性
若要使用 更改跟踪函数访问更改跟踪信息，主体必须拥有以下权限：
- 至少针对主键列（已启用更改跟踪的表针对被查询表的主键列）拥有 SELECT 权限。
- 对于要获取其更改的表拥有 VIEW CHANGE TRACKING 权限。 要求拥有 VIEW CHANGE TRACKING 权限的原因如下：
- 更改跟踪记录包含有关已删除行的信息，具体而言，就是已删除行的主键值。 在删除了某些敏感数据之后，某个主体可能已被授予针对启用了更改跟踪的表的 SELECT 权限。 在这种情况下，你不会希望该主体能够使用更改跟踪来访问那些已删除的信息。
- 更改跟踪信息可以存储有关更新操作所更改的列的信息。 某个主体可能无权访问包含敏感信息的列。 但是，由于有更改跟踪信息，因此主体可以确定某列的值是否已更新，但是该主体无法确定该列的值。
了解更改跟踪开销
启用表的更改跟踪后，会影响某些管理操作。 下表列出了应当注意的操作和影响。
展开表
| 操作 | 启用更改跟踪后 |
|---|---|
| DROP TABLE | 会删除已删除表的所有更改跟踪信息。 |
| ALTER TABLE DROP CONSTRAINT | 删除 PRIMARY KEY 约束的尝试将失败。 必须先禁用更改跟踪，然后才能删除 PRIMARY KEY 约束。 |
| ALTER TABLE DROP COLUMN | 如果要删除的列是主键的一部分，则不允许删除该列，而不管是否启用了更改跟踪。 如果要删除的列不是主键的一部分，则可以成功删除该列。 但是，首先应了解此操作对同步此数据的任何应用程序的影响。 如果为该表启用了列更改跟踪，则可能仍会将已删除的列作为更改跟踪信息的一部分返回。 已删除列的处理由应用程序负责。 |
| ALTER TABLE ADD COLUMN | 如果将新列添加到启用了更改跟踪的表中，则不会跟踪该列的添加。 只会跟踪对新列所做的更新和更改。 |
| ALTER TABLE ALTER COLUMN | 不会跟踪非主键列的数据类型更改。 |
| ALTER TABLE SWITCH | 如果其中一个表或两个表都启用了更改跟踪，则切换分区将失败。 |
| DROP INDEX 或 ALTER INDEX DISABLE | 不能删除或禁用强制使用主键的索引。 |
| TRUNCATE TABLE | 可以对启用了更改跟踪的表执行截断表操作。 但是，不会跟踪由该操作删除的行，并且会更新最低有效版本。 当应用程序检查其版本时，检查结果会表明该版本太陈旧，需要进行重新初始化。 这与禁用后又重新启用表的更改跟踪的效果相同。 |
由于在操作过程中会存储更改跟踪信息，因此使用更改跟踪会增加 DML 操作的一些开销。
对 DML 的影响
更改跟踪已经过优化，以尽可能减小对 DML 操作的性能影响。 对表使用更改跟踪所导致的性能开销增加类似于为表创建了一个索引并需要维护该索引时而导致的开销。
对于由 DML 操作更改的每一行，都会向内部更改跟踪表中添加一行。 这种与 DML 操作相关的影响取决于各种因素，例如：
- 主键列数
- 用户表行中所更改的数据量
- 事务中所执行的操作数
如果使用了快照隔离，则它也会影响所有 DML 操作的性能，而不管是否启用了更改跟踪。
对存储的影响
更改跟踪数据存储在以下类型的内部表中：
内部更改表
启用了更改跟踪的每个用户表都有一个内部更改表。
内部事务表
数据库有一个内部事务表。
这些内部表对存储要求有下列影响：
- 对于用户表中每行的每个更改，都会向内部更改表中添加一行。 该行有一个较小的固定开销，外加一个大小等于主键列大小的可变开销。 该行可以包含由应用程序设置的可选上下文信息。 此外，如果启用了列跟踪，则每个发生更改的列还需要在跟踪表中占用 4 字节。
- 对于每个已提交的事务，都会向内部事务表中添加一行。
对于其他内部表，可以使用 sp_spaceused 存储过程来确定用于更改跟踪表的空间。 可以使用 sys.internal_tables 目录视图来获取这些内部表的名称，如下例所示。
SQL
1 2 | sp_spaceused 'sys.change_tracking_309576141' sp_spaceused 'sys.syscommittab' |
使用更改跟踪 (SQL Server)
使用更改跟踪的应用程序必须能够获取跟踪的更改，将这些更改应用到其他数据存储区并更新源数据库。 本文介绍了如何执行这些任务，以及在发生故障转移且必须从备份还原数据库时，角色更改跟踪如何进行。
通过使用更改跟踪函数获取更改
介绍如何使用更改跟踪功能来获取更改以及有关对数据库所做的更改的信息。
关于更改跟踪函数
应用程序可以使用以下函数来获取在数据库中所做的更改以及有关这些更改的信息：
