全回の投稿の続きになります。
複数データファイルでデータベースを構成した場合のサイズ均等化について
■自動拡張時のファイルの拡張状況
データファイルを複数で構成した場合、ファイルによってサイズが異なっているとファイルの使用状況に偏りが出る傾向があるという事を前回の投稿で書いてみました。
初期状態からサイズを均等にしている場合はサイズに偏りが出るということはありませんが、ファイルの使用状況が上がり自動拡張が発生するとファイルのサイズに偏りが出てきます。
実際に確認をしてみたいと思います。
100MB のデータファイルが 2 つで構成されており、各ファイルに関しては 100MB ずつ自動拡張するように設定をしています。
それでは、このデータベースに 250 MB 程度のデータを INSERT してみたいと思います。
INSERT 後のファイルの状態がこちらになります。
最初のデータファイルのみが自動拡張されているのが [total_page_count] から確認できますね。
各ファイルのページの使用状況を確認してみます。
2 つめのデータファイルは [file_id = 3] となるのですが、こちらのファイルはすべてページが割り当てられています。
[file_id = 1] のファイルは自動拡張が発生しているので、未割当のページ (unallocated_extent_page_count) が存在しているのが確認できます。
自動拡張が発生すると、ファイルの空き領域が偏りますので I/O が発生するファイルが以下のようになります。
自動拡張が発生する前は両方のファイルに均等に I/O が発生しますが、自動拡張発生後はファイル 1 にしか空きが無い状態となりますので I/O が偏ります。
この状態になった場合、ファイル 2 はこの後拡張されないかというとそういう事はなく、ファイル 1 の空きが無くなったときに次はファイル 2 が拡張されます。
■トレースフラグ 1117 を使用した複数ファイルで拡張の実行
ファイルの拡張が発生した場合でも各ファイルに I/O を均等に発生させるためには、ファイルの自動拡張が発生したタイミングで
というように、ファイルの拡張が発生した場合に複数ファイルを同時に拡張させることができれば空きが無くなってきて拡張がされても I/O を均等にすることができます。
このような拡張を行うためのトレースフラグが [1117] になります。
このトレースフラグですが、Fast Track Data Warehouse (FTDW) という DWH 系で使用される SQL Server のアプライアンスモデルでも使用されているようです。
SQL Server Fast Track Data Warehouse の実装
それでは、動作を確認してみたいと思います。
以下のクエリでグローバルレベルのトレースフラグとして設定します。
# スタートアップオプションでトレースフラグを指定しても大丈夫です。
DBCC TRACEON(1117, ?1) |
この状態で先ほどと同じで 250MB 程度のデータを INSERT してみます。
先ほどと異なり、両方のファイルの [
total_page_count] から確認することができます。
下の 2 行がトレースフラグを設定する前に発生した自動拡張のイベントになります。
上の 2 行がトレースフラグを設定した後に発生した自動拡張のイベントになります。
トレースフラグ設定後は各ファイルを同時に拡張しているのが確認できますね。
これがトレースフラグを設定した効果になります。
海外の情報を眺めていたところ tempdb で自動拡張が発生した際にファイルサイズを均等化するために使用されることが多いみたいですね。
# tempdb のデータファイルを複数ファイルで構成するというチューニングで拡張が発生した場合でもサイズを均等化するための保険として。
このトレースフラグは自動拡張を同時に発生させるものになるので自動拡張のサイズは事前に各ファイルで同じになるようにしておく必要があります。
自動拡張が発生しないサイジングをするのがそもそも大事ですが、自動拡張が発生した際に各ファイルのサイズを均等にするためには使えそうですね。
[…] こちらに関しては 同時にファイルを拡張するためのトレースフラグ […]
SQL Server のスタートアップオプション -E の動きを見てみる « SE の雑記
8 9月 11 at 23:11