Twitter でご質問をいただきましたので軽くまとめて見たいと思います。
SQL Server のファイルグループは一つ以上のデータファイルで構成がされます。
ファイルグループはデータファイルの集合を管理する論理単位のため、SQL Server 上にデータとして情報が存在するだけですが、データファイルは実際のファイルになりますので、ディスク上にファイルが存在することになります。
データベースのデータはファイルグループと関連付けますので、データも絵に含めると以下のような形になります。
テーブルの件数が増加し、ディスクのサイズが枯渇 / ディスク負荷低減のため、ファイルグループに新規にデータファイル (ndf) を追加することがあります。
テーブルはファイルグループに関連づいていますので、そのファイルグループにデータファイルが追加されれば、使用できる領域が増えることになり、ディスクサイズの枯渇に関しては対応ができます。
ディスク負荷低減についてはどのようになるかを考えてみます。
ディスク追加後の各ファイルのデータの充填状況は以下のようになっています。
# 赤が使用領域となります。
追加したデータファイルにはデータは格納されていませんので、読み取りは今まで存在したデータファイルに、書き込みは新規に追加したデータファイルに集中することになります。
# 読み取りはデータが格納されているファイル / 書き込みは空きページの多いファイルに対して行われますので。
通常、同一のファイルグループにデータファイルを追加する場合は別のディスクにしますので、データの充填率は以下のようになっているとディスク I/O が分散されることになります。
データファイル追加後は上記の図のようにデータファイル内のデータは各ファイルに平準化して格納されていません。
平準化をするためにはデータ領域を再構築してデータを均等に配置する必要があります。
それでは、各状態を SQL Server で実際に確認をしてみたいと思います。
■初期状態
今回は [TEST] というデータベースを作成しています。
このデータベースですが単一のデータファイルで構成されています。
テスト用のテーブルを作成して、データを入れてありますので現在のファイルの使用状況を確認してみたいと思います。
ファイルの使用状況を確認するためには [DBCC SHOWFILESTATS] を使用します。
この DBCC コマンドを実行することで、現在のデータベースのファイル使用状況を取得することができます。
ファイルの使用状況はエクステントで表示されます。
[TEST.mdf] は [1,600] エクステント (1,600 × 64 KB = 102,400 KB = 100 MB) 割り当てられ、そのうち [339] エクステント (339 × 64 KB = 21,696 KB = 21 MB) が使用されていることが確認できます。
■ファイル追加後のデータ充填状況
それではファイルグループにデータファイルを追加して、データの充填状況を確認してみたいと思います。
# ドライブ構成の関係で既存のファイルと同じドライブに格納してしまっています…。
今回は [TEST2.ndf] というファイルを追加しています。
それではデータファイル追加後の使用状況を取得してデータの充填状況を確認してみたいと思います。
新規に吹ファイルが追加 (Fileid = 3) され、UsedExtents が [1] (管理用ページを作成する必要があるため) となっています。
データファイルを追加後はこのような状態となります。
■データを平準化する
データを平準化するためにはどうすればいいかというと、データ領域の再構築を行います。
# ヒープの場合は別の機会に考えて見たいと思います…。
再構築はインデックスの再構築をすれば実施できますので、インデックスの [REBUILD] を行います。
インデックスの再構築では、エクステントも含めてデータを再配置し直しますので、データが各ファイルに均等に再配置されます。
インデックスの再構成 (REORGANIZE) では、エクステントの再割り当てはせずに既に存在しているエクステント内でページの並び替えを行いますのでデータは平準化されません。
それでは、実際に確認をしてみたいと思います。
まずはインデックスを再構成してみます。
# GUID をキーにしてテスト用のデータを生成しているので断片化が著しいです…。
再構成後のデータ充填率を取得すると各ファイルにデータが分散していないことが確認できます。
それでは、再構成 (REORGANIZE) ではなく 再構築 (REBUILD) を行ってみます。
完全に平準化 (199 ページずつ) とはいきませんでしたが今まで UsedExtents が 1 となっていた TEST2.ndf のエクステントが利用されていることが確認できます。
データファイルの追加後にデータ領域を再構築することで、既に格納されているデータについて使用するファイルを平準化することができます。
■ファイルサイズの差によるデータファイルの使用状況
データファイルのサイズを変更して、
- TEST.mdf : 100 MB
- TEST2.ndf : 1 MB
にしてみました。
# 自動拡張は有効です。
この状態でデータを挿入するとどうなるか試してみます。
TEST2.ndf は 1 MB のサイズに変更しましたので 16 × 64 KB = 1,024 MB と割り当てていたサイズはすべて使用されていますが、自動拡張はせずに残りのデータは TEST.mdf に格納がされています。
データ格納時は空きページを考慮してデータを格納していきますので空きがある TEST.mdf が使用されたことになります。
これは極端な例ですので、
- TEST.mdf : 100 MB
- TEST2.ndf : 20 MB
として、
両方のファイルで分散させたデータが格納できる状態にしてデータを挿入してみます。
この場合は両ファイルに十分な空き領域がありますので、両方のファイルが均等と言っていいレベルで使用されています。
使用されるファイルは空き領域 (ページ) が考慮されて使われますので、均等にデータを書き込んだ結果片一方の空きページに余裕が無くなってしまうと思ったようにディスク利用が分散されない可能性があります。
使用領域を増やすのではなく、ディスクのアクセス効率を向上するためにファイルを追加した場合は追加後に各ファイルでデータを平準化するかを考慮し、作業 / 確認をした方がよいかもしれないですね。
