排除加速数据库恢复故障

适用于: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_timeaborted_version_cleaner_last_end_time,查看中止的事务清理是否已完成。 中止事务清理完成后,oldest_aborted_transaction_id 应会移到更高的位置。 如果 oldest_aborted_transaction_idoldest_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 分钟记录一次新的日志事件。

另请参阅

后续步骤