小物SEのメモ帳

IT関係の試したことや調べたことの備忘録

【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 テーブルスキーマに適したロックエスカレーションの粒度が選択される
また、パーティション分割されている場合、ロックエスカレーションをパーティション分割できる


◆参考資料

・locks オプションについて
https://technet.microsoft.com/ja-jp/library/ms175978(v=sql.105).aspx

・ロックのエスカレーションについて
https://technet.microsoft.com/ja-jp/library/ms184286(v=sql.105).aspx