SQL Server でロックエスカレーションが発生する要因としては次の 2 種類があります。
- ロックのメモリが閾値に達した場合
- ロック数が閾値に達した場合
閾値については、ロックのエスカレーションのしきい値 に記載されています。
それぞれの閾値に達した場合に、ロックエスカレーションが発生し、ロックの粒度がテーブルにエスカレーションされ確保されます。
この動作により、取得されているロックの数を最小限にすることで、ロックで過剰なメモリが使用されないようにします。
TF1211 を有効にすることで、「1.」「2.」の両方のロックエスカレーションを無効にし、TF1224 を有効にすることで「2.」についてのロックエスカレーションを無効にします。
これが、ロックエスカレーションの基本的な考え方となりますが、「1.」のケースについて、きちんと理解できていなかったことが分かったので、情報をまとめておきたいと思います。
メモリの閾値によるロックエスカレーションの挙動
前述のとおり、メモリの閾値によるロックエスカレーションは、ロックで使用しているメモリサイズが一定量になった場合に発生します。
閾値については、ロックのエスカレーションのしきい値 に記載されている次の内容となります。
メモリしきい値に達しました – ロック メモリの 40% のメモリしきい値に達しました。 ロック メモリがバッファー プールの 24% を超えると、ロックエスカレーションをトリガーできます。 ロック メモリは、可視バッファー プールの 60% に制限されます。 ロックのエスカレーションしきい値は、ロック メモリの 40% に設定されます。 これは、バッファー プールの 60% の 40% または 24% です。 ロック メモリが 60% の制限を超えた場合 (ロックエスカレーションが無効になっている場合の可能性が非常に高くなります)、追加のロックを割り当てようとするすべての試行が失敗し、
1204エラーが生成されます。
SQL Server の既定の設定では、バッファプールの 60% までロック用のメモリを取得することができます。
動的なロック プールは、データベース エンジンに割り当てられたメモリのうち最大 60% まで取得できます。 ロック プールがデータベース エンジンのインスタンスによって取得されたメモリの 60% に達した場合、またはコンピューターで使用できるメモリがなくなった場合、さらにロック要求があるとエラーが発生します。
60% はロック用のメモリで確保可能な最大のメモリサイズとなり、この 60% の中値に対して、40% or 24% までロック用途でメモリが取得されると、ロックエスカレーションが発生します。
次の画像は、ロックで大量のメモリを取得した状態を再現したものとなります。
「Total Server Memory (KB)」がバッファプールのサイズとなりますが、36GB 程度メモリが確保されている状態となります。
この状態に対して、「Lock Memory (KB)」が 20GB 程度取得されていますので、バッファプールの 60% 程度 (上限) までロックが取得された状態となっています。
この状態で次のようなクエリを実行してみます。
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; DECLARE @cnt bigint = 2500 BEGIN TRAN SELECT TOP (@cnt) * FROM LINEITEM WITH(ROWLOCK) ROLLBACK TRAN
2,500 前後のロックを追加で取得するクエリとなりますが、現在の状態でこのようなクエリを実行するとロックエスカレーションは発生するのでしょうか？
ロック数は、5,000 未満であり、このクエリ自体がロックで使用するメモリは少量となりますが、このクエリを実行した場合、ロックエスカレーションが発生します。
ロックエスカレーションのイベントは拡張イベントで「lock_escalation」を取得することで確認ができ、実際に取得した結果が以下となります。
「Memory threshold」によるロックエスカレーションが発生しており、「2,499」個目のロックを取得しようとした際にロックエスカレーションが発生しています。
この情報から少量のロックを取得しているにもかかわらず、ロックエスカレーションが発生しているということが確認できます。
「メモリの閾値によりロックエスカレーション」ですが、今までは「大量にロックを取得するセッションのクエリ」に対して発生するものと考えていたのですがこれは誤りだったようです。
メモリの敷地によるロックエスカレーションは、「インスタンスで大量にロックのメモリが取得されている状況で実行されたすべてのクエリ」が対象となるようでした。
そのため、大量のロックメモリが確保されている環境では、実行したクエリで大量のロックのメモリが取得されない場合でもロックエスカレーションの対象となる可能性があるようです。
大量にロック用のメモリが取得されている環境では、ロック用のメモリを肥大化させたクエリ以外のロックエスカレーションについても考慮が必要なようですね。
この挙動にならないようにするには、処理対象となるデータ件数の粒度を意識し過度にロックのメモリを取得しないようにするか、TF1211 によるメモリの閾値のロックエスカレーションを禁止する必要が出てくるのではないでしょうか。
本投稿の内容を AI で要約した画像
