断片化について

神谷 雅紀
Escalation Engineer

断片化とは？

一般的に、断片化と言われる状況は、平均ページ密度 (average page density) が低い状況か、または、論理スキャンフラグメンテーション (logical scan fragmentation) の値が高い状況です。

平均ページ密度とは？

平均ページ密度とは、あるテーブルもしくはインデックスに関して、1 ページ (8KB) に、平均どの程度データが詰まっているのかを表しています。値が小さいほど断片化の度合いが大きいことを示しています。

例えば、平均ページ密度が 90% であれば、1 ページ 8KB の 90%、つまり、約 7.2KB はデータを格納するために使用されており、残りの 0.8KB が空きということになります。この場合、1 行のサイズが 0.8KB 未満であれば、このページにもう 1 行入る可能性がありますが、それ以上の大きさであれば、このページにはもう新しい行は入らないので、ページ密度は 90% が最大ということになります。このように、平均ページ密度は、必ずしも 100% にはなりません。

平均ページ密度は、DBCC SHOWCONTIG コマンドの出力に含まれている「平均ページ密度 (Avg. Page Density)」、または sys.dm_db_index_physical_stats のavg_page_space_used_in_percent で確認することができます。

尚、ページ密度は、インデックスを新規作成したり、再構築したりする場合であれば、FILLFACTOR によって指定可能な値です。

平均ページ密度が低いと何が起こるのか？

平均ページ密度が低いということは、1 ページあたりに格納されているデータ量が少ないということです。言い換えれば、データベースファイルから同じデータ量を読み取るために、平均ページ密度が高い場合よりも低い場合の方が、多くのページを読み取る必要がある、つまり、読み取り量が多くなるということになります。ファイルから読み取る量が多くなれば、読み取りのために必要な時間は長くなります。また、読み取る量が多くなれば、それをキャッシュするために必要なメモリも多く必要になります。

例えば、あるテーブルに 10000 行が格納されており、使用しているページ数が 100 ページ、この時の平均ページ密度が 90% であったとします。このテーブルの平均ページ密度が、半分の 45% になったとします。このテーブル全体を読み取る処理が実行されると、平均ページ密度が 90% であった頃に比べて 45% の時には 2 倍の 200 ページの読み取りが必要になります。大量のデータを読み取る必要のある処理、例えば、データウェアハウスなどでは、ページ密度が低くなると、パフォーマンスが悪化します。

しかし、一方で、アクセスするデータ量の少ない OLTP のような処理はどうでしょうか？典型的な OLTP では、ひとつのクエリでは数行にアクセスするのみです。さらにそのアクセスにはインデックスが使用され、検索範囲が限定されています。インデックスによる行へのアクセスは、インデックスのルートページ → 中間ページ (存在する場合のみ) → リーフページという経路でのアクセスになります。仮に、平均ページ密度が低くなり、インデックス階層がひとつ増えたとしても、2 ページが 3 ページに、もしくは、3 ページが 4 ページといったように増える読み取り量は 1 ページのみであり、これがパフォーマンスに影響を与えることはほとんどありません。従って、OLTP 環境では、ほとんどの場合、平均ページ密度を意識する必要はないでしょう。

論理スキャンフラグメンテーションとは？

論理スキャンフラグメンテーションは、インデックスのリーフページ (インデックス B-Tree の最下層ページ) を構成するページの論理的な並び順と物理的な並び順が異なっている度合いを表しています。DBCC SHOWCONTIG の「論理スキャンの断片化 (Logical Scan Fragmentation)」、または、sys.dm_db_index_physical_stats の avg_fragmentation_in_percent で確認することができます。

各ページには、ファイルの先頭から順にページ番号が振られています。各ページのページヘッダーには、そのページの前のページ (prev page) のページ番号と次のページ (next page) のページ番号が記録されています。ページヘッダーに記録されている前のページと次のページにより表されるページの順番が、論理的なページの並び順です。この論理的なページの並び順とファイル先頭から順に振られているページ番号の順番が一致しない状況が、論理的な並び順と物理的な並び順が異なっている状況です。

例えば、あるインデックスは、ページヘッダーの次のページをたどっていくと、ページ 80 –> 81 –> 82 –> 83 –> 84 –> 85 –> 160 –> 86 –> 87 となっていたとします。80 から 85 と 86, 87 の並びは、ページ番号の並びと一致しますので、論理的な並び順と物理的な並び順は同じです。しかし、85 –> 160 –> 86 の部分は、一致しません。つまり、ページの論理的な並び順と物理的な並び順が異なっている状態で、論理スキャンフラグメンテーションが発生しています。この例では、80 から 85 までの断片 (フラグメント)、160 のフラグメント、86 と 87 のフラグメントの 3 つのフラグメントがあります。このインデックスをスキャンする場合、スキャン中には、ページが連続していない 85 –> 160 と 160 –> 86 の 2 箇所でジャンプすることになり、全ページ数が 9 ページであるため、論理スキャンフラグメンテーションの値は、2 / 9 * 100 = 22.22% となります。

論理スキャンフラグメンテーションは、値が大きいほど、断片化の度合いが大きいことを示しています。しかし、論理スキャンフラグメンテーションの値は、インデックスの総ページ数を分母とした値であるため、インデックスの総ページ数が少ないと、言い換えれば、小さなインデックスでは、必然的に大きな値になります。また、小さなインデックスでは大きな値になる要因として、オブジェクトの最初のページは、混合エクステントから割り当てられることも影響しています。混合エクステントは、複数のオブジェクトに共有されるエクステントであり、ひとつのオブジェクトに属するページが複数割り当てられることのないエクステントです。

論理スキャンフラグメンテーションが発生していると何が起こるのか？

平均ページ密度とは異なり、論理スキャンフラグメンテーションの値が変わっても、読み取り数は変化しません。読み取ったデータをキャッシュするために使用するメモリ量も変わりません。しかし、読み取りを行うために必要となる API 呼び出しの回数は変わる可能性があります。ディスクに対する I/O 要求は、1 度の API 読み出しでは連続した領域に対してしか行うことができません。ReadFile API にしても、ReadFileScatter API にしても、読み取りの開始点とそこからのサイズしか指定できないため、複数のフラグメントを一度に読み取ることはできません。それ以外では、先行読み取り (read ahead) の効率が落ちる可能性がある程度でしょうか。SQL Server の動きに与える影響として思い付くのはこの程度です。

では、これがパフォーマンスに与える影響はどの程度でしょうか？ハードウェアの観点では、連続しないデータの読み取りでは、ハードディスクのアームが動く量が多くなるなどのオーバーヘッドはあるかと思いますが、実際のところ、私自身はテストをしたことがないので、その影響は全く分かりません。いずれにしても、平均ページ密度の場合と同じように、大量のデータを読み取る処理でなければ、ほとんど影響はないでしょう。

論理スキャンフラグメンテーションが何パーセントになったらインデックスの再構築 (rebuild) を、何パーセントなら再構成 (reorg) を、といった話もありますが、おそらくそれらもテストから導き出されたものであり、すべての環境に対して画一的に適用できるものではないのではないかと思います。そのため、論理スキャンフラグメンテーションがパフォーマンスに与える影響やインデックスの再構築 / 再構成の効果については、個々の環境で検証してみることが必要かと思います。もしかすると、現在大きな時間を割いて実行しているインデックスの再構築は、パフォーマンスにはほとんど効果がないかもしれません。