SE の雑記

SQL Server には SQLIOSIM というストレステストを実施するためのツールが提供されています。
SQLIOSim ユーティリティを使用して、ディスク サブシステム上の SQL Server アクティビティをシミュレートする方法

このツールは SQL Server をインストールしなくても SQL Server と同等の I/O パターンをシミュレートしてストレージに対して負荷をかけることができるツールとなっています。SQL Server 2005 までは別途ダウンロードする必要があったのですが、SQL Server 2008 以降はインスタンスのディレクトリに標準で含まれるようなりました。
ただし標準で含まれている SQLIOSIM のコンポーネントには、サンプルの構成ファイル群 (sqliosim.cfg.zip) が含まれていませんので、これらのファイルが使用したい場合には KB からダウンロードしたほうが良いかと。

SQLIOSIM は 最近では、蒼の王座さんのブログ でも紹介されていました。
SQLIOSimを使って、Fusion-ioDriveとFusion-ioDrive2のSQL Server IOシミュレート評価してみたよ

似たようなツールに SQLIO というものもあるようです。
このツールに関しては SQL Server Best Practices Article で紹介がされていますね。

書籍で紹介されているものとしては、

絵で見てわかるSQL Serverの内部構造 (DB Magazine SELECTION)
Professional SQL Server 2008 Internals and Troubleshooting
でしょうか。

■SQLIOSIM でシミュレートできる内容

---

それでは、SQLIOSIM でテストを実行できる内容について見ていきたいと思います。

SQLIOSIM では以下の I/O パターンをテストすることができます。

• RandomUser
  OLTP 向けの I/O パターン (読み取り/書き込みのトランザクションミックス) のシミュレート
• AuditUser
  DBCC アクティビティのシミュレート
  # どの DBCC アクティビティなのかが分からないのですが CHECKDB 辺りでしょうか??
• ReadAheadUser
  先行読み取りのシミュレート
• BulkUpdateUser
  一括操作 (BULK INSERT) のシミュレート

SQL Server ではデータファイルを開くとき FILE_FLAG_NO_BUFFERING / FILE_FLAG_WRITE_THROUGH が使用されますが、この設定を行うこともできます。
# デフォルトでは両方とも有効 (True) になっており、SQL Server の通常のデータファイルを開く操作と同等です。

また、SQL Serverがデータを読み取り / 書き込みする際には、ReadFileScatter / WriteFileGather API が使用されていますが SQLIOSIM でもこれらの API が使用されています。
バックアップ系の操作で、ReadFile / WriteFile、ログの先行書き込みでWriteFile API が使用されているのですがこれらの API を使用した操作もシミュレートされています。
# SQL Server の I/O は Windows の標準 API が使用 されています。

スキャッタ読み取り、ギャザー書き込みについては BOL で情報が公開されていますね。
ページの読み取り
ページの書き込み

■テストできる内容

---

SQLIOSIM では SQL Server の I/O を複数のデータファイルを使用してシミュレートできますのでいろいろなテストができると思います。この辺はストレージを触られている方の方が詳しいと思いますが。
# 私、データベースエンジニアを目指しているのにハードウェアほとんど触れないんですよね…。

テストは

• 各テストのスレッド数
• データベースのファイルの配置場所
• 1 サイクル (一連のテスト項目の実施) のテスト時間
• テストの回数

を組み合わせて実行することができます。

1 サイクル×テストの回数がテストに必要となる時間になります。
60 秒のサイクル時間でテストサイクルを 2 にした場合はこのような形になります。
それぞれのサイクルが 1 分間程度実行されているのが確認できますね。
初期処理に関しては実行時の最初のみ行われているようです。

各テストのスレッド数も設定ができ、私の環境では初期設定値は (sqlctr.ini の設定内容) 以下のようになっていました。
# 初回起動時に搭載されている CPU の数を基準に設定しているのでしょうか。
並列度を上げたい場合は設定ファイルの各セクションの [UserCount] で調整することができます。

RandomUser	8
AuditUser	2
ReadAheadUser	2
BulkUpdateUser	8

 

SQLIOSIM を使用することで

• データファイル / ログファイルの配置の検討
• 複数のスレッドを使用して特定の回数ストレステストを実行してディスクのパフォーマンスを測定
• 日中の平均的なアクセス数を設定してテストのサイクルを 0 にしてストレステストを実行し続けエラーが発生しないことを確認
• 業務ピークに想定されるアクセス数を設定し、サイクルをピークとなる期間に設定しエラーが発生しないことを確認
• 大量のスレッドが発生した場合ストレージのパスに異常が発生しないかを確認
• ディスクの高負荷エージングを実施

というようなテストが実施できると思います。

 

■テストの実行と結果の利用

---

今回は SQLIOSIM を利用して [データファイル / ログファイルの配置の検討] をしてみたいと思います。

SQL Server ではディ
スク I/O の効率を良くするため、データファイルとログファイルを異なるディスクに配置することが推奨されます。
# SQL Server に限ったお話ではないと思いますが。

まずは、データファイルとログファイルを同一のドライブに配置してストレステストを実行してみます。

ストレステストが終了すると結果が表示されます。

今回は各テストのサイクルを実行する前の初期処理をしている箇所で時間がかかったため I/O の警告がいくつか表示されていますね。

蒼の王座さんのサイトで解説がされていますがログで確認するポイントは

Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 161, Number of times IO throttled = 36077, IO request blocks = 1 Reads = 14692, Scatter Reads = 59200, Writes = 1494, Gather Writes = 59544, Total IO Time (ms) = 1085796968

になってくると思います。
この辺の情報 も蒼の王座さんに参考 URL として記載されていますね。
# DRIVER LEVEL の行の情報も見た方がよい場合もあるのかなと思いますが。

今回の結果を表にしてみますと、

	データファイル	ログファイル
Target IO Duration (ms)	100	100
Running Average IO Duration (ms)	161	0
Number of times IO throttled	36077	4
IO request blocks	1	8
Reads	14692	0
Scatter Reads	59200	0
Writes	1494	10253
Gather Writes	59544	0
Total IO Time (ms)	1085796968	118429

となります。

ログファイルは基本的に Write のI/O しかかかっていないようですね。
# 復旧時には Read のI/O がかかりますので実運用で、必ず Write しか発生しないということではないので注意が必要ですが。

それでは、データファイルとログファイルを異なるディスクに配置して再度テストをしてみたいと思います。
今回は仮想環境上のゲスト OS ではなく物理環境で実施しており、ディスクのスピンドルも物理的に分かれています。

今回の結果を表にしますと、

	データファイル	ログファイル
Target IO Duration (ms)	100	100
Running Average IO Duration (ms)	67	0
Number of times IO throttled	36449	0
IO request blocks	19	8
Reads	15419	0
Scatter Reads	60049	0
Writes	1782	11127
Gather Writes	59870	0
Total IO Time (ms)	1070698884	1459

 

となりました。

これを同一ドライブに配置したものと比較してみたいと思います。

	データファイル			ログファイル
Target IO Duration (ms)	100	100	±0	100	100	±0
Running Average IO Duration (ms)	161	67	↓94	0	0	±0
Number of times IO throttled	36077	36449	↑372	4	0	↑4
IO request blocks	1	19	↑18	8	80	↑72
Reads	14692	15419	↑18	0	0	±0
Scatter Reads	59200	60049	↑849	0	0	±0
Writes	1494	1782	↑288	10253	11127	↑874
Gather Writes	59544	59870	↑326	0	0	±0
Total IO Time (ms)	1085796968	1070698884	↓15098084	118429	1459	↓116970

データとログを異なるドライブに配置したことで I/O 回数が向上し、処理に必要だった I/O Time が減少したことが確認できます。
# 同一の I/O 回数でも処理に必要だった I/O Time が少ない方が効率的に処理ができているかと思いますので回数の差が近似値だったとしても Toltal IO Time で差が出ていればよいのかなと。
誤差かもしれませんが、データファイルの Duration も減少していますね。

こちらがパフォーマンスモニターで取得したディスクキューのグラフになります。手元にあった適当なディスクを使っているのでキューの発生は同一ドライブでも分けてもトレンドは変わらないですね。

D ドライブを OS の機能でストライピングした際の結果がこちらになります。
# ストライピングした D ドライブと通常の F ドライブの構成です。

	データファイル	ログファイル
Target IO Duration (ms)	100	100
Running Average IO Duration (ms)	391	0
Number of times IO throttled	25100	0
IO request blocks	26	8
Reads	32084	0
Scatter Reads	73597	0
Writes	1940	32179
Gather Writes	70596	0
Total IO Time (ms)	519291557	2780

これをストライピングをしていなかった D/F ドライブの結果と比較してみます。

	データファイル			ログファイル
Target IO Duration (ms)	100	100	±0	100	100	±0
Running Average IO Duration (ms)	67	391	↑324	0	0	±0
Number of times IO throttled	36449	25100	↓25100	0	0	±0
IO request blocks	19	26	↑7	80	8	↑72
Reads	15419	32084	↑11054	0	0	±0
Scatter Reads	60049	73597	↑13548	0	0	±0
Writes	1782	1940	↑158	11127	32179	↑21052
Gather Writes	59870	70596	↑10726	0	0	±0
Total IO Time (ms)	1070698884	519291557	↓551407327	1459	2780	↑1321

 

データファイルの I/O 効率がかなり向上していますね。
この手の結果は数回繰り返して取得して、値にぶれがないことを確認する必要があると思いますが今回は一度しか取得していないので参考程度になりますが。

キューの比較をするとストライピングをしたものの方が平均的に低くなっていますね。

SQLIOSIM の結果だけでなくパフォーマンスモニターでストレージ関係のカウンタを取得しているといろいろな見方ができるかと思います。

軽くですが SQLIOSIM についてまとめてみました。