排他制御の落とし穴を避けるインデックス設計：Dr. K's SQL Serverチューニング研修（5）（1/3 ページ）

[熊澤幸生, 工藤淳（インタビュアー），CSK Winテクノロジ/オフィスローグ]

SQL Serverは一般的にチューニング不要のデータベースと認識されている。しかし基幹系業務システムへの導入が進むにつれて、パフォーマンス・チューニングのニーズは急速に高まってきた。そこで本記事では、日本におけるSQL Serverコンサルタントの第一人者、熊澤幸生氏にSQL Serverチューニングのノウハウを語っていただくことにした。インタビュアーはSQL Serverへの造詣が深いITジャーナリスト、工藤淳氏が担当する。（編集部）

　前回の記事「排他制御メカニズムから“待ち”原因を究明する」では、wait事象を引き起こす原因の中から排他制御について解説しました。ロックとラッチ、ロックの粒度、複数粒度でのロックとロックマネージャといったSQL Serverのアーキテクチャを理解していただけたと思います。

　本稿ではさらに一歩進んで、ページ分割のメカニズム、およびインデックスと排他制御（ラッチとロック）の関係を解説します。ページ分割が発生すると、領域確保のためのラッチ処理を中心にした変化が起こり、これによってトランザクションの処理時間が拡大してサーバ全体のパフォーマンスを低下させます。後半では、このような排他制御対象オブジェクトの変化を考慮して、パフォーマンスを向上させるクラスタ化インデックス選択のヒントも紹介しましょう。

ページ分割の重要性に気付かないうちは、
パフォーマンスも極められない

ページ分割が起こる原理

　ページ分割の話に入る前に、まずデータページの構造を思い出しましょう。前回説明したデータページの内部構造の図を再掲しておきます（図1）。

　図1　データページの内部構造

　SQL Serverでは1つのテーブルに1つのクラスタ化インデックスを定義でき、ほとんどの場合は、主キーに対して使用します。クラスタ化インデックスを使用すると、データページ内には主キーでソートされた順序でデータ行が格納されます。つまり、Bツリー構造であるインデックスページのルートノードおよび中間ノードには、キー値と対応するページへのポインタが格納され、リーフノード（ヒープ）には実際のデータ行が格納されたデータページが配置されているのです（図2）。

図2　クラスタ化インデックスを用いたテーブルのインデックスとデータページ構造

　さてここで、データ行のINSERT処理を考えてみましょう。クラスタ化インデックスを使用している場合、データ行の挿入は、論理的なソート順で格納されますから、新たなデータ行がどのページにINSERTされるかは、インデックス情報の参照によって決定されます。もし対象となるデータページに十分な空き領域が存在しない場合は、データページを前後2つに分割する必要に迫られます。これがページ分割です。

ページ分割のオーバーヘッド

　ページ分割がパフォーマンスに与える影響の大きさに着目しましょう。データの挿入時にはしばしばページ分割が発生します。このページ分割は非常に大きいオーバーヘッドとなります。なぜならページ分割の処理中には広範囲の排他制御が必要で、それがページ分割完了までの“ユーザーが体感する処理待ち”につながるからです。

　図3を見てください。ここで「P1」というページの中に「1、4、6、8」という値が入っていたとします。ここへ「7」という新しい値を追加したいのですが、データページ内に十分な空き領域がありません。そこでIAMと呼ばれるビットマップを参照し、新たな空きページ領域「P1'」を確保します。その後、現在のページを50％ずつに分割して、確保したデータページ「P1'」に「6、8」を移動します。同時に「P1'」に対応する新たなインデックスページ内のインデックス情報とポインタ（ページ番号）を挿入します。こうすれば「7」はインデックスを参照することにより、「6、8」の間に入ることができます。これによってページ全体の整合性が保たれます。しかし、「P1」、「P1'」、「P2」は、物理的に連続したデータ領域ではなく、領域の断片化が発生したことになります。

　図3　ページ分割の処理とオーバーヘッド

　各ページは、「P1」の次は「P2」であるといった、ページの論理的な順番を保証する2つのポインタ情報（自分のページから見た前後のページ番号）をページヘッダの中に格納しています。しかし、上に挙げたような書き換えを行った場合、ページそのものが新たに追加されるので、行単位の排他ロックと自分のページヘッダへのラッチ（ページ内の空き領域情報更新と最終LSN更新）では間に合わなくなってきます。

　そこで格納ページヘッダへのラッチ、前後のページヘッダラッチ（ページポインタの値を変更）、インデックスページへのラッチとインデックス情報の挿入を行わなくてはなりません。一般にデータの追加や削除では、行レベルの排他制御で可能と思われがちですが、こうしたページ分割を伴う作業では、ラッチ処理による排他制御の対象オブジェクトの拡大が発生することを知っておいてください。

　また、ページ分割処理により、インデックスページにも新たな行が追加されます。もし対象となるインデックスページに十分な空き領域がない場合、インデックスページでも同様のページ分割が発生します。

　もっとも実際のデータベース運用ユーザーは、こうしたページ分割によってページの整合性を保つということ自体を理解していない人も少なくありません。どうも近ごろパフォーマンスが出ないというので調査してみると、稼働開始から一度もこうしたインデックスの再編成を行ったことがなく、もはや内部は領域の断片化によりスパゲティ状態で、それがパフォーマンス悪化の大きな要因であることも珍しくないのです。お客さまに「DBCC SHOWCONTIG」（表のデータとインデックスの断片化に関する情報を表示するコマンド）の出力結果分析を提示して、初めて驚かれるケースも少なくありません。（次ページへ続く）