加速資料庫復原疑難排解

  • 發行項

適用於：SQL Server 2019 (15.x) Azure SQL DatabaseAzure SQL 受控執行個體

本文可協助系統管理員診斷 SQL Server 2019 (15.x) 及更新版本、Azure SQL 受控執行個體和 Azure SQL Database 中加速資料庫復原 (ADR) 的問題。

檢查持續版本存放區 (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。 使用下列步驟作為大型 PVS 的疑難排解協助。

  2. 已啟用 ADR 的任何資料庫中，作用中、長時間執行的交易可能會防止清除 PVS。 根據交易識別碼，透過查詢 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 中 (或是 tempdb 版本存放區中沒有 ADR 時) 的記錄。 因此，由於此版本追蹤，使用 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 (或 online_index_min_transaction_timestamp，若線上 PVS 正在延誤的話)，並根據其結果，檢查資料行 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 清除程序

對於具有更新/刪除高交易計數的資料庫環境，不建議使用 ADR (例如大量 OLTP，PVS 清除程序沒有回收空間的待用/復原時間)。

若要在工作負載之間或維護期間手動啟動 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 分鐘記錄一次新的記錄事件。

另請參閱

下一步