先日投稿した SQL Server の「NOLOCK」ヒントは単純なロックを取得しないという動作ではありません の内容に近いものですが。
NOLOCK ロックヒントを設定しても発生する待ち事象について少し書いておきたいと思います。
先日書いた投稿は NOLOCK ロックヒントを使用すると整合性のあるデータへのアクセスが保証されないため、COUNT(*) を実行すると、データの総数が変わっていないが、データの件数が一定しないというものでした。
今回の投稿は「PAGELATCH」による待ちが発生するというものです。
PAGELATCH (ページラッチ) については次のドキュメントの記載がわかりやすいかと思います。
ページラッチ(データまたはインデックス ページのラッチ) は、スレッド同期機構です。バッファー キャッシュ内にあるデータベースのページへの短期的な物理アクセスの同期に使用されます。
基本的な考え方としては、ページ内の管理情報の整合性を保つために使用される同期機構をイメージするとよいかと思います。
NOLOCK でクエリを実行した場合も、PAGELATCH の待ちについては発生します。
主キー (クラスター化インデックス) を使用した更新と、検索を例にして考えてみます。
1 つめのセッションでは次のようなクエリを連続して実行します。
- UPDATE TestTable SET C2 = NEWID() WHERE C1 = 15
UPDATE が実行されている最中に、複数のセッションから次のクエリを連続して実行します。
- SELECT COUNT(*) FROM TestTable WITH(NOLOCK) WHERE C1 = 10
更新しているデータは、C1=15 ですが、検索しているデータは C1=10 というように異なるデータについてのアクセスが行われている状態です。
行としては異なる情報にアクセスされていますが、実行のタイミングによっては次のような待ち事象が発生します。
UPDATE を実行すると、ページの管理情報の変更のために瞬間的に「PAGELATCH_EX」が取得されます。
NOLOCK でデータを検索しても、ページの管理情報へのアクセスについては瞬間的に「PAGELATCH_SH」が取得されるため、更新と検索のタイミングが合致すると、NOLOCK を指定していてもロックではなくページラッチによる競合が発生することがあります。
上記の画像ではページ ID10528 というページに対してのページラッチが発生しています。
SQL Server は 8KB ページの中に複数の行を格納しますので、C1=15 というデータと C1=10 というデータが同じページ内に入っている可能性があります。
このような場合は、同一の行を操作しているのではないため、行ロックの競合は発生しないのですが、同一のページにアクセスをしているためページラッチによる待ちが発生する可能性があります。
今回の例では wait_time は瞬間的なものですので、ページラッチが処理性能を大きく低下させる要因となっているとは考えられません。
もし、ページラッチのが多発しており、高速化する必要が出てきた場合、効果があるかを試しやすい方法としては、In-Memory OLTP (Hekaton) のメモリ最適化テーブルのロック / ラッチフリーアーキテクチャになるのではないでしょうか。
先ほど投稿した HADR_THROTTLE_LOG_RATE_MISMATCHED_SLO によるトランザクションログの書き込み性能の低下について とも関連するときがあるのですが、更新に時間がかかっていると、該当のページのアクセスについては NOLOCK を使用している / 更新が発生している行とは異なるデータに対してアクセスを行っていても「同一ページへのアクセス」が起因した待ち事象により同時実行性が低下する可能性があります
NOLOCK でページラッチが発生している場合は、本投稿のようなページ情報の競合というのを思い浮かべてみると、対応案の検討につながるのではないでしょうか。