SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server のページラッチ (PAGELATCH) の必要性

leave a comment

SQL Server では、「メモリにロードされた (バッファ上にキャッシュされた) ページ (データ領域)」を保護するための機構として、「ページラッチ (PAGERATCH)」というリソースを保護するための機能があります。

公式ドキュメントとしては SQL Server でラッチの競合を診断および解決する で解説が行われています。(こちらのドキュメントは以前は英語のホワイトペーパーとして公開されていたものが、Docs で翻訳されたものになるかと)

本ブログではページラッチについて、あまり取り扱ったことが無かった気がするので、この機会にまとめておこうかと。

ページラッチの基本的な考え方

ページラッチは、ラッチというリソースの保護方法の中の一つの種類となります。
ラッチは SQL Server 特有の用語ではありません。

C# のドキュメントであれば、 方法: SpinWait を使用して 2 フェーズ待機操作を実装する でラッチという用語が出てきています。

ラッチはスレッド同期プリミティブであり、スレッド間のリソースのアクセス制御に活用されます。

SQL Server ではラッチは多数の種類があり、SQL Server 2019 CU8 であれば 172 種類のラッチがあります。

SQL Server でラッチについて解説を行う場合、大きく 次の 2 種類に分けられます。

  • バッファラッチ
  • 非バッファラッチ (ノンバッファラッチ)

バッファラッチはバッファ上 (メモリ上) のデータを保護するためのラッチとなり、sys.dm_os_wait_stats であれば、次の情報となります。

  • PAGEIOLATCH_xx
  • PAGELATCH_xx

バッファラッチ以外のラッチを非バッファラッチといい、sys.dm_os_wait_stats で LATCH_xx でカウントされているものは非バッファラッチとなります。

LATCH_xx の詳細は、sys.dm_os_latch_stats で確認することができます。

sys.dm_os_wait_stats と sys.dm_os_latch_stats の情報は相互に関係しており、次のようになります。

  • sys.dm_os_wait_stats
    • バッファラッチは PAGEIOLATCH_xx / PAGELATCH_xx で確認できる
    • 非バッファラッチについては LATCH_xx として集計された形で確認できる
  • sys.dm_os_latch_stats
    • バッファラッチは BUFFER として集計された形で確認できる
    • 非バッファラッチはラッチクラスごとに確認できる
      • sys.dm_os_wait_stats の LATCH_xx の内訳を詳細に確認できる

PAGEIOLATCH

ストレージサブシステムとの I/O のため、バッファ上のデータ (領域) を保護するためのラッチが「PAGEIOLATCH_xx」です。
基本的な発生ケースとしては、

  • メモリ上にキャッシュされていないデータをディスクから読み取る
  • LAZY WRITER スレッドがバッファキャッシュの空きを確保するために、ダーティーページをディスクに書き出す

というような、メモリとディスク間の I/O が発生する際にデータ領域を保護するためにラッチの取得が行われます。

image

PAGEIOLATCH が多い場合は、ディスクとのデータ I/O が多いというような判断をするのは、このような動作が行われている場合に発生するラッチのためです。

PAGELATCH

ページラッチは、メモリ上にキャッシュされているデータ領域を保護するために取得されるラッチです。
PAGEIOLATCH は、ストレージサブシステムとの I/O は発生せずに、特定のページにアクセスが集中している場合などに発生します。

有名なのは、tempdb データ ファイルの数を増やすと、コンテンツの問題を減らす方法 で解説が行われているような、tempdb の システムアロケーションページ (PFS / GAM / SGAM) の競合ですね。

データベースから領域を確保する際には、PFS というページ (データ格納領域) の空きを管理しているページから「どのページに空きがあるか」を取得し、「ページの空き状況を更新する」という、管理領域の更新を行う必要があります。

PFS は、データファイル単位に、8,088 ページごとに存在しており、1 つの PFS で 8,000 ページ以上の空き領域を管理することができます。

tempdb のデータファイルの領域を使用する (データ領域を確保) 際には、この PFS の情報を変更する必要があるのですが、データファイルが一つしかない状態では PFS が一つしかない存在していません。
そのため、複数のセッションが、自分が確保したページの情報を PFS から取得 / 変更をしようとするのですが、ページラッチにも互換性があり、排他制御が行われています。

image

PFS の情報を変更しようとした場合、ページに対して PAGELATCH_EX が取得されますが、EX 間は競合するため、複数のセッションで PAGELATCH_EX を取得しようとすると、「ページラッチによる待機」が発生します。

image

このような、複数のセッションでアクセスが集中するページを「ホットページ (アクセスが頻繁に発生するページ)」というような言い方をすることがありますが、特定のページにアクセスが集中するとロックではなく、ページラッチが同時実行性の低下の一因になることがあります。

今回はシステムアロケーションページ (PFS) を例としていますが、これは通常のデータページでも考え方は同様です。
特定のページに対して更新系のアクセスが集中するとページラッチが発生する可能性が高くなります。
(参照の PAGELATCH_SH 間は競合しませんので、データ参照が特定のページに集中することは問題ありません)

PAGELATCH はメモリ上にキャッシュされているページに対してのアクセス制御をするために使用されるラッチとなります。

 

PAGEIOLATCH と PAGELATCH の関係は下図のようになるかと。

image

 

ページラッチが必要な理由

ここが今回の投稿の本題です。

データのレコードを排他制御するためには「ロック」が使用されています。
ロックはテーブル内のレコードの整合性を維持するために取得され、レコードの状態を正しく管理するために使用されます。

image

「ページラッチ」も考え方は同じで「メモリ上にキャッシュされているページの情報を正しく管理するため」に使用されています。

image

ページラッチがによるアクセス制御が行われていない場合の、データページの更新について考えてみます。
これは Bob Ward が説明する際に使用している内容で、SQL Server のデータ格納領域の構造を把握していると分かりやすいです。

SQL Server のデータページは次のように管理されています。

image

ページヘッダ / レコード / オフセットで構成されており、オフセットはデータページ内の各レコードが何バイト目から始まっているのかを示すポインターとなります。

image

新しいレコードが挿入されると、実レコードとともにオフセットも格納する必要があります。

ページラッチによるリソースの保護が行われていない状態で、複数のセッションから同一ページに対して同時にレコードの挿入が行われたケースを考えてみます。

image

複数のセッションで同時にページにアクセスができてしまうと、それぞれのレコードの挿入時にオフセットが上書きされてしまう可能性があります。

理想としては、次のように格納されるのが望ましいのですが、
image
ページたいして排他制御が行われていないと、全く同時にデータの挿入が行われた場合、次のようにどちらかのセッションのレコードのみが格納されてしまう可能性があります。
image

このような状況にならないようにするために、データの挿入を行う際には、データページの構造の整合性を維持するため、データページのバッファに対して「PAGELATCH_EX」が取得されます。

image

PAGELATCH_EX 間は競合するため、該当のデータページに対して PAGELATCH_EX が取得できるのは一つのセッション (タスク / ワーカー) のみとなります。

このような状況は「Last Page Insert (ラストページ INSERT / 最終ページ挿入)」のワークロードでよく発生するものになります。
クラスター化インデックスキーを IDENTITY にした場合、データは同一ページ内に格納される可能性が高くなり、ページラッチの競合が発生しやすいデータ構造となります。

sys.dm_exec_requests や sys.dm_os_waiting_tasks で確認ができる待機リソースでは次の状態となっているケースです。

image

上記の情報は、IDENTITY のプライマリキーを持つテーブルに対して複数セッションで INSERT を実行した際の情報となります。

「10:1:61688」というページに対して「PAGELATCH_EX」による待機が複数発生していることが確認できます。
INSERT を行う際には、瞬間的にページに対して排他のためのページを取得する必要があるため、同一のページ内の INSERT が大量に発生すると、ページラッチによる待機がボトルネックとなり、同時実行されているクエリ数によってはページラッチによる待機が数秒に達することもあります。

これを回避するためには、パーティショニングを使用 / クラスター化インデックスのキーを GUID にすることで、データが格納されるページを分散させるような方法があります。(GUID にした場合、ページ分割が発生する可能性が高くなり、ページラッチの発生頻度の抑制と引き換えに、ACCESS_METHODS_HOBT_VIRTUAL_ROOT のラッチの発生による B-Tree のページ構成の維持がボトルネックとなる可能性があります)

 

「ページラッチはバッファラッチであり、バッファのデータ構造を維持するための同期プリミティブ」となり、データページの構造を維持するために必要な機構です。
ユーザーデータの投入時にページラッチが頻繁に発生している場合は、「ワークロードによってはホットページができやすいデータ構造」となっている可能性が高いです。

ユーザーデータがページ内にどのように格納され、複数のセッションから大量にデータ投入 (挿入 / 更新 / 検索) が行われた場合、ページに対してどのようにアクセスされるかを意識しておくことは重要ではないでしょうか。

Share

Written by Masayuki.Ozawa

1月 12th, 2021 at 11:48 pm

Leave a Reply