排除加速数据库恢复故障

  • 项目

适用于： SQL Server 2019 (15.x) Azure SQL 数据库Azure SQL 托管实例

本文可帮助管理员诊断 SQL Server 2019 (15.x) 及更高版本中加速数据库恢复 (ADR)、Azure SQL 托管实例和 Azure SQL 数据库的问题。

检查永久性版本存储 (PVS)

利用 sys.dm_tran_persistent_version_store_stats DMV 确定加速数据库恢复 (ADR) PVS 的大小增长是否超出预期，然后确定哪个因素阻止了永久性版本存储 (PVS) 的清理。

以下示例脚本中包含 sys.dm_tran_persistent_version_store_stats.pvs_off_row_page_skipped_oldest_aborted_xdesid 列，此列是在 SQL Server 2022 (16.x) 中添加的，其中包含由于最早的中止事务而跳过回收的页数。 如果版本清理器运行缓慢或失效，这将反映必须为中止的事务保留的页数。

示例查询显示有关清理过程的所有信息，并显示当前的 PVS 大小、最早中止的事务和其他详细信息：

SELECT
 db_name(pvss.database_id) AS DBName,
 pvss.persistent_version_store_size_kb / 1024. / 1024 AS persistent_version_store_size_gb,
 100 * pvss.persistent_version_store_size_kb / df.total_db_size_kb AS pvs_pct_of_database_size,
 df.total_db_size_kb/1024./1024 AS total_db_size_gb,
 pvss.online_index_version_store_size_kb / 1024. / 1024 AS online_index_version_store_size_gb,
 pvss.current_aborted_transaction_count,
 pvss.aborted_version_cleaner_start_time,
 pvss.aborted_version_cleaner_end_time,
 dt.database_transaction_begin_time AS oldest_transaction_begin_time,
 asdt.session_id AS active_transaction_session_id,
 asdt.elapsed_time_seconds AS active_transaction_elapsed_time_seconds,
 pvss.pvs_off_row_page_skipped_low_water_mark,
 pvss.pvs_off_row_page_skipped_min_useful_xts,
 pvss.pvs_off_row_page_skipped_oldest_aborted_xdesid -- SQL Server 2022 only
FROM sys.dm_tran_persistent_version_store_stats AS pvss
CROSS APPLY (SELECT SUM(size*8.) AS total_db_size_kb FROM sys.database_files WHERE [state] = 0 and [type] = 0 ) AS df 
LEFT JOIN sys.dm_tran_database_transactions AS dt
ON pvss.oldest_active_transaction_id = dt.transaction_id
   AND
   pvss.database_id = dt.database_id
LEFT JOIN sys.dm_tran_active_snapshot_database_transactions AS asdt
ON pvss.min_transaction_timestamp = asdt.transaction_sequence_num
   OR
   pvss.online_index_min_transaction_timestamp = asdt.transaction_sequence_num
WHERE pvss.database_id = DB_ID();

  1. 检查 pvs_pct_of_database_size 大小，注意与典型大小的任何差异（与其他应用程序活动期间的基线相比）。 如果 PVS 明显比基线大，或者接近数据库大小的 50%，则认为 PVS 大。 将以下步骤用作大型 PVS 的故障排除帮助。

  2. 在启用 ADR 的任何数据库中，活动的长期运行的事务可能会阻止 PVS 的清理。 基于事务 ID 查询 sys.dm_tran_database_transactions，检索 oldest_active_transaction_id 并查看此事务是否已长期处于活动状态。 使用类似以下示例的查询检查长时间运行的活动事务，该查询声明变量以设置持续时间或日志量的阈值：

    DECLARE @longTxThreshold int = 1800; --number of seconds to use as a duration threshold for long-running transactions
DECLARE @longTransactionLogBytes bigint = 2147483648; --number of bytes to use as a log amount threshold for long-running transactions

SELECT
    dbtr.database_id, 
    transess.session_id,  
    transess.transaction_id, 
    atr.name, 
    sess.login_time,  
    dbtr.database_transaction_log_bytes_used, 
    CASE
       WHEN getdate() >= dateadd(second, @longTxThreshold, tr.transaction_begin_time) then 'DurationThresholdExceeded' 
       WHEN dbtr.database_transaction_log_bytes_used >= @longTransactionLogBytes then 'LogThresholdExceeded' 
       ELSE 'unknown' END AS Reason 
  FROM
    sys.dm_tran_active_transactions AS tr  
    INNER JOIN sys.dm_tran_session_transactions AS transess on tr.transaction_id = transess.transaction_id  
    INNER JOIN sys.dm_exec_sessions AS sess on transess.session_id = sess.session_id 
    INNER JOIN sys.dm_tran_database_transactions AS dbtr on tr.transaction_id = dbtr.transaction_id 
    INNER JOIN sys.dm_tran_active_transactions AS atr on atr.transaction_id = transess.transaction_id 
WHERE transess.session_id <> @@spid AND 
    ( getdate() >= dateadd(second, @longTxThreshold, tr.transaction_begin_time) OR
      dbtr.database_transaction_log_bytes_used >= @longTransactionLogBytes );

    确定会话后，请考虑将其终止（如果允许）。 此外，查看应用程序以确定有问题的活动事务的性质。

    有关排查长期运行的查询故障的详细信息，请参阅：

  1. 永久性版本清理可能会由于长时间运行的活动快照扫描而被延搁。 使用读取提交的快照隔离 (RCSI) 或 SNAPSHOT 隔离级别的语句接收实例级时间戳。 快照扫描使用时间戳来确定启用了加速数据库恢复的 PVS 中 RCSI 或 SNAPSHOT 事务的行可见性。 使用 RCSI 的每个语句都有自己的时间戳，而 SNAPSHOT 隔离具有事务级时间戳。 即使在单一数据库事务中也会使用这些实例级事务时间戳，因为此事务可能会提升为跨数据库事务。 因此，快照扫描可以防止清理 ADR PVS 中的记录，或者当 ADR 不存在时，清除 tempdb 版本存储中的记录。 因此，由于此版本跟踪，使用 SNAPSHOT 或 RCSI 的长期运行事务可能会导致 ADR PVS 延迟清理实例中的数据库，从而导致 ADR PVS 的大小增加。

    在本文前面部分的原始故障排除查询中，pvs_off_row_page_skipped_min_useful_xts 值显示由于长期运行的快照扫描而跳过回收的页数。 如果 pvs_off_row_page_skipped_min_useful_xts 显示的值大于正常值，则意味着长时间运行的快照扫描阻止了 PVS 清理。

    此示例查询可用于确定有问题的会话：

    SELECT 
    snap.transaction_id, snap.transaction_sequence_num, session.session_id, session.login_time, 
    GETUTCDATE() as [now], session.host_name, session.program_name, session.login_name, session.last_request_start_time
FROM sys.dm_tran_active_snapshot_database_transactions AS snap
INNER JOIN sys.dm_exec_sessions AS session ON snap.session_id = session.session_id  
ORDER BY snap.transaction_sequence_num asc;

    防止延迟 PVS 清理：

    1. 如果可能，请考虑终止延迟 PVS 清理的长期活动的事务会话。 启用 ADR 的任何数据库中长期运行的事务可能会延迟 ADR PVS 清理。
    2. 优化长期运行的查询，减少所需的查询持续时间和锁定。 有关详细信息和指南，请参阅了解并解决 SQL Server 阻塞和了解并解决 Azure SQL 数据库阻塞问题。
    3. 查看应用程序以确定有问题的活动快照扫描的性质。 对于延迟 ADR PVS 清理的长期运行的查询，请考虑使用不同的隔离级别（例如 READ COMMITTED），而不是 SNAPSHOT 或 READ COMMITTED SNAPSHOT。 使用 SNAPSHOT 隔离级别会更频繁地引发此问题。
    4. SQL Server、Azure SQL 托管实例和 Azure SQL 数据库的弹性池中可能发生此问题，但单一 Azure SQL 数据库中不会发生此问题。 在 Azure SQL 数据库弹性池中，考虑使用 READ COMMIT SNAPSHOT 或 SNAPSHOT 隔离级别，从具有长期运行的查询的弹性池中移出数据库。

  2. 当 PVS 大小因主要副本或辅助副本上长时间运行的事务而增长时，请调查长时间运行的查询并解决瓶颈。 sys.dm_tran_aborted_transactions DMV 显示所有中止的事务。 有关详细信息，请参阅 sys.dm_tran_aborted_transactions (Transact-SQL)。 nest_aborted 列指示事务已提交，但存在中止的部分（保存点或嵌套事务），这可能会阻碍 PVS 清理过程。

  3. 如果数据库属于可用性组，请检查 secondary_low_water_mark。 这与 sys.dm_hadr_database_replica_states 报告的 low_water_mark_for_ghosts 相同。 查询 sys.dm_hadr_database_replica_states，以查看其中一个副本是否包含此值，因为这也会阻止 PVS 清理操作。 版本清理由于可读辅助副本上的读取查询而被延搁。 本地 SQL Server 和 Azure SQL DB 都支持可读辅助副本。 在 sys.dm_tran_persistent_version_store_stats DMV 中，pvs_off_row_page_skipped_low_water_mark 还可以指示辅助副本延迟。 有关详细信息，请参阅 sys.dm_tran_persistent_version_store_stats。

    解决方案与快照扫描延搁相同。 转到辅助副本，找到发出长期运行的查询的会话，并考虑终止会话（如果允许）。 请注意，辅助副本延搁不仅会影响 ADR 版本清理，还会阻止虚影记录清理。

  4. 检查 min_transaction_timestamp（如果联机 PVS 被阻止，则为 online_index_min_transaction_timestamp），并根据对 transaction_sequence_num 列执行的 sys.dm_tran_active_snapshot_database_transactions 检查来查找包含阻止 PVS 清理的旧快照事务的会话。

  5. 如果以上均不适用，则表示清理操作被中止事务控制。 最后检查一次 aborted_version_cleaner_last_start_time 和 aborted_version_cleaner_last_end_time，查看中止的事务清理是否已完成。 中止事务清理完成后，oldest_aborted_transaction_id 应会移到更高的位置。 如果 oldest_aborted_transaction_id 比 oldest_active_transaction_id 小得多，并且 current_abort_transaction_count 具有更大的值，则早期中止的事务会阻止 PVS 清理。 收件地址：

    • 如果可能，请停止工作负载，让版本清理器取得进展。
    • 优化工作负载以减少对象级锁定使用情况。
    • 查看应用程序以查看任何高事务中止问题。 死锁、重复键或其他约束冲突可能会触发高中止率。
    • 如果在 SQL Server 上，请禁用 ADR 作为仅紧急步骤来控制 PVS 大小和中止事务编号。 请参阅禁用 ADR 功能。

  6. 如果中止事务最近未成功完成，请检查错误日志中是否存在报告 VersionCleaner 问题的消息。

  7. 监视 SQL Server 错误日志中是否存在“PreallocatePVS”条目。 如果存在“PreallocatePVS”条目，则意味着可能需要提高 ADR 功能为后台任务预分配页面，因为当 ADR 后台线程预分配足够的页面并且前台 PVS 分配的百分比接近 0 时，可以提高性能。 可以使用 sp_configure 'ADR Preallocation Factor' 来增加此数量。 有关详细信息，请参阅 ADR 预分配因素服务器配置选项。

手动启动 PVS 清理进程

对于更新/删除事务量较高（例如大容量 OLTP）并且 PVS 清理进程没有留出一段休息/恢复时间来回收空间的数据库环境，不建议使用 ADR。

若要在工作负载之间或维护时段内手动激活 PVS 清理进程，请使用系统存储过程 sys.sp_persistent_version_cleanup。

EXEC sys.sp_persistent_version_cleanup [database_name];

例如，

EXEC sys.sp_persistent_version_cleanup [WideWorldImporters];

捕获清理失败

从 SQL Server 2022 (16.x) 开始，SQL Server 在 SQL Server 错误日志中记录 ADR PVS 清理行为。 通常，这将导致每 10 分钟记录一次新的日志事件。

另请参阅

后续步骤