【SQL server】ロックエスカレーションの閾値について

悪者扱いされることの多いロックエスカレーションの発生する閾値と設定の確認方法についてです。

・ロック上限が動的な場合（locks オプションが既定値）のロックエスカレーションの閾値
（1）1 つの Transact-SQL ステートメントが 1 つのテーブルまたはインデックスに対して少なくとも 5,000 個のロックを獲得した場合
（2）ロックによって使用されるメモリ※がデータベースによって使用されるバッファープールの24%以上となった場合
※ロックオブジェクトのサイズは1ロックにつき96バイト

また、ロックの競合によりロックをエスカレートできない場合、新たなロックを 1,250 個獲得するごとにロックのエスカレーションが行われます。
尚、変化するワークロードに合わせてメモリを動的に確保・解放するため、上記閾値は動的に変わります。

・ロック上限が固定の場合（locks オプションが既定値でない）のロックエスカレーションの閾値
（1）ロック数が設定した値の上限の40％※に達した場合
※メモリに負荷がかかっている場合は 40% 未満

ロックエスカレーションはテーブル単位で無効化することは可能ですが、バッファープールのうち60%以上のロックを獲得した場合、もしくは使用できるメモリがなくなった場合にロックを獲得しようとするとエラーが発生しますので注意が必要です。

ちなみにロックエスカレーションの設定確認・変更は以下で行えます。

◆設定確認

SELECT 
	[name],
	[lock_escalation],
	[lock_escalation_desc]
FROM [sys].[tables]

f:id:utiowa:20161104005958p:plain

◆設定変更

ALTER TABLE sales.product.test SET (LOCK_ESCALATION = AUTO)

lock_escalation	lock_escalation_desc	説明
0	TABLE	ロックエスカレーションはテーブルレベルの粒度で行われる ※デフォルト
1	DISABLE	ほとんどの場合のロックエスカレーションを禁止するテーブルレベルのロックは完全には禁止されない
2	AUTO	テーブルスキーマに適したロックエスカレーションの粒度が選択されるまた、パーティション分割されている場合、ロックエスカレーションをパーティション分割できる