SE の雑記

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

SQL Server のスタートアップオプション -E の動きを見てみる

leave a comment

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 エクステントとなるということですね。
image

データを書くときには、エクステントの領域を確保してから
image

データページを割り当てるという流れになります。
# エクステント内に空きがある場合は新規のエクステントは確保せずにページを割り当てます。
image

 

それでは、-E オプションの効果を見ていきたいと思います。
今回は以下のクエリを使用して、データを挿入します。

DROP TABLE Table_1
CREATE TABLE Table_1(Col1 char(4100))
CHECKPOINT
SET NOCOUNT ON
GO

INSERT INTO Table_1 VALUES(NEWID())
GO 800

char 型 4100 バイトの列を持つテーブルを作成しています。
SQL Server の 1 ページは 8KB となり、固定長データの場合、複数のページを使用したデータの挿入はできませんので、1 レコードにつき 1 ページ消費する形となります。

1 レコードで消費するページ内のデータは以下のようになります。
# ページヘッダやオフセット情報等の管理情報でページ内のバイトは消費するため 1 ページ内のデータは 8060 バイトが上限となります。
image

今回はこの形式のレコードを 800 件挿入していますので、800 ページ使用されていることになります。
これをエクステントにすると 800 ページ (全体のページ数)/ 8 ページ (エクステントの単位) = 100 となりますのでエクステントとしては 100 エクステント使用されます。

このデータ (テーブル) を格納するためのファイルグループの構成は以下のようになっています。
image

5 つのデータファイルでテーブルを格納しているファイルグループを構成しています。

それでは、このファイル構成の場合 100 エクステントのデータがどのように格納されていくかを見ていきたいと思います。
まずは ?E オプションを指定しない状態で確認をしてみます。

エクステント情報の確認には [DBCC EXTENTINFO] という Undocument な DBCC コマンドを使用します。
DBCC コマンドで取得しただけですと加工がしにくいですので、今回はテーブル変数に結果を格納して、Table_1 というデータを格納しているテーブルのエクステントの情報のみを取得しています。

DECLARE @ExtentInfo TABLE(
file_id int,
page_id bigint,
pg_alloc smallint,
ext_size smallint,
object_id int,
index_id smallint,
parition_number int,
partition_id bigint,
iam_chain_type nvarchar(255),
pfs_bytes varbinary(max)
)

INSERT INTO @ExtentInfo
EXEC (‘DBCC EXTENTINFO’)

SELECT file_id, COUNT(*) AS [Used Extent]
FROM @ExtentInfo where object_id = OBJECT_ID(N’dbo.Table_1′)
GROUP BY file_id
GO

実行の結果がこちらになります。
image

file_id = 2 はログファイルの ID となりますので 1,3~6 の 5 ファイルに対して 20 エクステントずつ使用されているのが確認できますね。
-E オプションを使用していない場合、新規のエクステント割り当ては細かに各ファイルに対して行われるため、均等に使用されていることが確認できますね。

それでは ?E オプションを使用した場合、どのようになるか確認してみたいと思います。
スタートアップオプションに ?E を追加して SQL Server を再起動します。
image

-E オプションを指定した場合、エクステントの使用状況は以下のようになります。
image

100 エクステント使用されているという状況は変わりませんが、file_id は 4/5 しか使われていないことが確認できますね。
これが ?E を設定した効果になります。

今回は Denali CTP3 を使用しているのですが、Denali CTP3 では-E を設定していない場合は 1 エクステントずつ領域が確保されます。

たとえば、今回のテーブルで 10 レコード 挿入したとします。
この場合は 2 エクステント必要になるのですが、エクステントは各ファイルに対して下のように取得されます。
image

-E が使用されている場合は以下のように取得がされます。
image

-E を使用している場合、エクステントを確保するときは一つのデータファイルに最大で 64 エクステントが連続して取得されます。
# 一つのファイルに対して 64 エクステント取得したら次のデータファイルを使用するという形になります。

100 エクステント使用する場合、-E が無い場合は以下のように格納されます。
image

-E を使用した場合は以下のようになります。
image

このように連続したエクステントを使用するようになることが、

-E 起動オプションを使用すると、ファイル間でエクステントを振り分けるラウンド ロビン サイクルが繰り返される間に、ファイル グループ内でファイルごとに割り当てられるエクステントの数を増やすことができます。

の効果になります。

DWH の場合は連続したスキャンの方が効率的なため、このように連続したエクステントを確保したほうが効率が良いので FTDW のような DWH に最適化された環境ではオプションが設定されているようです。

FTDW で使用されている ?T1117 というトレースフラグも、とても興味深い動作をすることができるようになるトレースフラグです。
こちらに関しては 同時にファイルを拡張するためのトレースフラグ で過去に投稿をしていますので、興味のある方がいらっしゃいましたらご参考にしていただけると幸いです。

Share

Written by Masayuki.Ozawa

9月 8th, 2011 at 11:11 pm

Posted in SQL Server

Tagged with

Leave a Reply