排查更改跟踪自动清理问题

项目
01/08/2024

适用于： SQL Server Azure SQL 数据库 Azure SQL 托管实例

本文介绍了在进行更改跟踪自动清理过程中遇到的常见问题的排查方法。

现象

通常，如果自动清理未按预期运行，则可以看到以下一个或多个现象：

一个或多个更改跟踪端表或 syscommittab 系统表出现存储消耗高的情况。
端表（名称以前缀 change_tracking 开头（例如 change_tracking_12345）或 syscommittab 两者，在配置的保留期之外显示大量行。
dbo.MSChange_tracking_history 表具有包含具体清理错误的条目。
CHANGETABLE 性能随时间推移而下降。
自动清理或手动清理报告 CPU 使用率高。

调试和缓解

若要确定更改跟踪自动清理问题的根本原因，请使用以下步骤调试和缓解问题。

自动清理状态

检查自动清理是否已经运行。请在同一数据库中查询清理历史记录表来检查此状态。如果清理已经运行，则表中会包含清理开始和结束时间的条目。如果清理尚未运行，则表为空或具有旧条目。如果历史记录表具有列中 comments 标记 cleanup errors 的条目，则由于表级清理错误，清理失败。

SELECT TOP 1000 * FROM dbo.MSChange_tracking_history ORDER BY start_time DESC;

自动清理会定期运行，默认间隔为 30 分钟。如果历史记录表不存在，很可能是自动清理从未运行过。否则，检查 start_time 和 end_time 列的值。如果最近的条目并非最新的（即相差了几个小时或几天），则自动清理可能并未运行。如果是这种情况，请使用以下步骤进行故障排除。

1.清除已关闭

检查数据库是否已打开自动清理。如果未打开，则打开自动清理并等待至少 30 分钟，然后查看历史记录表以获取新条目。监视历史记录表中之后的进度。

SELECT * FROM sys.change_tracking_databases WHERE database_id=DB_ID('<database_name>')

is_auto_cleanup_on 中出现非零值表示自动清理已启用。保留期值控制系统中更改跟踪元数据的保留期限。更改跟踪保留期的默认值为 2 天。

若要了解如何启用或禁用更改跟踪，请参阅启用和禁用更改跟踪 (SQL Server)。

2.清理已打开，但未运行

如果自动清理处于打开状态，自动清理线程可能会因意外错误而停止。目前，重启自动清理线程不可行。必须启动故障转移到辅助服务器（或在缺少辅助服务器的情况下重启服务器），并确认为数据库启用了自动清理设置。

自动清理运行，但没有进度

如果一个或多个端表出现大量存储消耗，或者包含大量超出配置的保留期的记录，请按照本节中的步骤操作，其中描述了单个端表的补救措施。如有必要，可以针对更多表重复相同步骤。

1.评估自动清理积压工作

识别包含大量过期记录积压工作，需要对其执行缓解措施的端表。运行以下查询识别包含大量过期记录计数的端表。记得替换示例脚本中的值，如下所示。

获取无效的清理版本：
```
SELECT * FROM sys.change_tracking_tables;
```
返回的行中的 cleanup_version 值代表无效的清理版本。

运行以下动态 Transact-SQL （T-SQL）查询，该查询生成查询以获取端表的过期行计数。将查询中 <invalid_version> 的值替换为在上一步中获取的值。

SELECT 'SELECT ''' + QUOTENAME(name) + ''', count(*) FROM [sys].' + QUOTENAME(name)
    + ' WHERE sys_change_xdes_id IN (SELECT xdes_id FROM sys.syscommittab ssct WHERE ssct.commit_ts <= <invalid_version>) UNION'
FROM sys.internal_tables
WHERE internal_type = 209;

复制上个查询中的结果集，并从最后一行删除 UNION 关键字。如果运行的是通过专用管理员连接 (DAC) 生成的 T-SQL 查询，则查询会提供所有端表的过期行计数。根据 sys.syscommittab 表的大小和端表的数量，此查询可能花费很长时间才能完成。

重要

若要继续执行缓解步骤，必须执行此步骤。如果上一个查询无法执行，请使用下一个给定的查询标识各个端表的过期行计数。

对侧表执行以下缓解步骤，使过期行计数的递减顺序下降，直到过期的行计数归结为可管理的状态，以便自动清理赶上。

识别包含大量过期记录计数的端表后，首先，收集有关端表 delete 语句的延迟以及过去几个小时每秒删除速率的信息。接下来，考虑旧的行计数和删除延迟估算出清理端表所需要的时间。

使用以下 T-SQL 代码片段，将参数模板替换为适当的值。

查询每秒清理速率：

SELECT
    table_name,
    rows_cleaned_up / ISNULL(NULLIF(DATEDIFF(second, start_time, end_time), 0), 1),
    cleanup_version
FROM dbo.MSChange_tracking_history
WHERE table_name = '<table_name>'
ORDER BY end_time DESC;

还可以对 DATEDIFF 函数使用分钟或小时粒度。

在端表中查找旧的行计数。此查询可帮助你查找待清理的行数。

用户表的 <internal_table_name> 和 <cleanup_version> 位于前一个部分中返回的输出中。使用此信息，通过专用管理员连接执行以下 T-SQL 代码 (DAC)：
```
SELECT '<internal_table_name>',
    COUNT(*)
FROM sys.<internal_table_name>
WHERE sys_change_xdes_id IN (
        SELECT xdes_id
        FROM sys.syscommittab ssct
        WHERE ssct.commit_ts <= <cleanup_version>
);
```
此查询需要一段时间才能完成。如果查询超时，请通过查找汇总行与待清理的行之间的差值来计算旧的行。

通过执行以下查询查找端表中的行总数：

SELECT sum(row_count) FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('sys.<internal_table_name>')
GROUP BY partition_id;

通过执行以下查询查找端表中的活动行总数：
```
SELECT '<internal_table_name>', COUNT(*) FROM sys.<internal_table_name> WHERE sys_change_xdes_id
IN (SELECT xdes_id FROM sys.syscommittab ssct WHERE ssct.commit_ts > <cleanup_version>);
```
可以使用清理和陈旧的行计数的速率来计算清理表的估算时间。请考虑以下公式：

以分钟为单位清理时间 = （陈旧的行计数）/（以分钟为单位的清理速率）

如果完成表清理的时间可以接受，则监视进度并让自动清理继续工作。否则，请继续执行后续步骤，进一步向下钻取。

2.检查表锁冲突

确定由于表锁升级冲突而无法进行清理，这会始终在端表上获取锁来删除行从而让清理无法进行。

若要确认锁冲突，请运行以下 T-SQL 代码。此查询提取存在问题的表的记录以确定是否有多个条目指示存在锁冲突。如果只是一段时间内偶尔发生几次冲突，则不应继续执行缓解步骤。冲突应该是反复出现才行。

SELECT TOP 1000 *
FROM dbo.MSChange_tracking_history
WHERE table_name = '<user_table_name>'
ORDER BY start_time DESC;

如果历史记录表在具有值 Cleanup error: Lock request time out period exceeded 的 comments 列中具有多个条目，则明确指示多次清理尝试由于锁冲突或连接出现锁超时而失败。请考虑下列补救措施：

对有问题的表启用和禁用更改跟踪。这会导致为表保留的所有跟踪元数据被清除。表中的数据保持不变。这是最快的补救措施。
如果上一个选项不可行，则通过启用跟踪标志 8284 继续对表执行手动清理，如下所示：
```
DBCC TRACEON (8284, -1);
GO
EXEC [sys].[sp_flush_CT_internal_table_on_demand] @TableToClean = '<table_name>';
```

3.检查其他原因

清理滞后的另一个可能原因是 delete 语句的速度缓慢。若要确定是否是此原因，请检查值 hardened_cleanup_version。此值可以通过专用管理员连接 (DAC) 检索到正在考虑的数据库。

通过执行以下查询查找强化清理版本：

SELECT * FROM sys.sysobjvalues WHERE valclass = 7 AND objid = 1004;

通过执行以下查询查找清理版本：

SELECT * FROM sys.sysobjvalues WHERE valclass = 7 AND objid = 1003;

如果 hardened_cleanup_version 和 cleanup_version 值相等，请跳过此部分，然后继续执行下一部分。

如果这两个值不同，则表示一个或多个端表遇到了错误。最快的缓解措施是禁止和对有问题的表启用更改跟踪。这会导致为表保留的所有跟踪元数据被清除。表中的数据保持不变。

如果上一个选项不可行，则对表运行手动清理。

排查 syscommittab 问题

本部分介绍调试和缓解系统表问题 syscommittab 的步骤（如果它使用大量存储空间，或者如果存在大量过时行积压）。

syscommittab 系统表清理取决于端表清理。只有在清除所有端表后，才能清除 syscommittab。确保执行自动清理运行，但没有进度部分中介绍的所有步骤。

若要明确调用 syscommittab 清理，请使用 sys.sp_flush_commit_table_on_demand 存储过程。

注意

如果要删除大量行的积压工作，则 sys.sp_flush_commit_table_on_demand 存储过程会花费不少时间。

如果 sys.sp_flush_commit_table_on_demand 一文中示例部分所示，此存储过程会返回 safe_cleanup_version() 的值，以及已删除的行的数量。如果返回的值看起来是 0，且打开了快照隔离，则清理可能没有删除 syscommittab 中的任何内容。

如果保留期大于一天，则应在全局启用跟踪标志 8239 后重新运行 sys.sp_flush_commit_table_on_demand 存储过程。当快照隔离处于关闭状态时，使用此跟踪标志始终是安全的，但在某些情况下可能没有必要使用。

清理期间 CPU 使用率高

本节中介绍的问题可能在较旧版本的 SQL Server 上出现。如果数据库中存在大量更改跟踪表，并且自动清理或手动清理会导致 CPU 使用率较高。此问题也可能是由于历史记录表（前面部分简要提及）引起的。

使用以下 T-SQL 代码检查历史记录表中的行数：

SELECT COUNT(*) from dbo.MSChange_tracking_history;

如果行数足够大，请尝试添加以下索引（如果不存在）。使用以下 T-SQL 代码添加索引：

IF NOT EXISTS (
    SELECT *
    FROM sys.indexes
    WHERE name = 'IX_MSchange_tracking_history_start_time'
        AND object_id = OBJECT_ID('dbo.MSchange_tracking_history')
)
BEGIN
    CREATE NONCLUSTERED INDEX IX_MSchange_tracking_history_start_time
    ON dbo.MSchange_tracking_history (start_time)
END

运行清理的频率超过 30 分钟

特定表可能会遇到高更改率，你可能会发现自动清理作业无法清理侧表，并且 syscommittab 间隔为 30 分钟。如果发生这种情况，可以运行频率增加的手动清理作业来简化该过程。

对于 SQL Server 和 Azure SQL 托管实例，请使用 sp_flush_CT_internal_table_on_demand 创建后台作业，内部时间短于默认的 30 分钟。对于 Azure SQL 数据库，可以使用 Azure 逻辑应用来计划这些作业。

可以使用以下 T-SQL 代码创建作业来帮助清理更改跟踪的端表：

-- Loop to invoke manual cleanup procedure for cleaning up change tracking tables in a database
-- Fetch the tables enabled for change tracking
SELECT IDENTITY(INT, 1, 1) AS TableID,
    (SCHEMA_NAME(tbl.Schema_ID) + '.' + OBJECT_NAME(ctt.object_id)) AS TableName
INTO #CT_Tables
FROM sys.change_tracking_tables ctt
INNER JOIN sys.tables tbl
    ON tbl.object_id = ctt.object_id;

-- Set up the variables
DECLARE @start INT = 1,
    @end INT = (
        SELECT COUNT(*)
        FROM #CT_Tables
        ),
    @tablename VARCHAR(255);

WHILE (@start <= @end)
BEGIN
    -- Fetch the table to be cleaned up
    SELECT @tablename = TableName
    FROM #CT_Tables
    WHERE TableID = @start

    -- Execute the manual cleanup stored procedure
    EXEC sp_flush_CT_internal_table_on_demand @tablename

    -- Increment the counter
    SET @start = @start + 1;
END

DROP TABLE #CT_Tables;

通过

排查更改跟踪自动清理问题

现象

调试和缓解

自动清理状态

1.清除已关闭

2.清理已打开，但未运行

自动清理运行，但没有进度

1.评估自动清理积压工作

2.检查表锁冲突

3.检查其他原因

排查 syscommittab 问题

清理期间 CPU 使用率高

运行清理的频率超过 30 分钟

反馈

其他资源

通过

排查更改跟踪自动清理问题

现象

调试和缓解

自动清理状态

1.清除已关闭

2.清理已打开，但未运行

自动清理运行，但没有进度

1.评估自动清理积压工作

2.检查表锁冲突

3.检查其他原因

排查 syscommittab 问题

清理期间 CPU 使用率高

运行清理的频率超过 30 分钟

相关内容

反馈

其他资源