追加コスト 0 で行う SQL Server のパフォーマンス改善・ボトルネック解消 その8

こんにちは、nagino です。

今日は FILL FACTOR と （PAD_INDEX と）ページ分割についてです。

SQL Server はデータやインデックスを 8KB のページという単位で管理します。

ですので、1 行が 5KB あるような巨大なデータは、ページあたり 1 行しか格納できず、残りの 3KB は使用できない空き領域となります。

ディスクで言うセクタに近いものになります。

さて、例えば 1 行が 800B あるデータが 1 ページに 10 行格納されているとします。

ここにデータを挿入する場合、ページに入りきらないため、データのおよそ半分を新しいページに移動します。

これがページ分割です。

他にも可変長列のデータが更新で大きくなったときにも、ページ分割は発生します。

ページ分割は、おおよそ 4KB ほどのデータの移動が発生し、またページの確保などが発生するため、パフォーマンスがその分劣化します。

一方、データを末尾に追加する場合、FILL FACTOR で指定した割合までしかデータを追加せず、空き領域を残します。

このため、ある程度空き領域を各ページに残しておくことができるため、その後データの挿入や可変長列の更新があっても、ページ分割が発生する可能性が減少します。

INSERT でも、データの追加される位置によって動作が異なるということになります。

ただし、FILL FACTOR を大きく指定すれば、同じデータ量でも使用するページ数が増えるため、その分データキャッシュが増えることでメモリを消費し、ディスク I/O が増加することでパフォーマンスが劣化します。

また、クラスタ化インデックスが IDENTITY の列の場合、データの挿入は起こらないため、可変長列の更新によるページ分割しか起こりません。

特にマスタや過去データのように、更新がほとんど行われないようなデータの場合は、FILL FACTOR をデフォルトの 0 ないし 100 にしておくほうがパフォーマンス上優れます。

一方、データの挿入や可変長列の更新が頻繁に発生する場合は、FILL FACTOR を若干押さえた値にすることで、更新処理のパフォーマンスの改善が期待できます。

ちなみに、インデックスの再構築時に PAD_INDEX を指定することで、インデックスのページにも FILL FACTOR と同様の充填率を指定することができます。

データの追加・挿入の特性、可変長列の更新の特性、検索と更新のバランス、ディスクとメモリのリソース量に応じて調整が必要な、ちょっと難しい領域ですが、OLTP 系でデータの更新処理のパフォーマンスが求められる際には検討の価値があるパラメーターになります。

まあ、たいていの場合は検索重視のため、デフォルトの 0 のままが最適なのですが・・・。