SE の雑記

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

SQL Server / Azure SQL Database の last-page insert による PAGELATCH_EX の競合について

without comments

調べ物をしていたところ、SQL Server で最終ページの挿入 PAGELATCH_EX 競合を解決する方法 (How to resolve last-page insert PAGELATCH_EX contention in SQL Server) というドキュメントが公開されていることに気づいたので、少しまとめておこうかと思います。

本投稿は、データベースを遅くするための8つの方法 で解説されている、「シーケンスナンバーに PK を使用する」の Right Growing Index に関してのSQL Server での基本的な考え方になります。

last-page insert による同時実行性の低下について

last-page insert (最終ページへの Insert) は、大量のデータ投入が行われた際に、データが追加されてる最後のページに、Insert が集中して、PAGE ALTCH による同時実行性が低下するという状況です。
このような状況になっている場合「ホットページが存在している」というような言い方をすることもあります。

データのソート順とデータ格納状況によっては、下図のように、テーブルの最終ページにのみデータの Insert が集中するケースがあります。

image

SQL Server の場合、identity を付与した列で、プライマリキー (クラスター化インデックス プライマリーキー) を設定している場合などに、この事象に遭遇する可能性が高くなります。

例として、次のようなテーブルを作成してみます。

CREATE TABLE IdentityTest(
    C1 int identity NOT NULL,
    C2 int NOT NULL,
    C3 uniqueidentifier NOT NULL,
    C4 uniqueidentifier,
    C5 uniqueidentifier,
    C6 uniqueidentifier,
    C7 uniqueidentifier,
    C8 uniqueidentifier,
    C9 uniqueidentifier,
    C10 uniqueidentifier
)
ALTER TABLE IdentityTest 
ADD CONSTRAINT PK_IdentityTest PRIMARY KEY CLUSTERED(C1, C2)
ON [PRIMARY]

 

プライマリキーは「C1 (int)」を先頭の項目に設定したクラスター化インデックスの項目となっています。

そのためデータは次のような順序で追加されていきます。

image

C1 が 1 から始まった連番として登録が行われていますね。

identity を先頭項目として設定している場合、基本的なデータ投入として、データはページ内の最後に投入が行われることになります。

つまり「last-page insert が行われる可能性が高い状態」であるといえます。

今回は identity をプライマリキーとした場合についてで記載していますが、last-page insert が発生するのは identity がプライマリキーとして設定されているテーブルに、限定されるものではありません。

基本的に最後にデータが投入される構成になっているものについては、この現象が発生します。

データの順序がばらけるように Insert していても、使用されているページが少ない状態 (データの初期状態) でも last-page insert は発生します。

「現在のページ割り当て状況で、データがページ内にどのように投入が行われるのか?」を意識しておくと、パフォーマンスチューニングの際に役に立つのではないでしょうか。

last-page insert が発生すると何が起こるか

それでは、SQL Server の実際の動作を見ながら、事象を考えていきたいと思います。

先ほど作成したテーブルに対して、複数のスレッドから次のクエリで Insert を実行するとどのようになるでしょうか。

DECLARE @cnt bigint = 0
while(@cnt<1000)
BEGIN
    INSERT INTO IdentityTest 
    VALUES(@@spid % 10, NEWID(), NEWID(), NEWID(), NEWID(), NEWID(), NEWID(), NEWID(), NEWID())
    SET @cnt += 1
END
&#91;/sourcecode&#93;
</pre>
</div>

<p>&#160;</p>

<p>この状態で実行中のクエリの状態を確認してみると、次のような待機事象の発生が確認できます。</p>

<p><a href="https://blog.engineer-memo.com/wp-content/2020/11/image-49.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; margin: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="https://blog.engineer-memo.com/wp-content/2020/11/image_thumb-49.png" width="531" height="176" /></a></p>

<p>「待機事象」は SQL Server のタスク (処理) が何らかの事象で待機している際の「待機の要因」を示したものです。</p>

<p>今回「PAGELATCH_SH」「PAGELATCH_EX」という待機事象が発生しており、その待機は「5:1:1646」というリソースで発生しているということが、この情報から確認することができます。</p>

<p>待機リソースの「5:1:1646」は待機が発生しているページの情報を表すものです。
  <br />この形式で取得されているページ情報は「&lt;Db id&gt;:&lt;File id&gt;:&lt;Page id&gt;」となっています。</p>

<p>取得できている情報であれば「Db id : 5 の File id : 1 (.mdf ファイル) の Page id : 1646」というページに対して「PAGELATCH の競合」が集中しているということを表しています。</p>

<p>それでは「PAGELATCH」とは何でしょうか?</p>

<p>冒頭で紹介した技術文書には次のように記載されています。</p>

<blockquote>
  <p><strong>PAGELATCH</strong> (データまたはインデックスページのラッチ) は、スレッド同期メカニズムです。 これは、バッファーキャッシュ内に配置されているデータベースページに短期的な物理アクセスを同期するために使われます。</p>

  <p><strong>PAGELATCH</strong> は、 <strong>PAGEIOLATCH</strong>とは異なります。 後者は、ディスクの読み取りまたは書き込みを行ったときに、ページへの物理的なアクセスを同期するために使用されます。</p>

  <p>ページラッチは、物理的なページ保護を実現するため、すべてのシステムで共通です。 集合インデックスは、先頭のキー列でデータを並べ替えます。 このため、シーケンシャル列でインデックスを作成すると、そのページが入力されるまで、インデックスの最後の同じページで新しいデータの挿入が行われます。 ただし、高負荷の場合、同時挿入操作では、B ツリーの最後のページで競合が発生する可能性があります。 この競合は、クラスター化インデックスと非クラスター化インデックスで発生する可能性があります。 これは、非クラスター化インデックスがリーフレベルのページを先頭のキーによって注文したためです。 この問題は、最後のページの挿入の競合とも呼ばれます。</p>

  <p>詳細については、次のホワイトペーパーを参照してください。</p>

  <p><a href="https://docs.microsoft.com/sql/relational-databases/diagnose-resolve-latch-contention">SQL Server でのラッチの競合の診断と解決</a></p>
</blockquote>

<p>SQL Server ではデータ (レコード) は、ページに格納されます。
  <br />ページ内では次のようにデータが格納されています。</p>

<p><a href="https://blog.engineer-memo.com/wp-content/2020/11/image-50.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; margin: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="https://blog.engineer-memo.com/wp-content/2020/11/image_thumb-50.png" width="385" height="248" /></a></p>

<p>データを Insert する際には、データが格納されている、このページという構造に対して、ページの整合性を保証するために、瞬間的にページラッチが取得されます。</p>
<strong>注 : 
  <br />今回の動作で取得されるのは「ロック」ではなく「ページラッチ」です。

  <br />SQL Server の同時実行性の話では、SQL Server だからページロック / テーブルロック / ロックエスカレーション がという話を聞くことがあります。

  <br />しかし、今回の内容は、ページラッチを主観点としていますので、ロックとは関係ありません。</strong>

<p>&#160;</p>

<p>ページラッチ (バッファーラッチ) については、<a href="https://docs.microsoft.com/ja-jp/sql/relational-databases/diagnose-resolve-latch-contention?view=sql-server-ver15">SQL Server でラッチの競合を診断および解決する</a> で次のように解説されています。</p>

<blockquote>
  <ul>
    <li><strong>バッファー (BUF) ラッチ:</strong> ユーザー オブジェクトのインデックスとデータ ページの整合性を保証するために使用されます。 また、SQL Server によってシステム オブジェクト用に使用されるデータ ページへのアクセスを保護するためにも使用されます。 たとえば、割り当てを管理するページは、バッファー ラッチによって保護されます。 これらには、Page Free Space (PFS)、Global Allocation Map (GAM)、Shared Global Allocation Map (SGAM)、Index Allocation Map (IAM) などのページが含まれます。 バッファー ラッチは、 <em>wait_type</em> が * <em>PAGELATCH_*</em> の <em>sys.dm_os_wait_stats</em> でレポートされます。</li>
  </ul>
</blockquote>

<p>ページラッチというと、「tempdb のデータファイルを分割し、システムアロケーションページの競合を減少させる」というような情報が有名ではありますが、システムオブジェクトを格納するためのページ以外でも、ページラッチは発生する可能性があります。</p>

<p>last-page insert が行われるデータ構造では、データは最後のページに格納されますので、大量の Insert が行われる状態では、同一のページに対しての変更が頻繁に発生する可能性が高くなります。</p>

<p>そのため、このようなデータ構造の場合は、「last-page insert による PAGELATCH 競合の発生頻度が高くなる可能性がある」ということになります。</p>

<h2>Identity を使用した場合のオーバーヘッド</h2>

<p>少し話は変わりますが、Identity を使用した場合のオーバーヘッドについても考えてみましょう。</p>

<p>SQL Server の Identity の仕様については <a href="https://docs.microsoft.com/ja-jp/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-ver15">CREATE TABLE (Transact-SQL) IDENTITY (プロパティ)</a> に記載されています。</p>

<p>Identity は自動的に ID (連番) を生成してくれます。
  <br />ID をどこまで取得したかを、毎回チェックしてディスクに永続化しているとオーバーヘッドが高くなりますので、昨今の SQL Server では、ID をキャッシュするようになっています。</p>

<blockquote>
  <p><strong>サーバーの再起動または他のエラーが発生した後の連続した値</strong> -SQL Server では、パフォーマンス上の理由から ID 値をキャッシュすることがあります。割り当てられた値の一部は、データベースの障害やサーバーの再起動が発生したときに失われることがあります。 その結果、挿入時に非連続的な ID 値が生成される場合があります。 非連続的な値が許可されない場合、アプリケーションは独自のメカニズムを使用してキー値を生成する必要があります。 シーケンス ジェネレーターを <strong>NOCACHE</strong> オプションを指定して使用すると、非連続的な値を絶対にコミットされないトランザクションに制限することができます。</p>
</blockquote>

<p>デフォルトの状態では、Identity は <a href="https://docs.microsoft.com/ja-jp/sql/t-sql/statements/create-sequence-transact-sql?redirectedfrom=MSDN&amp;view=sql-server-ver15">キャッシュを使用するシーケンスオブジェクト</a> のような形で動作をします。</p>

<p>Identity の場合、キャッシュするサイズを特定の値に、明示的に指定することはできないのですが、ID 値はキャッシュされています。(1,000 ぐらいずつキャッシュされているようですが)
  <br />これにより、再起動等でメモリの値がフラッシュされた時の連番の割り当て状況とのトレードオフで性能を向上することができます。</p>

<p>デフォルトでは、キャッシュが有効になっていますが、<a href="https://docs.microsoft.com/ja-jp/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql?view=sql-server-ver15">ALTER DATABASE SCOPED CONFIGURATION</a> の IDENTITY_CACHE の設定を使用することで、ID キャッシュを無効にすることができます。</p>

<blockquote>
  <p>IDENTITY_CACHE <strong>=</strong> { <strong>ON</strong> | OFF }</p>

  <p><strong>適用対象</strong> :SQL Server (開始値 SQL Server 2017 (14.x)) および Azure SQL データベース</p>

  <p>データベース レベルで ID キャッシュを有効または無効にします。 既定値は <strong>ON</strong> です。 ID キャッシュは、ID 列が含まれるテーブルでの INSERT パフォーマンスを改善するために使用されます。 サーバーが突然再起動したか、セカンダリ サーバーにフェールオーバーしたときに ID 列の値に隔たりができることを回避するには、IDENTITY_CACHE オプションを無効にします。 このオプションは、サーバー レベルのみならずデータベース レベルで設定可能という点を除き、既存の<a href="https://docs.microsoft.com/ja-jp/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver15">トレース フラグ 272</a> と似ています。</p>
</blockquote>

<p>無効にする際には次のステートメントを実行します。</p>

<p>
  <div id="scid:C89E2BDB-ADD3-4f7a-9810-1B7EACF446C1:a182a01d-c28b-4eae-a711-0d51248396d4" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px"><pre style=white-space:normal>

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF

これにより、ID キャッシュが無効になりますが、ID キャッシュを無効にすると、シーケンスジェネレーターの待ちが発生する可能性が高くなります。

具体的には、LATCH の待機として「METADATA_SEQUENCE_GENERATOR」が発生頻度が上昇します。

image

ID キャッシュが有効な場合にも、この待機は発生するのですが、発生頻度は ID キャッシュが無効な場合より抑えられます。

上が、ID キャッシュが無効の場合、下が、ID キャッシュが有効な場合の待機事象の発生状況なのですが、発生回数 / 待機時間が大幅に異なっていることが確認できますね。

image

 

Identity の値の保持

Identity は、「sys.syscolpars」という内部テーブルの「idtval」というバイナリ値で管理が行われています。

image

バイナリの先頭が ID キャッシュの値を示しており、「0xD107~」であれば、「0x07D1」=「2001」となりますので、この値が ID キャッシュが次にフラッシュされたタイミングで使用される値となります。

このデータはディスク上に永続化されていますので、ID キャッシュが利用し終わった場合には、「idtval」の値を更新する必要が出てくるのかと。

ID キャッシュの ON / OFF でこのデータの更新状況は変化しますので、「METADATA_SEQUENCE_GENERATOR」の待ちと、Identity の現在の値の更新については関連性があるかと。

 

last-page insert による同時実行性の低下を防ぐためには

冒頭で紹介した技術文書では、7 種類の方法について紹介されています。

  1. SQL Server 2019 で OPTIMIZE_FOR_SEQUENTIAL_KEY インデックスオプションを使用する
  2. 連続した値のクラスター化プライマリーキーを削除し、非クラスター化プライマリーキーで再作成する
  3. クラスター化プライマリーキーの定義を変更し、ID を後ろの項目にした複合キーに設定する
  4. テーブルにハッシュ項目を追加し、ハッシュ項目 + ID 列の複合キーに設定する
  5. GUID 列を追加し、GUID をプライマリーキーとして設定する
    • この方法はページ分割が発生しやすく、ページ密度が低下する可能性が高い
  6. パーティショニングとハッシュ項目を組み合わせ、データの分散を行う
  7. In-Memory OLTP 化を行う

「6.」に近い内容となりますが、先ほどのテーブル設定を次のように変更してみます。

CREATE PARTITION FUNCTION PF_IdentityTest(int)
AS RANGE RIGHT FOR VALUES(1,2,3,4,5,6,7,8,9)
GO

CREATE PARTITION SCHEME PS_IdentityTest
AS PARTITION PF_IdentityTest ALL TO ([PRIMARY])
GO

CREATE TABLE IdentityTest(
    C1 int identity NOT NULL,
    C2 int NOT NULL,
    C3 uniqueidentifier NOT NULL,
    C4 uniqueidentifier,
    C5 uniqueidentifier,
    C6 uniqueidentifier,
    C7 uniqueidentifier,
    C8 uniqueidentifier,
    C9 uniqueidentifier,
    C10 uniqueidentifier
)
ALTER TABLE IdentityTest 
ADD CONSTRAINT PK_IdentityTest PRIMARY KEY CLUSTERED(C1, C2)
ON PS_IdentityTest(C2)

 

上が、パーティショニングをする前、下が、パーティショニングをした後の待機事象です。

image

パーティショニングする前は、PAGELATCH_EX が上記の待機事象となっていました。

これは last-page insert が発生しているためです。

パーティショニングした後も PAGELATCH_EX は発生しているのですが、発生頻度が抑えられています。

パーティションキーに指定した C2 については、「@@spid % 10」の値が設定されています。

そのため、Sessoin ID によって、データの格納箇所が物理的に分散されるケースが出てくるため、last-page insert が抑制される可能性が上がっています。

 

OPTIMIZE_FOR_SEQUENTIAL_KEY でも PAGELATCH_EX の発生は抑えることができますが、この設定を追加した場合、「BTREE_INSERT_FLOW_CONTROL」の待機事象が発生する可能性がありますので、ワークロードにどのように影響を与えるかは、慎重に調査する必要があります。

 

last-page insert による同時実行性の低下は、物理的なデータの配置によって発生する可能性がある事象です。

テーブルにデータを投入する際に、「どのようにページに格納されるのか」を意識すると、最適なパフォーマンスを発揮できるのではないでしょうか。

Written by Masayuki.Ozawa

11月 19th, 2020 at 2:13 pm

Leave a Reply