ロックエスカレーションによって発生するブロックの問題を解決SQL Server

まとめ

ロック エスカレーションは、多くの細かいロック (行ロックやページ ロックなど) をテーブル ロックに変換するプロセスです。 Microsoft SQL Serverエスカレーションを実行する時間を動的に決定します。 この決定を行う場合、SQL Server は、特定のスキャンで保持されるロックの数、トランザクション全体が保持するロックの数、およびシステム全体のロックに使用されるメモリを考慮します。 通常、SQL Server の既定の動作では、ロック エスカレーションが発生するのは、パフォーマンスが向上する場合や、システム ロック メモリを過度に低くする必要がある場合のみです。 ただし、一部のアプリケーションまたはクエリ デザインでは、このアクションが望ましくないときにロック エスカレーションがトリガーされ、エスカレートされたテーブル ロックによって他のユーザーがブロックされる場合があります。 この記事では、ロック エスカレーションがブロックの原因となっているかどうかを判断する方法と、望ましくないロック エスカレーションに対処する方法について説明します。

元の製品バージョン:  SQL Server
元の KB 番号:   323630

ロック エスカレーションによってブロックが発生するかどうかを判断する

ロック エスカレーションは、ほとんどのブロックの問題を引き起こします。 ブロックの問題が発生した時点または近くでロック エスカレーションが発生するかどうかを確認するには、イベントを含む拡張イベント セッションを開始 lock_escalation します。 イベントが表示されない場合は、サーバー上でロック エスカレーションが発生していないので、この記事の情報は状況 lock_escalation には適用されません。

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

ヘッド ブロッカーによって保持され、他のサーバー プロセスの ID (SPID) をブロックしているヘッド ブロッカーとロック リソースを特定する方法の詳細については、「INF: SQL Serverブロックの問題について」を参照してください。

他のユーザーをブロックしているロックが、ロック モードが S (共有) または X (排他) の TAB (テーブル レベル) ロック以外の場合、ロック エスカレーションは問題ありません。 特に、TAB ロックがインテント ロック (IS、IU、IX のロック モードなど) の場合、ロックエスカレーションによって発生しません。 ブロックの問題がロック エスカレーションによって引き起こされない場合は、「INF:ブロックの問題のトラブルシューティング手順を理解SQL Server解決する」を参照してください。

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

ロック エスカレーションを防ぐ最も簡単で安全な方法は、トランザクションを短くし、ロック エスカレーションのしきい値を超えないように、高価なクエリのロックフットプリントを削減する方法です。 この目標を達成するには、次の戦略を含むいくつかの方法があります。

  • 大規模なバッチ操作をいくつかの小さな操作に分割します。 たとえば、次のクエリを実行して監査テーブルから 100,000 以上の古いレコードを削除し、そのクエリによって他のユーザーをブロックするロック エスカレーションが発生したと判断します。

    DELETE FROM LogMessages WHERE LogDate < '20020102';
    

    これらのレコードを一度に数百個削除することで、トランザクションごとに蓄積されるロックの数を大幅に削減できます。 これにより、ロックエスカレーションが防止されます。 たとえば、次のクエリを実行します。

    DECLARE @done bit = 0;
    WHILE (@done = 0)
    BEGIN
        DELETE TOP(1000) FROM LogMessages WHERE LogDate < '20020102';
        IF @@rowcount < 1000 SET @done = 1;
    END;
    
  • クエリを可能な限り効率的にすることで、クエリのロックフットプリントを削減します。 大規模なスキャンや多数のブックマーク参照によって、ロックエスカレーションの可能性が高まる可能性があります。 さらに、デッドロックの可能性が高まり、同時実行とパフォーマンスに悪影響を及ぼします。 ロック エスカレーションを引き起こすクエリを特定した後、新しいインデックスを作成したり、既存のインデックスに列を追加してインデックスまたはテーブル スキャンを削除したり、インデックス シークの効率を最大化したりする機会を探します。 クエリのパフォーマンスを向上させるために、実行計画を確認し、新しい非リソース化インデックスを作成する可能性があります。 詳細については、「インデックス アーキテクチャとSQL Serverガイド」を参照してください

    この最適化の 1 つの目的は、インデックス シークを可能な限り少ない行数で返して、ブックマーク参照のコストを最小限に抑える (クエリのインデックスの選択性を最大化すること) です。 ブックマークSQL Server論理演算子が多数の行を返すと推定する場合は、句を使用してブックマーク参照 PREFETCH を実行できます。 ブックマークSQL Serverを使用する場合は、クエリの一部のトランザクション分離レベルをクエリの一部の "反復可能な読み取り" に PREFETCH 増やす必要があります。 つまり、"読み取りコミット" 分離レベルのステートメントのように見える場合、(クラスター化インデックスと 1 つの非クラスター化インデックスの両方で) 何千ものキー ロックが取得される SELECT 可能性があります。 これにより、このようなクエリがロック エスカレーションのしきい値を超える可能性があります。 これは、エスカレートされたロックが共有テーブル ロックである場合に特に重要ですが、これらは通常、既定の "読み取りコミット済み" 分離レベルでは表示されません。 ブックマーク 参照 WITH 句がエスカレーションを引き起こしている場合は、インデックス シークに表示される非子化インデックスに列を追加するか、クエリ プランのブックマーク 参照論理演算子の下にインデックス スキャン論理演算子を追加します。 PREFETCH カバー インデックス (クエリで使用されたテーブル内のすべての列を含むインデックス) を作成したり、結合条件または WHERE 句で使用された列をカバーするインデックスを少なくとも作成することもできます 。"select column" リストにすべてを含めるのは現実的な場合です。

    入れ子になったループ結合も使用 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 時間取得および保持します。 これにより、その間のテーブルのロック エスカレーションが防止されます。 このバッチは、データを変更したり、他のクエリをブロックしたりしません (他のクエリが TABLOCK ヒントを使用してテーブル ロックを強制的に行う場合や、管理者が ALTER INDEXを使用してページロックまたは行ロックを無効にしていない場合)。

  • SARGability の欠如によるロックエスカレーションを排除します。クエリが述語と結合列にインデックスを使用できるかどうかを記述するために使用されるリレーショナル データベース用語です。 SARGability の詳細については 、「Inside Design Guide Query Considerations」を参照してください。 たとえば、多くの行 (または 1 行) を要求していないかなり単純なクエリでは、テーブル/インデックス全体がスキャンされる可能性があります。 これは、WHERE 句の左側に関数または計算がある場合に発生する可能性があります。 SARGability に欠けるこのような例には、暗黙的または明示的なデータ型変換、ISNULL() システム関数、パラメーターとして渡された列を持つユーザー定義関数、またはなどの列の計算が含まれます WHERE CONVERT(INT, column1) = @aWHERE Column1*Column2 = 5 このような場合、すべての列の値を最初に取得して関数に渡す必要があるため、クエリは、適切な列が含まれている場合でも、既存のインデックスを SEEK することはできません。 これにより、テーブルまたはインデックス全体がスキャンされ、多数のロックが取得されます。 このような状況では、SQL Serverカウントエスカレーションのしきい値に達する可能性があります。 解決策は、WHERE 句の列に対して関数を使用しないようにし、SARGable 条件を確保します。

ロックエスカレーションを無効にする

ロック エスカレーションを無効にSQL Server、お勧めしません。 代わりに、[ロックエスカレーションの防止] セクションで説明されている 防止戦略を使用 します。

  • テーブル レベル: テーブル レベルでロック エスカレーションを無効にできます。 ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE)を参照してください。 ターゲットとするテーブルを決定するには、T-SQLを調べてください。 それができない場合は、拡張イベントを使用し、lock_escalationイベントを有効にし、object_id します。 または、Lock:Escalation イベントを使用し、プロファイルを使用して列 ObjectID2 SQLします。
  • インスタンス レベル: インスタンスのトレース フラグ 1211 を有効にすると、ロック エスカレーションを無効にできます。 ただし、このトレース フラグは、すべてのロック エスカレーションをグローバルに無効にSQL Server。 ロック エスカレーションは、数千のロックの取得と解放のオーバーヘッドによって低下するクエリの効率を最大限に高め、SQL Server で役立ちます。 ロックエスカレーションは、ロックを追跡するために必要なメモリを最小限に抑えるのにも役立ちます。 ロック構造にSQL Server割り当て可能なメモリは有限です。 したがって、ロック エスカレーションを無効にし、ロック メモリが十分に大きくなると、クエリに追加のロックを割り当てしようとすると失敗し、次のエラー エントリが生成される可能性があります。

エラー: 1204、重大度: 19、状態: 1
このSQL Server LOCK リソースを取得できません。 アクティブなユーザーが少ない場合はステートメントを再実行するか、システム管理者にロックとメモリSQL Server確認してください。

注意

1204 エラーが発生すると、現在のステートメントの処理が停止し、アクティブなトランザクションがロールバックされます。 ロールバック自体がユーザーをブロックしたり、データベース サービスを再起動するとデータベースの回復時間が長SQL Serverがあります。

このトレース フラグ (-T1211) は、次のコマンドを使用してSQL Server 構成マネージャー。 新しいスタートアップ パラメーターを有効SQL Serverサービスを再起動する必要があります。 クエリを実行すると DBCC TRACEON (1211, -1) 、トレース フラグは直ちに有効になります。
ただし、-T1211 スタートアップ パラメーターを追加しない場合、サービスの再起動時にコマンド DBCC TRACEON のSQL Server失われます。 トレース フラグをオンにしても、将来のロック エスカレーションは防止されますが、アクティブ なトランザクションで既に発生しているロック エスカレーションは元に戻す必要があります。

ROWLOCK などのロック ヒントを使用すると、初期ロック 計画だけが変更されます。 ロック ヒントはロックエスカレーションを防止しない。

ロックエスカレーションのしきい値

ロックエスカレーションは、次のいずれかの条件で発生する可能性があります。

  • メモリのしきい値に達 した - ロック メモリの 40% のメモリしきい値に達しました。 ロック メモリがバッファー プールの 24% を超えると、ロック エスカレーションをトリガーできます。 ロック メモリは、表示されるバッファー プールの 60% に制限されます。 ロック エスカレーションのしきい値は、ロック メモリの 40% に設定されます。 これは、バッファー プールの 60% の 40%、つまり 24% です。 ロック メモリが 60% の制限を超えた場合 (ロック エスカレーションが無効になっている場合、これははるかに可能性が高い)、追加のロックの割り当て試行はすべて失敗し、エラー 1204 が生成されます。

  • ロックのしきい値に 達する - メモリしきい値がチェックされた後、現在のテーブルまたはインデックスで取得されたロックの数が評価されます。 数値が 5,000 を超えると、ロックエスカレーションがトリガーされます。

どのしきい値に達したのか理解するには、拡張イベントを使用し、lock_escalation イベントを有効にし、escalated_lock_count escalation_causeします。 または、Lock:Escalationイベントを使用し、値を調べて、"0 - LOCK_THRESHOLD" はステートメントがロックしきい値を超え、"1 - MEMORY_THRESHOLD" はステートメントがメモリしきい値を超えたと示します。 EventSubClass また、列と IntegerData 列を IntegerData2 調べてください。

推奨事項

「ロックエスカレーションの防止」セクションで説明されているメソッドは、テーブルまたはインスタンス レベルでエスカレーションを無効にするよりも優れたオプションです。 さらに、一般に、予防方法では、ロック エスカレーションを無効にするよりもクエリのパフォーマンスが向上します。 このトレース フラグは、ロック エスカレーションによって引き起こされる重大なブロックを軽減するためにのみ有効にし、この記事で説明されているオプションなど、他のオプションも調査中です。

関連項目