SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

ファイルグループにファイル追加後のデータ平準化

one comment

Twitter でご質問をいただきましたので軽くまとめて見たいと思います。

SQL Server のファイルグループは一つ以上のデータファイルで構成がされます。
image

ファイルグループはデータファイルの集合を管理する論理単位のため、SQL Server 上にデータとして情報が存在するだけですが、データファイルは実際のファイルになりますので、ディスク上にファイルが存在することになります。
image

データベースのデータはファイルグループと関連付けますので、データも絵に含めると以下のような形になります。
image

 

テーブルの件数が増加し、ディスクのサイズが枯渇 / ディスク負荷低減のため、ファイルグループに新規にデータファイル (ndf) を追加することがあります。
image

テーブルはファイルグループに関連づいていますので、そのファイルグループにデータファイルが追加されれば、使用できる領域が増えることになり、ディスクサイズの枯渇に関しては対応ができます。

ディスク負荷低減についてはどのようになるかを考えてみます。
ディスク追加後の各ファイルのデータの充填状況は以下のようになっています。
# 赤が使用領域となります。
image

追加したデータファイルにはデータは格納されていませんので、読み取りは今まで存在したデータファイルに、書き込みは新規に追加したデータファイルに集中することになります。
# 読み取りはデータが格納されているファイル / 書き込みは空きページの多いファイルに対して行われますので。

通常、同一のファイルグループにデータファイルを追加する場合は別のディスクにしますので、データの充填率は以下のようになっているとディスク I/O が分散されることになります。
image

データファイル追加後は上記の図のようにデータファイル内のデータは各ファイルに平準化して格納されていません。
平準化をするためにはデータ領域を再構築してデータを均等に配置する必要があります。

それでは、各状態を SQL Server で実際に確認をしてみたいと思います。

■初期状態

今回は [TEST] というデータベースを作成しています。
image

このデータベースですが単一のデータファイルで構成されています。
テスト用のテーブルを作成して、データを入れてありますので現在のファイルの使用状況を確認してみたいと思います。
ファイルの使用状況を確認するためには [DBCC SHOWFILESTATS] を使用します。
この DBCC コマンドを実行することで、現在のデータベースのファイル使用状況を取得することができます。
image

ファイルの使用状況はエクステントで表示されます。
[TEST.mdf] は [1,600] エクステント (1,600 × 64 KB = 102,400 KB = 100 MB) 割り当てられ、そのうち [339] エクステント (339 × 64 KB = 21,696 KB = 21 MB)  が使用されていることが確認できます。

■ファイル追加後のデータ充填状況

それではファイルグループにデータファイルを追加して、データの充填状況を確認してみたいと思います。
# ドライブ構成の関係で既存のファイルと同じドライブに格納してしまっています…。
image

今回は [TEST2.ndf] というファイルを追加しています。

それではデータファイル追加後の使用状況を取得してデータの充填状況を確認してみたいと思います。
image

新規に吹ファイルが追加 (Fileid = 3) され、UsedExtents が [1] (管理用ページを作成する必要があるため) となっています。
データファイルを追加後はこのような状態となります。
image

 

■データを平準化する

データを平準化するためにはどうすればいいかというと、データ領域の再構築を行います。
# ヒープの場合は別の機会に考えて見たいと思います…。
再構築はインデックスの再構築をすれば実施できますので、インデックスの [REBUILD] を行います。

インデックスの再構築では、エクステントも含めてデータを再配置し直しますので、データが各ファイルに均等に再配置されます。

インデックスの再構成 (REORGANIZE) では、エクステントの再割り当てはせずに既に存在しているエクステント内でページの並び替えを行いますのでデータは平準化されません。

それでは、実際に確認をしてみたいと思います。

まずはインデックスを再構成してみます。
# GUID をキーにしてテスト用のデータを生成しているので断片化が著しいです…。
image

再構成後のデータ充填率を取得すると各ファイルにデータが分散していないことが確認できます。
image

それでは、再構成 (REORGANIZE) ではなく 再構築 (REBUILD) を行ってみます。
image

完全に平準化 (199 ページずつ) とはいきませんでしたが今まで UsedExtents が 1 となっていた TEST2.ndf のエクステントが利用されていることが確認できます。
image

データファイルの追加後にデータ領域を再構築することで、既に格納されているデータについて使用するファイルを平準化することができます。

 

■ファイルサイズの差によるデータファイルの使用状況

データファイルのサイズを変更して、

  • TEST.mdf : 100 MB
  • TEST2.ndf : 1 MB

にしてみました。
# 自動拡張は有効です。
image

この状態でデータを挿入するとどうなるか試してみます。
image

TEST2.ndf は 1 MB のサイズに変更しましたので 16 × 64 KB = 1,024 MB と割り当てていたサイズはすべて使用されていますが、自動拡張はせずに残りのデータは TEST.mdf に格納がされています。
データ格納時は空きページを考慮してデータを格納していきますので空きがある TEST.mdf が使用されたことになります。

これは極端な例ですので、

  • TEST.mdf : 100 MB
  • TEST2.ndf : 20 MB

として、
image

両方のファイルで分散させたデータが格納できる状態にしてデータを挿入してみます。
image

この場合は両ファイルに十分な空き領域がありますので、両方のファイルが均等と言っていいレベルで使用されています。

 

使用されるファイルは空き領域 (ページ) が考慮されて使われますので、均等にデータを書き込んだ結果片一方の空きページに余裕が無くなってしまうと思ったようにディスク利用が分散されない可能性があります。

使用領域を増やすのではなく、ディスクのアクセス効率を向上するためにファイルを追加した場合は追加後に各ファイルでデータを平準化するかを考慮し、作業 / 確認をした方がよいかもしれないですね。

Written by masayuki.ozawa

12月 2nd, 2010 at 6:38 pm

Posted in SQL Server

Tagged with

One Response to 'ファイルグループにファイル追加後のデータ平準化'

Subscribe to comments with RSS or TrackBack to 'ファイルグループにファイル追加後のデータ平準化'.

  1. […] ファイルグループにファイル追加後のデータ平準化 追加したデータファイルからデータを移動する方法 データ平準化の再構成時のファイルアクセス状況について […]

    複数ファイルでデータベースを構成した場合のサイズ均等化について « SE の雑記

    26 4月 11 at 08:12

Leave a Reply

*