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

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

先頭へ戻る

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

• 大規模なバッチ操作をいくつかの小さな操作に分けます。たとえば、次のクエリを実行して監査テーブルから数十万個の古いレコードを削除すると、このクエリが他のユーザーをブロックするロックのエスカレーションの原因となることがわかります。

  DELETE FROM LogMessages WHERE LogDate < '2/1/2002'						

  これらのレコードを一度に数百個ずつ削除することによって、トランザクションごとに累積するロックの数を大幅に削減し、ロックのエスカレーションを防止できます。以下に例を示します。

  SET ROWCOUNT 500
  delete_more:
       DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
  IF @@ROWCOUNT > 0 GOTO delete_more
  SET 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  (http://support.microsoft.com/kb/260652/ ) "BOOKMARK LOOKUP ...WITH PREFETCH" を使用するネストしたループ結合が長時間ロックを保持することがある
• 異なる SPID が、互いに互換性のないテーブル ロックを保持している場合、ロックのエスカレーションは発生しません。ロックのエスカレーションは、常にテーブル ロックにエスカレートされ、ページ ロックにはエスカレートされません。さらに、他の SPID が、互いに互換性のない TAB ロックを保持しているためにロックのエスカレーションの試行が失敗する場合、エスカレーションを試行したクエリは、TAB ロックを待機している間はブロックを行いません。その代わり、引き続きより細かな本来のレベル (行、キー、またはページ) でロックを取得し、定期的に追加のエスカレーションを試行します。そのため、特定のテーブルでロックのエスカレーションを防止するための 1 つの方法は、エスカレートされたロックの種類と互換性のない別の接続でロックを取得して保持する方法です。テーブル レベルの IX (インテント排他) ロックは行またはページをロックしませんが、それでも S (共有) または X (排他) モードのエスカレートされた TAB ロックと互換性がありません。たとえば、mytable テーブル内の多くの行を変更するバッチ ジョブを実行する必要があり、このジョブでロックのエスカレーションが発生するためにブロックが発生すると想定します。このジョブが常に 1 時間以内に完了する場合、次のコードを含んだ Transact-SQL ジョブを作成し、バッチ ジョブの数分前に開始するようにスケジュールを設定できます。

  BEGIN TRAN
  SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0
  WAITFOR DELAY '1:00:00'
  COMMIT TRAN				

  このクエリは、mytable テーブルで IX ロックを取得して 1 時間保持します。そのため、その 1 時間は、テーブル上のロックのエスカレーションは防止されます。このバッチでは、データの変更や他のクエリのブロックは行われません。ただし、他のクエリが TABLOCK ヒントを使用して、テーブル ロックを強制実行する場合、または管理者が sp_indexoption ストアド プロシージャを使用して、ページ ロックまたは行ロックを無効にした場合は除きます。

DBCC TRACEON (1211, -1)				

先頭へ戻る