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

適用対象: SQL Server 2019 (15.x) 以降のバージョン Azure SQL DatabaseAzure SQL Managed InstanceSql データベース

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

PVS のサイズを調べる

永続的なバージョン ストア (PVS) のサイズが予想より大きいかどうかを確認するには、sys.dm_tran_persistent_version_store_stats DMV を使います。

次の診断クエリ例は、PVS サイズが 0 より大きいすべてのデータベースの現在の PVS サイズ、クリーンアップ プロセス、およびその他の詳細に関する情報を示しています。

SQL Server と SQL Managed Instance

SELECT pvss.database_id,
       DB_NAME(pvss.database_id) AS database_name,
       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_percent_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,
       pvss.oldest_aborted_transaction_id,
       pvss.oldest_active_transaction_id,
       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
FROM sys.dm_tran_persistent_version_store_stats AS pvss
CROSS APPLY (
            SELECT SUM(size * 8.) AS total_db_size_kb
            FROM sys.master_files AS mf
            WHERE mf.database_id = pvss.database_id
                  AND
                  mf.state = 0
                  AND
                  mf.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.persistent_version_store_size_kb > 0
ORDER BY persistent_version_store_size_kb DESC;

SQL Database と Fabric SQL Database

SELECT pvss.database_id,
       DB_NAME(pvss.database_id) AS database_name,
       pvss.persistent_version_store_size_kb / 1024. / 1024 AS persistent_version_store_size_gb,
       100 * pvss.persistent_version_store_size_kb / IIF(pvss.database_id = 2, tdf.total_db_size_kb, df.total_db_size_kb) AS pvs_percent_of_database_size,
       IIF(pvss.database_id = 2, tdf.total_db_size_kb, 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,
       pvss.oldest_aborted_transaction_id,
       pvss.oldest_active_transaction_id,
       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
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
CROSS APPLY (
            SELECT SUM(size * 8.) AS total_db_size_kb
            FROM tempdb.sys.database_files
            WHERE state = 0
                  AND
                  type = 0
            ) AS tdf
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 IN (DB_ID(), 2)
ORDER BY persistent_version_store_size_kb DESC;

pvs_percent_of_database_size 列を調べて、データベースの合計サイズと比較した PVS のサイズを確認します。 一般的な PVS サイズと、アプリケーション アクティビティの一般的な期間中に表示されるベースラインの違いに注意してください。 PVS は、ベースラインより大幅に大きい場合、またはデータベース サイズの 50% に近い場合に、大きいと見なされます。

PVS のサイズが小さくない場合は、次のトラブルシューティング手順を使用して、PVS サイズが大きい理由を見つけて解決します。

ヒント

次のトラブルシューティング手順で説明する列は、このセクションの診断クエリの結果セットの列を参照します。

PVS サイズが大きい場合は、次のいずれかの理由が考えられます。

• 実行時間の長いアクティブなトランザクション
• 実行時間の長いアクティブなスナップショット スキャン
• セカンダリ レプリカで実行時間の長いクエリ
• 中止されたトランザクション

実行時間の長いアクティブなトランザクションを調べる

実行時間の長いアクティブなトランザクションは、ADR が有効になっているデータベースでの PVS のクリーンアップを妨げる可能性があります。 oldest_transaction_begin_time 列を使って、最も古いアクティブなトランザクションの開始時刻を調べます。 実行時間の長いトランザクションを検索するには、次のクエリ例を使用します。 トランザクションの継続時間と、生成されるトランザクション ログの量に対して、しきい値を設定できます。

DECLARE @LongTxThreshold int = 900;  /* The number of seconds to use as a duration threshold for long-running transactions */
DECLARE @LongTransactionLogBytes bigint = 1073741824; /* The number of bytes to use as the generated log threshold for long-running transactions */

SELECT  dbtr.database_id,
        DB_NAME(dbtr.database_id) AS database_name,
        st.session_id,
        st.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 st
ON tr.transaction_id = st.transaction_id
INNER JOIN sys.dm_exec_sessions AS sess
ON st.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 = st.transaction_id
WHERE GETDATE() >= DATEADD(second, @LongTxThreshold, tr.transaction_begin_time)
      OR
      dbtr.database_transaction_log_bytes_used >= @LongTransactionLogBytes;

セッションが特定されたら、許可されている場合はセッションの中止を検討します。 今後問題を回避するために、問題のあるトランザクションの性質を判断するためにアプリケーションを確認します。

実行時間の長いクエリのトラブルシューティングの詳細については、次を参照してください。

• SQL Server での実行速度が遅いクエリのトラブルシューティング
• Azure SQL Database での検出可能なクエリ パフォーマンス ボトルネックの種類
• SQL Server および Azure SQL Managed Instance で検出可能なクエリのパフォーマンス ボトルネックの種類

実行時間の長いアクティブなスナップショット スキャンを調べる

実行時間の長いアクティブなスナップショット スキャンは、ADR が有効になっているデータベースでの PVS のクリーンアップを妨げる可能性があります。 READ COMMITTED スナップショット分離 (RCSI) または SNAPSHOT 分離レベルを使用するステートメントは、インスタンス レベルのタイムスタンプを受け取ります。 スナップショット スキャンでは、タイムスタンプを使用して、RCSI または SNAPSHOT トランザクションのバージョン行の可視性が決定されます。 RCSI を使うすべてのステートメントには独自のタイムスタンプがあります。一方、SNAPSHOT 分離にはトランザクション レベルのタイムスタンプがあります。

これらのインスタンス レベルのトランザクション タイムスタンプは、データベース間トランザクションに昇格される可能性があるため、単一データベース トランザクションでも使用されます。 そのため、スナップショット スキャンは、同じデータベース エンジン インスタンス上の任意のデータベースでの PVS クリーンアップを妨げる可能性があります。 同様に、ADR が有効になっていない場合でも、スナップショット スキャンを実行すると、tempdbでのバージョン ストアのクリーンアップを防ぐことができます。 その結果、SNAPSHOT または RCSI を使う実行時間の長いトランザクションが存在する場合、PVS のサイズが大きくなる可能性があります。

pvs_off_row_page_skipped_min_useful_xts 列には、長いスナップショット スキャンが原因でクリーンアップ中にスキップされたページの数が表示されます。 この列に大きな値が表示されている場合は、長いスナップショット スキャンによって PVS クリーンアップが妨げられていることを意味します。

実行時間の長い SNAPSHOT または RCSI トランザクションを含むセッションを検索するには、次のクエリ例を使用します。

SELECT sdt.transaction_id,
       sdt.transaction_sequence_num,
       s.database_id,
       s.session_id,
       s.login_time,
       GETDATE() AS query_time,
       s.host_name,
       s.program_name,
       s.login_name,
       s.last_request_start_time
FROM sys.dm_tran_active_snapshot_database_transactions AS sdt
INNER JOIN sys.dm_exec_sessions AS s
ON sdt.session_id = s.session_id;

PVS クリーンアップの遅延を防ぐには:

• 可能であれば、PVS のクリーンアップを遅らせる長いアクティブなトランザクション セッションを強制終了することを検討してください。
• 実行時間の長いクエリを調整して、クエリの継続時間を短くします。
• アプリケーションを調べて、問題のあるアクティブなスナップショット スキャンの性質を確認します。 PVS のクリーンアップを遅らせている実行時間の長いクエリでは、SNAPSHOT や RCSI ではなく、READ COMMITTED などの別の分離レベルを検討します。 この問題は、SNAPSHOT 分離レベルでより頻繁に発生します。
• Azure SQL Database エラスティック プールでは、同じプール内の他のデータベースで PVS クリーンアップの遅延を回避するために、SNAPSHOT 分離または RCSI を使用して実行時間の長いトランザクションがあるデータベースをエラスティック プールから移動することを検討してください。

セカンダリ レプリカで実行時間の長いクエリを調べる

データベースにセカンダリ レプリカがある場合は、セカンダリの低基準値が増えているかどうかを調べます。

pvs_off_row_page_skipped_low_water_mark 列の大きな値は、セカンダリ レプリカで実行時間の長いクエリが実行されるため、クリーンアップの遅延を示している可能性があります。 PVS クリーンアップを遅らせることに加えて、セカンダリ レプリカで長時間実行されるクエリは、ゴースト クリーンアップも遅延させる可能性があります。

プライマリ レプリカで次のクエリ例を使用すると、セカンダリ レプリカで実行時間の長いクエリが PVS クリーンアップを妨げている可能性があるかどうかを確認できます。 書き込みワークロードがプライマリ レプリカで実行されているが、low_water_mark_for_ghosts 列の値がサンプル クエリの 1 回の実行から次の実行に増加していない場合、PVS とゴースト クリーンアップはセカンダリ レプリカで実行時間の長いクエリによって保持される可能性があります。

SQL Server と SQL Managed Instance

SELECT database_id,
       DB_NAME(database_id) AS database_name,
       low_water_mark_for_ghosts,
       synchronization_state_desc,
       synchronization_health_desc,
       is_suspended,
       suspend_reason_desc,
       secondary_lag_seconds
FROM sys.dm_hadr_database_replica_states
WHERE is_local = 1
      AND
      is_primary_replica = 1;

詳細については、sys.dm_hadr_database_replica_statesの low_water_mark_for_ghosts 列の説明を参照してください。

SQL Database と Fabric SQL Database

SELECT database_id,
       DB_NAME(database_id) AS database_name,
       low_water_mark_for_ghosts,
       synchronization_state_desc,
       synchronization_health_desc,
       is_suspended,
       suspend_reason_desc,
       secondary_lag_seconds
FROM sys.dm_database_replica_states
WHERE is_local = 1
      AND
      is_primary_replica = 1;

詳細については、sys.dm_database_replica_statesの low_water_mark_for_ghosts 列の説明を参照してください。

読み取り可能な各セカンダリ レプリカに接続し、実行時間の長いクエリを持つセッションを見つけ、許可されている場合はセッションを終了することを考慮します。 詳細については、「低速クエリを検索する」を参照してください。

中止されたトランザクションの数の多さを調べる

aborted_version_cleaner_start_time 列と aborted_version_cleaner_end_time 列を調べて、最後に中止されたトランザクションのクリーンアップが完了したかどうかを確認します。 中止となったトランザクションの消去が完了した後は、oldest_aborted_transaction_id の値が上位に移動するはずです。 oldest_aborted_transaction_id が oldest_active_transaction_idよりもはるかに小さく、current_abort_transaction_count 値が大きい場合は、PVS クリーンアップを妨げる古い中止されたトランザクションがある可能性があります。

多数の中止されたトランザクションによる PVS クリーンアップの遅延を解決するには、次の点を考慮してください。

• SQL Server 2022 (16.x) を使用している場合は、ADR Cleaner Thread Count サーバー構成の値を大きくします。 詳細については、「サーバー構成: ADR クリーナー スレッド数の」を参照してください。
• 可能な場合は、ワークロードを停止して、バージョン クリーナーの処理が進むようにします。
• アプリケーションを確認して、トランザクション中止率の高い問題を特定して解決します。 中止は、高いデッドロック率、重複キー、制約違反、またはクエリのタイムアウトによって発生する可能性があります。
• ワークロードを最適化して、PVS クリーナーに必要なオブジェクト レベルまたはパーティション レベルの IX ロックと互換性のないロックを減らします。 詳細については、「ロックの互換性」を参照してください。
• SQL Server をお使いの場合は、PVS のサイズを制御するための緊急手段として ADR を無効にします。 「ADR を無効にする」をご覧ください。
• SQL Server を使用していて、中止されたトランザクションのクリーンアップが最近正常に完了していない場合は、エラー ログで VersionCleaner の問題を報告するメッセージを確認します。
• クリーンアップが完了した後も PVS のサイズが期待したほど減らない場合は、pvs_off_row_page_skipped_oldest_aborted_xdesid 列を調べます。 大きな値は、中止されたトランザクションの行バージョンによって領域がまだ使われていることを示します。

PVS サイズを制御する

大量の OLTP などの大量の DML ステートメント (INSERT、UPDATE、DELETE、MERGE) を持つワークロードがあり、PVS のサイズが大きい場合は、PVS サイズを制御するために ADR Cleaner Thread Count サーバー構成の値を増やす必要があります。 詳細については、「サーバーの構成: ADR クリーナー スレッド数」を参照してください。これは SQL Server 2022 (16.x) 以降で使用できます。

SQL Server 2019 (15.x) では、または ADR Cleaner Thread Count 構成の値を大きくしても PVS のサイズを十分に小さくできない場合、ワークロードでは、領域を再利用するために PVS クリーンアップ プロセスの残り/回復の期間が必要になる場合があります。

ワークロードの間またはメンテナンス期間中に PVS クリーンアップ プロセスを手動でアクティブにするには、システム ストアド プロシージャ sys.sp_persistent_version_cleanup を使います。

次に例を示します。

EXEC sys.sp_persistent_version_cleanup [WideWorldImporters];

クリーンアップエラーをキャプチャします

SQL Server 2022 (16.x) 以降では、注目すべき PVS クリーンアップ メッセージがエラー ログに記録されます。 クリーンアップの統計は、tx_mtvc2_sweep_stats 拡張イベントによっても報告されます。

関連コンテンツ

• 高速データベース復旧
• 高速データベース復旧の管理
• sys.dm_tran_persistent_version_store_stats
• sys.sp_persistent_version_cleanup
• sys.dm_tran_aborted_transactions