SE の雑記

SQL Server でデータを格納する際のページ密度とバッファキャッシュ (データをキャッシュするためのメモリ領域) の関係について軽くまとめてみたいと思います。

■ページ密度について

SQL Server のページサイズは 8KB となっています。
ページ内には最大で 8,060 バイトのレコードを格納することができます。
# ヘッダー情報とオフセット領域を確保する必要があるため 8,192 バイトをフルに使用することはできません。

以下のようなテーブルにデータを格納した場合、どのようにページが使用されるでしょう。

char(4100) の列のみを持つ単純なテーブルです。
2 レコード格納する場合には 8,200 バイトのデータ領域が必要となります。
ページ内には 8,060 バイトのデータしか格納することができません。BLOB は除きますが通常のデータ型はページをまたいでデータを格納することができません。
そのため、2 レコード格納するためには以下のようにページが利用されます。

ページ間で列のデータをまたぐことができないため、2 レコード格納するために 2 ページ利用されています。
ページ内で1 レコード分のデータは領域を確保できているのですが、2 レコード目のデータ領域としては使用できない (空きサイズが足りていない) ため、未使用領域として使用されない状態となります。
1 ページに 1 レコードしか入っていない状態になりますので、ページ内のレコードの密度が低い状態となります。
# レコード挿入による断片化を発生させないためのテクニックとして、このような密度の低い状態を作り出す手法もあるのでこの状態が必ずしも悪いわけではありません。
 

■ページ密度の低いページを読み込んだ際のバッファキャッシュの状態

Char(4100) の列のみを格納したテーブルに対して、10 万件のレコードを格納しこのテーブルを全件取得した際にバッファキャッシュの状態はどうなるでしょう。
実際にデータで使用している領域は
4,100 バイト × 10 万件 = 410,000,000 バイト = 410 MB
になりますので、データのサイズ分のメモリが確保されるでしょうか?
この時、バッファキャッシュは以下のような状態になります。

データのキャッシュで 800MB 程度使用されていることが確認できます。
このことから、
4,100 バイト × 10 万件 = 410,000,000 バイト = 410 MB
ではなく、
10 万ページ × 8 KB = 800,000,000 バイト = 800 MB
使用されていることになります。
見方を変えてパフォーマンスモニタではなく、DMV で情報を取得してみます。

管理用のページも読み込まれているため 10 万ページちょうどとはなっていませんが、ほぼ同等のページをバッファキャッシュで使用していることが確認できます。
使用している DMV では対象としたデータベースで使用しているページ数以外にページ内の空き領域を確認することができるためこの合計値を取得すると、3,960 バイト× 10 万ページの結果とほぼ同等の領域が空きになっていることが確認できます。
SQL Server では I/O の基本単位として [ページ] が利用されます。
ページ内に空きがあったとしても 1 ページを読み込むためには 8KB のメモリが使用されるという動作になるようです。
この動作を気にする必要がある要因の一つとして [ページの断片化] が考えられます。
SQL Server ではページ内にデータを挿入する際に、空き領域がない場合は 50/50 分割を発生させ、ページ内のデータを他のページに移動させることにより空き領域を作成します。
ページ分割を発生させたことにより空き領域ができ、ページ内のデータ密度が下がった状態になると言えます。
断片化が発生すると読み取る必要のあるページ数が多くなりますので、使用しているディスクのサイズが増加 = 使用するメモリの増加という状態となります。
必ずデータ密度を高くしないといけないかというとそういうこともなく、データ密度が高いと、可変長列に格納されているデータ長の変更やページの途中にレコードを挿入した際にページ分割が発生しやすい状態となりますので、どれだけデータ密度を高くするか (FILLFACOTR の設定に影響) はテーブルの用途や更新のトレンドを考慮する必要があります。
# 読み取り専用のテーブルであれば密度は高いほうが良いと思いますが。
ページ密度を高くするとトランザクションログの負荷が減ることがあります。
これについては別の機会にまとめてみたいと思います。