SQL Serverでディストリビューション エージェントの SubscriptionStreams パラメーターを構成してトラブルシューティングする方法

  • [アーティクル]

元の製品バージョン: SQL Server (サポートされているすべてのバージョン)
元の KB 番号: 953199

この記事では、パラメーターの詳細、このパラメーター SubscriptionStreamsを使用する場合のベスト プラクティス、および関連するトラブルシューティングについて説明します。

概要

パラメーター SubscriptionStreams を使用して、接続の数を制御できます。 Microsoft SQL Server のトランザクション レプリケーションでは、 パラメーターを使用して、ディストリビューション エージェントがサブスクライバーに並行して変更のバッチを適用するために使用する複数の接続を有効にすることができます。 この操作により、レプリケーションのスループットが大幅に向上します。 同時に、ディストリビューション エージェントは、ディストリビューション エージェントが単一の接続を使用して変更を適用する場合と同じトランザクション特性の多くを維持できます。 いずれかの接続が実行またはコミットに失敗した場合、すべての接続が現在のバッチを中止し、エージェントは単一のストリームを使用して失敗したバッチを再試行します。 この再試行フェーズが完了する前に、サブスクライバーで一時的なトランザクションの不整合が発生する可能性があります。 失敗したバッチが正常にコミットされると、サブスクライバーはトランザクション整合性の状態に戻されます。

パラメーター SubscriptionStreamsに 2 以上の値を指定すると、サブスクライバーでトランザクションを受信する順序が、パブリッシャーで行われた順序と異なる場合があります。 この動作によって同期中に制約違反が発生する場合は、 オプションを NOT FOR REPLICATION 使用して、同期中に制約の適用を無効にする必要があります。 詳細については、「 同期でのトリガーと制約の制御動作」を参照してください。

SubscriptionStreams を有効にする前に考慮すべき要素

SubscriptionStreams 主にディストリビューターからサブスクライバーへの待機時間が処理されるため、 を選択する前に SubscriptionStreams、ディストリビューターからサブスクライバーへの待機時間が発生していることを確認してください。 レプリケーション モニターでトレーサー トークンを使用することも、SQLServer:Replication Dist などのカウンターをパフォーマンス モニターすることもできます。>Dist:Delivery Latency:待機時間のレベルを把握します。

ディストリビューターからサブスクライバーへの待機時間は、次のような多くの理由によって発生する可能性があります。ただし、これらに限定されません。

  • ディストリビューターまたはサブスクライバーでのブロック
  • ディストリビューターまたはサブスクライバーのボトルネック (低速ディスク ドライブ、低速ネットワーク帯域幅、古い統計情報など)
  • パブリッシャーからの一括トランザクション
  • パブリッシャーからの受信トランザクションのレートが高すぎます
  • サブスクライブされたデータベース内のトリガーまたは不要なインデックス

データベース管理者 (DBA) は、呼び出しを受け、それらを支援するかどうかを SubscriptionStreams テストする必要があります。 たとえば、サブスクライバーでブロックする場合、同時接続の数を増やすと役に立ちませんが、状況が悪化する可能性があります。 Publisher からの受信トランザクション レートが高すぎて、ディストリビューション エージェントの 1 つのスレッドが受信負荷に対応できないと感じる場合は、パラメーターSubscriptionStreamsの値を =2 に>増やすことを検討できます。 また、ネットワークの速度が低下し、ディスクの状態が遅くなる場合にも役立ちます。 理想的には、このパラメーターの最大値は 64 ですが、推奨される値 (または開始する適切な値) は、Destination (サブスクライバー) の物理プロセッサの数と等しくなります。

SubscriptionStreams パラメーターを構成する方法

SubscriptionStreamsは、レプリケーション モニターのディストリビューション エージェント プロファイルに表示されないパラメーターの 1 つです。 このエージェント パラメーターの値は、sp_addsubscription (Transact-SQL) を使用@subscriptionstreamsして指定するか、次の手順を使用してディストリビューション エージェント ジョブ コマンド セクションに追加できます。

  1. レプリケーション モニターを開き、[ My Publisher] を展開し、左側のウィンドウ ウィンドウでパブリケーションを選択します。 右側のウィンドウの [ すべてのサブスクリプション ] セクションに、このパブリケーションのすべてのサブスクライバーの一覧が表示されます。

  2. パラメーター SubscriptionStreams を有効にするサブスクライバーを右クリックし、[詳細の表示] を選択 します。 新しいウィンドウが表示され、ディストリビューション エージェントセッションの詳細が表示されます。

  3. この新しいウィンドウで、上部のメニュー バーで [アクション] を選択し、[ジョブのプロパティ] ディストリビューション エージェント選択します。 これにより、ディストリビューション エージェントの [ジョブのプロパティ] ウィンドウが開きます。

  4. 左側のウィンドウで [ ステップ ] を選択し、右側のウィンドウウィンドウで [ エージェントの実行 ] を選択し、[編集] を選択 します。 新しいウィンドウが表示されます。

  5. コマンド セクションの末尾 (右端) までスクロールし、このパラメーター -SubscriptionStreams 6 を追加します。

  6. 設定を保存し、ディストリビューション エージェント ジョブを再起動します。 変更を実装するには、ディストリビューション エージェントの再起動が必要です。

注:

上の例では、 SubscriptionStreams を 6 に設定します。つまり、サブスクライバーでディストリビューション エージェントに対して 6 つの並列接続を探しています。 この数値は、環境とテストに従って設定できます。

ストリームの数の決定

パラメーター SubscriptionStreamsを使用すると、パフォーマンスが向上することがあります。 改善がある場合、改善は名目上の可能性があります。 を使用 SubscriptionStreamsして、市場の各ディスク サブシステムが提供するパフォーマンス向上の種類を特定するのは困難です。 そのため、運用環境をシミュレートするテスト環境を準備することをお勧めします。 さまざまな構成値と を使用 SubscriptionStreams しないシナリオを使用して、 SubscriptionStreams使用するシナリオをテストできます。

を使用 SubscriptionStreamsして取得できるパフォーマンスの向上を判断するには、パブリケーションとサブスクリプションに対してロード テストを実行することをお勧めします。 ディスク サブシステムの予想スループットを把握するには、パフォーマンス ベースライン テストを実行する必要があります。 各テストを実行する前に、多くの変更を適用してパブリッシャーで負荷を作成します。 読み込みを作成するときは、ディストリビューション エージェントが実行されないようにします。 レプリケーションに十分な待機時間がある場合は、ディストリビューション エージェントを実行して、次の構成のパフォーマンスをテストします。

  • パラメーター SubscriptionStreamsは使用しないでください。
  • の SubscriptionStreams 値をサーバー上のプロセッサの数に等しく設定します。 たとえば、サーバーに 8 つのプロセッサがある場合は、 の SubscriptionStreams 値を 8 に設定します。
  • に異なる値を SubscriptionStreams 指定して、最適な構成を取得します。

テストを実行すると、ディストリビューション エージェントの次のパフォーマンス カウンターを監視できます。

  • Dist: 配信された Cmds/sec
  • Dist: 配信待機時間

パラメーター SubscriptionStreams を指定した後のディストリビューション エージェントの動作

ディストリビューション エージェントでは、 でSubscriptionStreams指定したセッション/接続の数が保持されます。 ディストリビューション エージェントでは、これらのセッションを使用してサブスクライバーに変更を適用します。

ただし、指定SubscriptionStreamsしてディストリビューション エージェントをしばらく実行すると、ディストリビューション エージェントは 1 つのセッションのみを使用してサブスクライバーに変更を適用するように切り替えることができます。

ディストリビューション エージェントが 1 つのセッションのみを使用するように切り替える理由

ディストリビューション エージェントは、多くの理由で 1 つのセッションのみを使用するように切り替えることができます。 最も一般的な理由は次のとおりです。

  • ディストリビューション エージェントが変更を適用すると、いずれかのセッションでエラーが発生します。

    たとえば、ディストリビューション エージェントは、1 つのセッションを使用して子テーブルに行を挿入します。 これが、別のセッションを使用して、ディストリビューション エージェントが親テーブルに対応する行を挿入する前に発生した場合、外部キー制約違反によってエラー メッセージが発生します。

  • ブロッキング モニター スレッドはブロックを検出します。 ブロックは、次のいずれかの理由で発生する可能性があります。

    • ディストリビューション エージェントは、異なるセッションをINSERTUPDATE使用してサブスクライバーのテーブルに対して および 操作を実行します。 テーブルに一意の非クラスター化インデックスが含まれている場合、ディストリビューション エージェントがテーブルのインデックス キーを更新するときに、2 つのセッション間でブロックが発生する可能性があります。

    • サブスクライバーでは、ディストリビューション エージェントは複数のテーブルに対してデータ操作言語 (DML) ステートメントを実行します。 これらのテーブルにインデックス付きビューが定義されている場合、インデックス付きビューが共有インデックス キーを更新するときに、2 つのセッション間でブロックが発生する可能性があります。

    • ディストリビューション エージェントは、1 つのセッションを使用してサブスクライバーのテーブルに対して DML ステートメントを実行します。 DML トリガーは、このテーブルで定義されます。 DML トリガーは、別のセッションを使用して更新されている別のテーブルで DML ステートメントを実行します。 このような状況では、2 つのセッション間でブロックが発生する可能性があります。

サブスクライバー データベースでは、次のデータベース オブジェクトを使用しないことを強くお勧めします。

  • 外部キー制約
  • 一意の非クラスター化インデックス
  • インデックス付きビュー
  • セッション間でブロックを引き起こす可能性がある DML トリガー

ディストリビューション エージェントが 1 つのセッションのみを使用するように切り替えたかどうかを判断する方法

これを行うには、次のいずれかの方法を使用します。

注:

方法 1 を使用して、ディストリビューション エージェントが 1 つのセッションに切り替えされていないことを確認できますが、方法 2 または方法 3 を使用して、ディストリビューション エージェントが 1 つのセッションの使用に切り替わったことを確認する必要があります。

  • 方法 1

    サブスクリプション データベースへの接続セッションについて、動的管理ビュー (DMV) sys.dm_exec_sessions を照会します。 接続セッションが 1 つだけ表示される場合、ディストリビューション エージェントが 1 つのセッションを使用するように切り替えている可能性があります。 複数の接続セッションが表示される場合、ディストリビューション エージェントは指定された数のセッションを引き続き使用しています。

    ディストリビューション エージェントが 1 つのセッションを使用してに切り替わったことを確認するには、方法 2 または方法 3 を使用します。

  • 方法 2

    ディストリビューション データベース内のテーブル msdistribution_historyの列commentsに対してクエリを実行します。 クエリの結果に次のエントリが含まれている場合、ディストリビューション エージェントは 1 つのセッションを使用するように切り替えられます。

    プロセスは、マルチストリーミング モードで最後のバッチを完了できませんでした。単一接続モードにリセットされ、操作を再試行しています。

  • 方法 3

    ディストリビューション エージェントの出力ファイルを調べます。 出力ファイルにメソッド 2 と同じエラー メッセージが含まれている場合、ディストリビューション エージェントは 1 つのセッションのみを使用するように切り替えました。

    次の出力ファイルの例を示します。

    Date/Time 100 transaction(s) with 1181 command(s) were delivered. 
Date/Time 100 transaction(s) with 2672 command(s) were delivered. 
Date/Time Bucket 6 aborted the wait for Ready To Commit event, deadlock found between spid 117 and 114 
Date/Time Bucket 1 aborted the wait for Ready To Commit event, deadlock found between spid 117 and 114 
Date/Time Bucket 3 aborted the wait for Ready To Commit event, deadlock found between spid 117 and 114 
Date/Time Bucket 0 aborted the wait for Ready To Commit event, deadlock found between spid 117 and 114 
Date/Time Bucket 5 aborted the wait for Ready To Commit event, deadlock found between spid 117 and 114 
Date/Time Bucket 2 aborted the wait for Ready To Commit event, deadlock found between spid 117 and 114 
Date/Time Bucket 7 aborted the wait for Ready To Commit event, deadlock found between spid 117 and 114 
Date/Time Bucket 4 aborted the wait for Ready To Commit event, due to thread shutdown event 
... 
Date/Time Number of subscription streams has been reset from 8 to 1, state 4. 
Date/Time Disconnecting from Subscriber 
SQLInstance 
Date/Time Disconnecting from Subscriber 
SQLInstance 

Date/Time Disconnecting from Subscriber 
SQLInstance 

Date/Time Disconnecting from Subscriber 
SQLInstance 

Date/Time Disconnecting from Subscriber 
SQLInstance 

Date/Time Disconnecting from Subscriber 
SQLInstance 

Date/Time Disconnecting from Subscriber 
SQLInstance 

Date/Time Disconnecting from Subscriber 
SQLInstance 


Date/Time Connecting to Subscriber 
SQLInstance 

Date/Time The process failed to complete last batch in multi-streaming mode, it has been reset to single connection mode and is retrying the operation. 
Date/Time 21 transaction(s) with 390 command(s) were delivered.

1 つのセッションのみを使用するように切り替えるディストリビューション エージェントのトラブルシューティング方法

  1. サブスクライバーでSQL Server Profilerを実行して、ブロックされたプロセス レポート イベントと Exception イベントをキャプチャします。 これらのイベントは、ディストリビューション エージェントが変更されたときに発生するブロックとエラーを記録します。

    注:

    Exception イベントは、問題に関連付けられている可能性があるあらゆる種類のエラーによって発生する可能性があります。 たとえば、外部キー制約違反が原因でエラーが発生する可能性があります。

  2. ディストリビューション エージェントが 1 つのセッションのみを使用してディストリビューション エージェントを監視するように切り替えたかどうかを判断する方法に関するセクションのいずれかの方法を使用します。

  3. ディストリビューション エージェントが 1 つのセッションを使用するように切り替えた場合は、トレースを停止します。

  4. ディストリビューション エージェントの出力ファイルまたはテーブル msdistribution_historyの列start_timeから、次のエントリのタイム スタンプを取得します。

    プロセスは、マルチストリーミング モードで最後のバッチを完了できませんでした。単一接続モードにリセットされ、操作を再試行しています。

  5. サブスクライバーからトレース (.trc) ファイルを開きます。 ステップ 4 で取得したタイム スタンプと同じまたは非常に近いタイム スタンプを持つブロック スクリプトまたは例外イベントを見つけます。

  6. 例外が発生した場合は、例外の詳細を調べて原因を特定します。 たとえば、例外は外部キー制約違反によって発生する可能性があります。 その場合は、サブスクライバー データベースの外部キー制約を削除することをお勧めします。

    ブロックスクリプトに気付いた場合、問題はブロックによって引き起こされます。 ブロック スクリプトの例を次に示します。

    <blocked-process-report monitorLoop="41589"> 
    <blocked-process> 
        <process id="process3a6d438" taskpriority="0" logused="24592" waitresource="KEY: 6:72057594375700480 (0100e420fa5a)" waittime="9937" ownerId="568644832" transactionname="user_transaction" lasttranstarted="2008-05-05T04:55:04.430" XDES="0xa5619e370" lockMode="X" schedulerid="11" kpid="6104" status="suspended" spid="58" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2008-05-05T04:55:04.553" lastbatchcompleted="2008-05-05T04:55:04.430" clientapp=<DistributionAgentProgram> hostname=<servername> hostpid="3980" loginname=<SQLAgentAcct>  isolationlevel="read committed (2)" xactid="568644832" currentdb="6" lockTimeout="4294967295" clientoption1="671090784" clientoption2="128056"> 
            <executionStack> 
            <frame line="5" stmtstart="642" stmtend="1600" sqlhandle="0x0300060057a14477a8c6dd00609a00000100000000000000"/> 
            </executionStack> 
            <inputbuf> 
            Proc [Database Id = 6 Object Id = 2000986455]
            </inputbuf> 
        </process> 
    </blocked-process> 
    <blocking-process> 
        <process status="sleeping" spid="68" sbid="0" ecid="0" priority="0" transcount="1" lastbatchstarted="2008-05-05T04:55:04.570" lastbatchcompleted="2008-05-05T04:55:05.103" clientapp=<DistributionAgentProgram> hostname=<servername> hostpid="3980" loginname=<SQLAgentAcct> isolationlevel="read committed (2)" xactid="568644998" currentdb="6" lockTimeout="4294967295" clientoption1="671090784" clientoption2="128056"> 
        <executionStack/> 
        <inputbuf> 
        Proc [Database Id = 6 Object Id = 1172459501]
        </inputbuf> 
        </process> 
    </blocking-process> 
</blocked-process-report>

    ブロック スクリプトは、ブロックされたセッションとブロック セッションを記録します。 ブロックされたセッションは、 タグ <blocked-process>から開始されます。 ブロッキング セッションは、 タグ <blocking-process>から開始されます。

  7. Object Idブロックされたセッションとブロック セッション内の オブジェクトProcの を見つけます。

    サンプル ブロッキング スクリプトでは、Object IdProcブロックされたセッションの の は です2000986455。 Object IdProcブロッキング セッションの の は です1172459501。

  8. サブスクリプション データベースで、手順 7 で取得したオブジェクト ID と等しい列object_idを指定して、ビュー sys.objects に対してクエリを実行します。 これを行うと、オブジェクト名を決定できます。

    たとえば、サブスクリプション データベースのコンテキストで次のクエリを実行します。

    USE <SubDBName> 
GO 
SELECT name FROM sys.objects 
WHERE object_id = 1172459501 OR object_id = 2000986455

    注:

    • プレースホルダー <SubDBName> は、サブスクリプション データベースの名前を表します。
    • 通常、これらのオブジェクトはレプリケーションで使用されるストアド プロシージャです。

  9. ブロックの原因となるインデックスまたはインデックス付きビューを決定します。 これを行うには、次の手順を実行します。

    1. ブロッキング スクリプトで、 プロパティ waitresourceの値を見つけます。

      サンプル ブロッキング スクリプトでは、 の waitresource 値は です 72057594375700480。

    2. ビュー sys.partition にクエリを実行し、手順 9a で取得した の値waitresourceと等しい列PARTITION_IDを指定して、オブジェクト ID とインデックス ID を取得します。

      たとえば、次のクエリを実行します。

      SELECT object_id, index_id FROM SYS.PARTITIONS WHERE PARTITION_ID=72057594375700480

    3. サブスクリプション データベースで、ビュー sys.indexes にクエリを実行し、手順 9b で取得したオブジェクト ID とインデックス ID を使用してインデックスを決定します。

      たとえば、次のクエリを実行します。

      USE <SubDBName> 
GO 
SELECT name, type_desc, is_unique FROM sys.indexes 
WHERE object_id = <objID> and index_id = <idxID>

      注:

      • プレースホルダー <objID> は、手順 9b で取得したオブジェクト ID を表します。
      • プレースホルダー <idxID> は、手順 9b で取得したインデックス ID を表します。

  10. ブロックがインデックス付きビューによって発生する場合は、インデックス付きビューを削除することをお勧めします。 ブロックが一意の非クラスター化インデックスによって発生する場合は、インデックスを削除してから、一意でないインデックスを再作成することをお勧めします。

ブロックしているモニター スレッドの説明

ディストリビューション エージェントでは、セッション間のブロックを検出するブロック モニター スレッドが保持されます。 ブロッキング モニター スレッドがセッション間のブロックを検出した場合、ディストリビューション エージェントは 1 つのセッションを使用して、ディストリビューション エージェントが以前に適用できなかったコマンドの現在のバッチを再適用するように切り替えます。

ブロッキング モニター スレッドの詳細については、「モニター スレッドの ブロック」を参照してください。

ディストリビューション エージェントが複数のセッションを再開する方法

ディストリビューション エージェントが複数のセッションを再開する前に、ディストリビューション エージェントはストアド プロシージャsp_MSget_repl_commandsを実行して、サブスクライバーで適用されていないコマンドのディストリビューション データベースを再クエリする必要があります。 その後、ディストリビューション エージェントは、ディストリビューション エージェントが複数のセッションを再開する前に、サブスクライバーでこれらすべてのコマンドを適用する必要があります。 潜在的なレプリケーション環境では、ディストリビューション エージェントが複数のセッションを再開する前に、ディストリビューション エージェントがサブスクライバーに多数のコマンドを適用する必要があるため、ディストリビューション エージェントは複数のセッションを再開できません。

プロセス全体を追跡するには、ディストリビューション エージェントの出力ファイルを調べます。