現在オフラインです。再接続するためにインターネットの接続を待っています

SQL Server でロックのエスカレーションが原因で発生するブロッキング問題を解決する方法

この記事は、以前は次の ID で公開されていました: JP323630
概要
ロックのエスカレーションは、粒度が細かい多数のロック (行ロックやページ ロックなど) をテーブル ロックに変換する処理です。Microsoft SQL Server は、ロックのエスカレーションをいつ実行するかを動的に決定します。この決定を行うときに、SQL Server は、特定のスキャンで保持するロックの数、トランザクション全体が保持するロックの数、および全体としてシステムでロックに使用しているメモリを考慮に入れます。SQL Server のデフォルトの動作により、パフォーマンスが改善されるような場面、または過度のシステム ロック メモリをより適切なレベルまで下げる必要があるときにのみ、ロックのエスカレーションが発生します。ただし、一部のアプリケーションやクエリのデザインでは、不適切な場合にロックのエスカレーションが発生することがあり、エスカレートしたテーブルのロックが他のユーザーをブロックすることがあります。この資料では、ロックのエスカレーションがブロッキングの原因であるかどうか判断する方法、および不適切なロックのエスカレーションに対処する方法について説明します。
詳細

ロックのエスカレーションがブロッキングの原因であるかどうかを判断する方法

多くの場合、ロックのエスカレーションがブロッキングの問題の原因ではありません。ブロッキングの問題が発生したときに、ロックのエスカレーションが発生しているかどうかを判断するには、Lock:Escalation イベントを含む SQL プロファイラのトレースを開始します。Lock:Escalation イベントを確認できない場合、ロックのエスカレーションはサーバー上で発生していません。この場合、この資料の情報は適用されません。

ロックのエスカレーションが発生している場合、エスカレートされたテーブルのロックが他のユーザーをブロックしていることを確認します。

ブロッキング チェーンの先頭を確認する方法、およびブロッキング チェーンの先頭で所有されていて他のサーバー プロセス ID (SPID) がブロックされる原因となっているロック リソースを識別する方法の関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
224453 [INF] SQL Server 7.0 または SQL Server 2000 のブロッキング問題と解決策
他のユーザーをブロックしているロックが S (共有) または X (排他) のロック モードを使用する TAB (テーブル レベル) ロック以外の場合、ロックのエスカレーションは問題ではありません。特に、TAB ロックがインテント ロック (ロック モードが IS、IU、または IX など) の場合、これはロックのエスカレーションによるものではありません。ブロッキング問題の原因がロックのエスカレーションではない場合、トラブルシューティングの手順ついては文書番号 224453 の資料を参照してください。

ロックのエスカレーションを防止する方法

ロックのエスカレーションを防止する最も簡単で安全な方法は、トランザクションを短くして、負荷の高いクエリのロックの使用状況を削減し、ロックのエスカレーションのしきい値を超えないようにすることです。この目的を達成するにはいくつかの方法がありますが、以下に主な方法を説明します。
  • 大規模なバッチ操作をいくつかの小さな操作に分けます。たとえば、次のクエリを実行して監査テーブルから数十万個の古いレコードを削除すると、このクエリが他のユーザーをブロックするロックのエスカレーションの原因となることがわかります。
    DELETE FROM LogMessages WHERE LogDate < '2/1/2002'						
    これらのレコードを一度に数百個ずつ削除することによって、トランザクションごとに累積するロックの数を大幅に削減し、ロックのエスカレーションを防止できます。以下に例を示します。
    SET ROWCOUNT 500delete_more:     DELETE FROM LogMessages WHERE LogDate < '2/1/2002'IF @@ROWCOUNT > 0 GOTO delete_moreSET ROWCOUNT 0
  • できるだけ効率の良いクエリを作成することによって、クエリのロックの使用状況を削減します。スキャンまたは Bookmark Lookup 論理操作の多くでは、ロックのエスカレーションが発生する可能性が増加する場合があり、そのためにデッドロックが発生する可能性が高まります。また、一般的に、同時実行とパフォーマンスに悪影響を及ぼす場合があります。ロックのエスカレーションの原因となるクエリを検出した後、インデックスあるいはテーブル スキャンを削除してインデックス シークの効率を最大限にするために、新しいインデックスを作成するか、既存のインデックスに列を追加する可能性を探ります。クエリ アナライザのクエリ ウィンドウにクエリを貼り付け、そこでインデックス分析を自動的に実行することを検討します。この操作を行うには、SQL Server 2000 のクエリ アナライザで [クエリ] メニューの [インデックス チューニング ウィザード] をクリックするか、SQL Server 7.0 のクエリ アナライザで [クエリ] メニューの [インデックス分析の実行] をクリックします。

    この最適化の目的の 1 つは、インデックス シークで返される行ができるだけ少なくなるようにし、Bookmark Lookup の負荷を最小限にする (特定のクエリのインデックスの選択を最大限にする) ことです。Bookmark Lookup 論理操作で多数の行が返されることが予測される場合、PREFETCH 句を使用してブックマークの検索を実行できます。SQL Server が PREFETCH 句を使用してブックマークを検索する場合、トランザクション分離レベルを一部のクエリに対して REPETABLE READ 分離レベルまで上げる必要があります。これは、READ COMMITTED 分離レベルで SELECT ステートメントに似たようなものが (クラスタ化インデックスと非クラスタ化インデックスの両方で) 何千ものキーのロックを取得し、これが原因で、このようなクエリがロックのエスカレーションのしきい値を超える場合があることを意味します。これは、エスカレートされたロックが共有テーブルのロックであることが判別できる場合に特に重要です。ただし、一般的に、デフォルトの READ COMMITTED 分離レベルでは判別できません。エスカレーションの原因が Bookmark Lookup の WITH PREFETCH 句の場合、クエリ プランの Bookmark Lookup 論理操作の下の Index Seek 論理操作または Index Scan 論理操作で表示される非クラスタ化インデックスに別の列を追加することを検討します。select 列リストにすべての行を含むことが実際的でない場合、カバーするインデックス (クエリで使用したテーブルのすべての列を含むインデックス) か、結合条件または WHERE 句で使用した列をカバーする最低 1 つのインデックスを作成できることがあります。

    ネストしたループ結合でも PREFETCH 句が使用される場合があります。その場合、同じロックの現象が発生する原因になります。

    関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
    260652 "BOOKMARK LOOKUP ...WITH PREFETCH" を使用するネストしたループ結合が長時間ロックを保持することがある
  • 異なる SPID が、互いに互換性のないテーブル ロックを保持している場合、ロックのエスカレーションは発生しません。ロックのエスカレーションは、常にテーブル ロックにエスカレートされ、ページ ロックにはエスカレートされません。さらに、他の SPID が、互いに互換性のない TAB ロックを保持しているためにロックのエスカレーションの試行が失敗する場合、エスカレーションを試行したクエリは、TAB ロックを待機している間はブロックを行いません。その代わり、引き続きより細かな本来のレベル (行、キー、またはページ) でロックを取得し、定期的に追加のエスカレーションを試行します。そのため、特定のテーブルでロックのエスカレーションを防止するための 1 つの方法は、エスカレートされたロックの種類と互換性のない別の接続でロックを取得して保持する方法です。テーブル レベルの IX (インテント排他) ロックは行またはページをロックしませんが、それでも S (共有) または X (排他) モードのエスカレートされた TAB ロックと互換性がありません。たとえば、mytable テーブル内の多くの行を変更するバッチ ジョブを実行する必要があり、このジョブでロックのエスカレーションが発生するためにブロックが発生すると想定します。このジョブが常に 1 時間以内に完了する場合、次のコードを含んだ Transact-SQL ジョブを作成し、バッチ ジョブの数分前に開始するようにスケジュールを設定できます。
    BEGIN TRANSELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0WAITFOR DELAY '1:00:00'COMMIT TRAN				
    このクエリは、mytable テーブルで IX ロックを取得して 1 時間保持します。そのため、その 1 時間は、テーブル上のロックのエスカレーションは防止されます。このバッチでは、データの変更や他のクエリのブロックは行われません。ただし、他のクエリが TABLOCK ヒントを使用して、テーブル ロックを強制実行する場合、または管理者が sp_indexoption ストアド プロシージャを使用して、ページ ロックまたは行ロックを無効にした場合は除きます。
さらに、トレース フラグ 1211 を有効にして、ロックのエスカレーションを無効にできます。ただし、このトレース フラグは、SQL Server のインスタンスにあるすべてのロックのエスカレーション全体を無効にします。ただし、ロックのエスカレーションは、数千のロックを取得および解放するオーバーヘッドにより速度が低下するクエリの効率を最大にするため、SQL Server の有用性が向上します。また、ロックのエスカレーションは、ロックを追跡するのに必要なメモリを最小限にすることにも役立ちます。SQL Server がロック構造に動的に割り当てることができるメモリは有限であるため、ロックのエスカレーションが無効で、かつロックで使用されるメモリのサイズが非常に大きくなっている状態で、クエリで使用するために新たなロックを割り当てようとすると、次のエラーが発生する場合があります。
エラー : 1204、レベル : 19、状態 : 1。
この時点では、SQL Server が LOCK リソースを取得できません。アクティブなユーザーが少ないときにステートメントを再実行してください。または、SQL Server のロックとメモリの設定を確認するようにシステム管理者に依頼してください。
: "1204" エラーが発生すると、現在のステートメントの処理が停止し、アクティブなトランザクションがロールバックされます。ロールバック自体が、ユーザーをブロックする原因になったり、SQL Server サービスを再開した場合に、データベースの復旧時間が長くなる原因になったりすることがあります。

ROWLOCK などのロック ヒントを使用しても、最初のロック プランが変更されるだけです。ロック ヒントでは、ロックのエスカレーションは防止できません。

この資料で先に説明したロックのエスカレーションの防止方法以外に、トレース フラグを有効にするよりも優れた選択肢があります。さらに、その他の方法では通常、インスタンス全体のロックのエスカレーションを無効にするよりもクエリのパフォーマンスが向上する結果になります。マイクロソフトは、この資料で先に説明したような他の選択肢を調査していますが、ロックのエスカレーションによって発生する深刻なブロッキングを軽減するためにのみ、このトレース フラグを有効にすることを推奨します。SQL Server を起動するたびにトレース フラグを有効にするには、トレース フラグをサーバー起動時のパラメータとして追加します。

サーバー起動時のパラメータを追加するには、SQL Enterprise Manager でサーバーを右クリックし、[プロパティ] をクリックします。次に、[全般] タブの [起動時のパラメータ] をクリックし、次のパラメータを正確に追加します。
-T1211
新しい起動時のパラメータを有効にするために SQL Server サービスを再開する必要があります。クエリ アナライザで次のクエリを実行すると、すぐにトレース フラグが有効になります。
DBCC TRACEON (1211, -1)				
ただし、-T1211 という起動時のパラメータを追加しない場合、SQL Server サービスを再開すると、traceon コマンドが無効になります。トレース フラグを有効にすることによって、今後のロックのエスカレーションが防止されますが、アクティブなトランザクションで既に発生したロックのエスカレーションは無効になりません。
プロパティ

文書番号:323630 - 最終更新日: 05/15/2011 18:51:00 - リビジョン: 11.0

Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 7.0 Standard Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Express Edition, Microsoft SQL Server 2005 Workgroup Edition

  • kbsqlmanagementtools kbinfo KB323630
フィードバック
/html>