SE の雑記

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

同時にファイルを拡張するためのトレースフラグ

one comment

全回の投稿の続きになります。
複数データファイルでデータベースを構成した場合のサイズ均等化について

■自動拡張時のファイルの拡張状況


データファイルを複数で構成した場合、ファイルによってサイズが異なっているとファイルの使用状況に偏りが出る傾向があるという事を前回の投稿で書いてみました。

初期状態からサイズを均等にしている場合はサイズに偏りが出るということはありませんが、ファイルの使用状況が上がり自動拡張が発生するとファイルのサイズに偏りが出てきます。
image

実際に確認をしてみたいと思います。

今回はこのようなファイル構成のデータベースを準備しました。
image

100MB のデータファイルが 2 つで構成されており、各ファイルに関しては 100MB ずつ自動拡張するように設定をしています。

それでは、このデータベースに 250 MB 程度のデータを INSERT してみたいと思います。
INSERT 後のファイルの状態がこちらになります。
image

最初のデータファイルのみが自動拡張されているのが [total_page_count] から確認できますね。
各ファイルのページの使用状況を確認してみます。
image

2 つめのデータファイルは [file_id = 3] となるのですが、こちらのファイルはすべてページが割り当てられています。
[file_id = 1] のファイルは自動拡張が発生しているので、未割当のページ (unallocated_extent_page_count) が存在しているのが確認できます。

自動拡張が発生すると、ファイルの空き領域が偏りますので I/O が発生するファイルが以下のようになります。
image

自動拡張が発生する前は両方のファイルに均等に I/O が発生しますが、自動拡張発生後はファイル 1 にしか空きが無い状態となりますので I/O が偏ります。

この状態になった場合、ファイル 2 はこの後拡張されないかというとそういう事はなく、ファイル 1 の空きが無くなったときに次はファイル 2 が拡張されます。
image

 

■トレースフラグ 1117 を使用した複数ファイルで拡張の実行


ファイルの拡張が発生した場合でも各ファイルに I/O を均等に発生させるためには、ファイルの自動拡張が発生したタイミングで
image

という拡張ではなく
image

というように、ファイルの拡張が発生した場合に複数ファイルを同時に拡張させることができれば空きが無くなってきて拡張がされても I/O を均等にすることができます。

このような拡張を行うためのトレースフラグが [1117] になります。

このトレースフラグですが、Fast Track Data Warehouse (FTDW) という DWH 系で使用される SQL Server のアプライアンスモデルでも使用されているようです。
SQL Server Fast Track Data Warehouse の実装

それでは、動作を確認してみたいと思います。

まずは各ファイルを 100 MB の状態にして、
image

以下のクエリでグローバルレベルのトレースフラグとして設定します。
# スタートアップオプションでトレースフラグを指定しても大丈夫です。

DBCC TRACEON(1117, ?1)

この状態で先ほどと同じで 250MB 程度のデータを INSERT してみます。
image

先ほどと異なり、両方のファイルの [
total_page_count] から確認することができます。

SSMS のレポートでファイルの拡張状態を確認してみます。
image

下の 2 行がトレースフラグを設定する前に発生した自動拡張のイベントになります。
上の 2 行がトレースフラグを設定した後に発生した自動拡張のイベントになります。

トレースフラグ設定後は各ファイルを同時に拡張しているのが確認できますね。

これがトレースフラグを設定した効果になります。
海外の情報を眺めていたところ tempdb で自動拡張が発生した際にファイルサイズを均等化するために使用されることが多いみたいですね。
# tempdb のデータファイルを複数ファイルで構成するというチューニングで拡張が発生した場合でもサイズを均等化するための保険として。

このトレースフラグは自動拡張を同時に発生させるものになるので自動拡張のサイズは事前に各ファイルで同じになるようにしておく必要があります。

自動拡張が発生しないサイジングをするのがそもそも大事ですが、自動拡張が発生した際に各ファイルのサイズを均等にするためには使えそうですね。

Share

Written by Masayuki.Ozawa

4月 28th, 2011 at 8:02 am

Posted in SQL Server

Tagged with

One Response to '同時にファイルを拡張するためのトレースフラグ'

Subscribe to comments with RSS or TrackBack to '同時にファイルを拡張するためのトレースフラグ'.

  1. […] こちらに関しては 同時にファイルを拡張するためのトレースフラグ […]

Leave a Reply