加速資料庫復原疑難排解

文章
05/04/2023

適用於：SQL Server 2019 (15.x) Azure SQL Database Azure 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();

檢查 pvs_pct_of_database_size 大小，請注意與應用程式活動其他期間基準相較之下的一般差異。若 PVS 遠大於基準，或是其接近資料庫大小的 50%，該 PVS 便是大型 PVS。使用下列步驟作為大型 PVS 的疑難排解協助。

已啟用 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 );

如果已識別工作階段，請考慮在允許的情況下終止工作階段。此外，請檢閱應用程式，以判斷有問題之作用中交易的性質。

如需針對長時間執行的查詢進行疑難排解的詳細資訊，請參閱：

持續性版本清除可能會因為長時間作用中的快照集掃描而保留。使用讀取認可快照集隔離 (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 隔離等級，將資料庫移出具有長時間執行查詢的彈性集區。
當 PVS 大小因為主要或次要複本上長時間執行交易而成長時，請調查長時間執行查詢並解決瓶頸。 sys.dm_tran_aborted_transactions DMV 會顯示所有中止的交易。如需詳細資訊，請參閱 sys.dm_tran_aborted_transactions (Transact-SQL)。 nest_aborted 資料行表示交易已認可，但有部分交易已中止 (儲存點或巢狀交易)，這可能會封鎖 PVS 清除程序。
若資料庫是可用性群組的一部分，請檢查 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 版本清除，也可以防止清除準刪除記錄。
檢查 min_transaction_timestamp (或 online_index_min_transaction_timestamp，若線上 PVS 正在延誤的話)，並根據其結果，檢查資料行 transaction_sequence_num 的 sys.dm_tran_active_snapshot_database_transactions 來尋找包含正在延誤 PVS 清除舊快照集交易的工作階段。
若上述項目皆不適用，這表示清除是由中止的交易延誤。請檢查最後一次的 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 功能。
若中止交易尚未成功完成，請檢查錯誤記錄檔，尋找報告 VersionCleaner 問題的訊息。
監視 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 分鐘記錄一次新的記錄事件。

分享方式：

加速資料庫復原疑難排解

檢查持續版本存放區 (PVS)

手動啟動 PVS 清除程序

擷取清除失敗

另請參閱

下一步

意見反映

更多資源