高速データベース復旧のトラブルシューティング

  • [アーティクル]

適用対象: SQL Server 2019 (15.x) Azure SQL DatabaseAzure SQL Managed Instance

この記事は、管理者が SQL Server 2019 (15.x) 以降、Azure SQL Managed Instance、および Azure SQL Database の高速データベース復旧 (ADR) に関する問題を診断するのに役立ちます。

永続的なバージョン ストア (PVS) を調べる

sys.dm_tran_persistent_version_store_stats DMV を利用し、高速データベース復旧 (ADR) PVS のサイズが予想以上に大きくなっているかどうかを調べてから、永続的なバージョン ストア (PVS) のクリーンアップを妨げている要因を特定します。

次のサンプル スクリプトには、SQL Server 2022 (16.x) で追加された列 sys.dm_tran_persistent_version_store_stats.pvs_off_row_page_skipped_oldest_aborted_xdesidが含まれており、最も古い中止されたトランザクションのために再利用のためにスキップされたページの数が含まれています。 バージョン クリーナーが遅い、または無効になっている場合は、中止されたトランザクションに保持する必要があるページの数が反映されます。

サンプル クエリでは、クリーンアップ プロセスに関するすべての情報と、現在の 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 のトラブルシューティングに役立ちます。

  2. ADR が有効になっているデータベースで実行時間の長いアクティブなトランザクションを実行すると、PVS のクリーンアップを防ぐことができます。 sys.dm_tran_database_transactions を取得し、トランザクション ID に基づいて 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 Database のブロックの問題の概要と解決策」を参照してください。
    3. アプリケーションを調べて、問題のあるアクティブなスナップショット スキャンの性質を確認します。 ADR PVS のクリーンアップを遅らせる実行時間の長いクエリでは、SNAPSHOT や READ COMMITTED SNAPSHOT の代わりに、READ COMMITTED などの別の分離レベルを検討してください。 この問題は、SNAPSHOT 分離レベルでより頻繁に発生します。
    4. この問題は、Azure SQL Database の SQL Server、Azure SQL Managed Instance、エラスティック プールで発生する可能性がありますが、シングルトン Azure SQL データベースでは発生しません。 Azure SQL Database エラスティック プールでは、READ COMMIT SNAPSHOT または SNAPSHOT 分離レベルを使用して、実行時間の長いクエリがあるエラスティック プールからデータベースを移動することを検討してください。

  2. プライマリまたはセカンダリ レプリカでの実行時間の長いトランザクションが原因で PVS のサイズが増えている場合は、実行時間の長いクエリを調べて、ボトルネックに対処します。 sys.dm_tran_aborted_transactions DMV には、中止されたトランザクションがすべて表示されます。 詳しくは、「ys.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. "PreallocatePVS" エントリの SQL Server エラー ログを監視します。 "PreallocatePVS" エントリがある場合は、ADR バックグラウンド スレッドによって十分なページが事前に割り当てられ、フォアグラウンド PVS による割り当ての割合が 0 に近くなると、パフォーマンスが向上する可能性があるので、バックグラウンド タスク用にページを事前に割り当てる ADR の能力を増強することが必要な場合があることを意味します。 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 は ADR PVS クリーンアップ動作を SQL Server エラー ログに記録します。 これを有効にすると、通常、10 分ごとに新しいログ イベントが記録されます。

関連項目

次のステップ