64 ビットの SQL Server ではスタートアップ (起動) オプションとして -E というオプションがあります。
SQL Server サービスのスタートアップ オプションの使用
ファイル グループ内の各ファイルに割り当てられるエクステントの数を増やします。
このオプションは、インデックス スキャンまたはデータ スキャンを実行するユーザーの数が限られているデータ ウェアハウス アプリケーションで役立つ場合があります。
パフォーマンスに悪影響を及ぼす可能性があるため、他のアプリケーションでは使用しないでください。
このオプションは、32 ビット リリースの SQL Server ではサポートされていません。
Fast Track Data Warehouse (FTDW) でもこのオプションは使用されており、SQL Server Fast Track Data Warehouse の実装 にもこのスタートアップオプションについて記載がされています。
SQL Server が 2 MB のエクステントを使用できるようにします。
ダウンロードできる SQL Server Fast Track Data Warehouse の実装 には、
-E をスタートアップのオプションに追加する必要があります。
これによって、データベース テーブルの増大に応じて割り当てられる、各ファイル内の連続するエクステントの数が増えます。
そして、シーケンシャルなディスク アクセスが向上します。
Microsoft サポート技術情報 (KB) の記事 329526 では、-E オプションについて詳しく説明しています。
というように書かれており、リンク先には、
SQL Server 2000 Enterprise Edition (64-bit) リリースでは、-E という新しいサーバー起動オプションをサポートしています。
-E 起動オプションを使用すると、ファイル間でエクステントを振り分けるラウンド ロビン サイクルが繰り返される間に、ファイル グループ内でファイルごとに割り当てられるエクステントの数を増やすことができます。
この拡張によりデータの連続性が向上し、DW (データ ウェアハウス) クエリに対して実行される連続したスキャン中に処理される I/O 数が先読みメカニズムによって減少するため、DW のパフォーマンスが向上する場合があります。
-E オプションの使用は、インデックスまたはデータのスキャンを実行するユーザーの数が限られているデータ ウェアハウス アプリケーションで、役立つ場合があります。
このオプションは、データ ウェアハウス以外の環境ではパフォーマンス上の利点がなく、またパフォーマンスに影響を与える可能性もあるため、他の環境では使用しないようにする必要があります。-E オプションは SQL Server 2000 (32-bit) リリースで機能しますが、サポートされていません。
というように記載があります。
今回の投稿では、この内容がどのようなことを表しているのかデータを取りながら見ていきたいと思います。
■-E の効果を見てみる
このスタートアップオプションですが、ファイルグループを複数のデータファイルで構成した時に効果が見えてきます。
# 一つのファイルで構成している場合はデータが取れないのですよね。
効果を見る前にまずは、エクステントとページの構成についておさらいをしておきたいと思います。
SQL Server のデータはページという 8KB のデータ領域に格納がされます。
データを格納する際に 8KB という単位で領域が確保されていくかというとそういうことはなく、データ格納領域の確保はエクステントというページを 8 個まとめた単位で取得されます。そのため 64KB が 1 エクステントとなるということですね。
データページを割り当てるという流れになります。
# エクステント内に空きがある場合は新規のエクステントは確保せずにページを割り当てます。
それでは、-E オプションの効果を見ていきたいと思います。
今回は以下のクエリを使用して、データを挿入します。
DROP TABLE Table_1 INSERT INTO Table_1 VALUES(NEWID()) |
char 型 4100 バイトの列を持つテーブルを作成しています。
SQL Server の 1 ページは 8KB となり、固定長データの場合、複数のページを使用したデータの挿入はできませんので、1 レコードにつき 1 ページ消費する形となります。
1 レコードで消費するページ内のデータは以下のようになります。
# ページヘッダやオフセット情報等の管理情報でページ内のバイトは消費するため 1 ページ内のデータは 8060 バイトが上限となります。
今回はこの形式のレコードを 800 件挿入していますので、800 ページ使用されていることになります。
これをエクステントにすると 800 ページ (全体のページ数)/ 8 ページ (エクステントの単位) = 100 となりますのでエクステントとしては 100 エクステント使用されます。
このデータ (テーブル) を格納するためのファイルグループの構成は以下のようになっています。
5 つのデータファイルでテーブルを格納しているファイルグループを構成しています。
それでは、このファイル構成の場合 100 エクステントのデータがどのように格納されていくかを見ていきたいと思います。
まずは ?E オプションを指定しない状態で確認をしてみます。
エクステント情報の確認には [DBCC EXTENTINFO] という Undocument な DBCC コマンドを使用します。
DBCC コマンドで取得しただけですと加工がしにくいですので、今回はテーブル変数に結果を格納して、Table_1 というデータを格納しているテーブルのエクステントの情報のみを取得しています。
DECLARE @ExtentInfo TABLE( INSERT INTO @ExtentInfo SELECT file_id, COUNT(*) AS [Used Extent] |
file_id = 2 はログファイルの ID となりますので 1,3~6 の 5 ファイルに対して 20 エクステントずつ使用されているのが確認できますね。
-E オプションを使用していない場合、新規のエクステント割り当ては細かに各ファイルに対して行われるため、均等に使用されていることが確認できますね。
それでは ?E オプションを使用した場合、どのようになるか確認してみたいと思います。
スタートアップオプションに ?E を追加して SQL Server を再起動します。
-E オプションを指定した場合、エクステントの使用状況は以下のようになります。
100 エクステント使用されているという状況は変わりませんが、file_id は 4/5 しか使われていないことが確認できますね。
これが ?E を設定した効果になります。
今回は Denali CTP3 を使用しているのですが、Denali CTP3 では-E を設定していない場合は 1 エクステントずつ領域が確保されます。
たとえば、今回のテーブルで 10 レコード 挿入したとします。
この場合は 2 エクステント必要になるのですが、エクステントは各ファイルに対して下のように取得されます。
-E を使用している場合、エクステントを確保するときは一つのデータファイルに最大で 64 エクステントが連続して取得されます。
# 一つのファイルに対して 64 エクステント取得したら次のデータファイルを使用するという形になります。
100 エクステント使用する場合、-E が無い場合は以下のように格納されます。
このように連続したエクステントを使用するようになることが、
-E 起動オプションを使用すると、ファイル間でエクステントを振り分けるラウンド ロビン サイクルが繰り返される間に、ファイル グループ内でファイルごとに割り当てられるエクステントの数を増やすことができます。
の効果になります。
DWH の場合は連続したスキャンの方が効率的なため、このように連続したエクステントを確保したほうが効率が良いので FTDW のような DWH に最適化された環境ではオプションが設定されているようです。
FTDW で使用されている ?T1117 というトレースフラグも、とても興味深い動作をすることができるようになるトレースフラグです。
こちらに関しては 同時にファイルを拡張するためのトレースフラグ で過去に投稿をしていますので、興味のある方がいらっしゃいましたらご参考にしていただけると幸いです。