SQL Server ではファイルグループに複数のデータファイルを含めることができます。
このようなファイル構成の場合、どのようにデータが書き込まれるかを見てみたいと思います。
■複数データファイルで構成している場合のデータ書き込み
データファイルを複数の物理ディスクに配置することでデータを分散させることができ、ディスク負荷を抑えることができます。
このファイルに 1 レコード 700 バイトのレコードを 20 件挿入してみます。
今回は 4 つのデータファイルを使用しているので、各データファイルに 5 件ずつデータが挿入され、分散されるでしょうか??
SQL Server ではデータは 8KB のページに対して書き込みが行われますが、ページを格納するための領域はエクステントとして、64KB 領域の中に確保されます。
データの書き込みを行う場合には
- 64KB のエクステントを特定のデータファイルに確保
- エクステント内の 8KB ページを使用してデータを格納
という流れになります。
そのためデータの書き込みを行う際にはエクステントを確保したデータファイル内にデータの書き込みが行われます。
先ほどのデータ書き込みであれば特定のデータファイル に 20 件のレコードが格納されることになります。
10 レコードで 8KB ですので現状、64KB エクステント内の 2 ページ分の領域が使用されていることになります。
エクステント内には 8 ページ格納できますので、さらに 60 レコード (6ページ分) 挿入してファイルの使用状況を確認してみます。
エクステント内のページが使用し終わるまでは特定のファイルに対して書き込みが行われます。
今の状況で、エクステント内のページはすべて使用し終えている状態となっています。
この状態でさらに 1 レコード挿入してみます。
エクステント内のページはすべて使用が終わっているため新しいエクステントの割り当てが行われます。
この時のエクステントの割り当ては今まで使用していたファイルグループとは異なるファイルグループに対して行われます。
# スタートアップオプションや、ファイルの空き状況によっては同じファイルグループが使用されることもありますが。
ファイルグループに複数のファイルを配置した場合、エクステント単位でデータの書き込みが各ファイルグループに分散されることになります。
そのため、単純に 1 レコードの書き込み速度を上げたいという場合には、ファイルグループ内にデータファイルを増やすのではなく、データファイルに使用するディスクのスピンドル数を増やす必要が出てきます。
ストレージのディスク本数には限りがありますので、データファイル数とスピンドル数をどのようにするかは悩ましいですが、ディスク I/O の効率を考える場合にはこの辺の動作を意識しておきたいですね。