今回はデータファイルの I/O 特性を確認してみたいと思います。
テストで使用するデータは 5,000 万件 / 1.2GB のデータが入っている GUID のみで構成されたテーブルを使用しています。
CREATE TABLE TestTable ( Col1 uniqueidentifier DEFAULT NEWSEQUENTIALID() , INDEX CIX_TestTable CLUSTERED (Col1) )
このテーブルを 1 / 4 ディスクで構成された記憶域スペース、P10 に配置し性能を測定してみます。
■シングルスレッドのシーケンシャル読み込み
まずは、シングルスレッドで読み込みを行わせるため、MAXDOP を 1 にして検索を行ってみます。
SET NOCOUNT ON
DBCC DROPCLEANBUFFERS
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR)
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
SELECT COUNT(*) FROM TestTable OPTION(MAXDOP 1)
GO
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO
SELECT * FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC
この際の実行プランは以下のようになります。
この場合、大量の先行読み取りが実行されますがこの際の I/O は「512KB」単位で行われているため、ブロックサイズとしてはかなり大きめのサイズでディスクにアクセスが行われています。
この時の処理時間が以下になります。
| ディスク | 処理時間 | CPU 時間 (ミリ秒) |
| 記憶域 : 1 | 5:11 | 10,297 |
| 記憶域 : 4 | 1:18 | 9,718 |
| P10 | 0:17 | 8,609 |
通常のデータディスクでは「5:11」かかっていますが、データディスクを 4 本使っているものに関しては、大幅に処理時間が短縮されています。
ストライプサイズでうまくディスクアクセスが分散されているようですね。
プレミアムストレージに関しては 1 本のディスクで構成していますが、処理時間は 1 本構成でも圧倒的に短いですね。
言い方が雑ではありますがプレミアムストレージを使用すると、ディスクの構成をあまり考えないでもディスクアクセスを効率化することができています。
この際の待ち時間と読み取りページ数が以下になります。
| ディスク | PAGEIOLATCH_SH | 読み取りページ数 | |
| waiting_tasks_count | wait_time_ms | ||
| 記憶域 : 1 | 432 | 345,219 | 155,329 |
| 記憶域 : 4 | 407 | 72,900 | 155,329 |
| P10 | 361 | 8,947 | 155,329 |
すべてのケースで同一ののページ数が読み取られていますが、「PAGEIOLATCH_SH」(ディスクからメモリへのデータ読み取りが発生した場合の待ち時間) が、ディスク性能に応じて変化していることが確認できます。
データベースのデータファイルの分割は、先行読み取りが発生した場合などに、大きいブロックサイズでの読み込みが発生した場合に効果が出ていることが確認できます。
■マルチスレッドのシーケンシャル読み込み
先ほどは MAXDOP を 1 に設定して読み込みを行っていました。
次は MAXDOP を 0 に設定して読み込みを行ってみたいと思います。
DS13 は 8 コアの 1 NUMA 構成ですので MAXDOP を 0 にした場合、最大で 8 スレッドで処理を行うことができます。
処理時間に関しては以下になっています。
| ディスク | 処理時間 | CPU 時間 (ミリ秒) |
| 記憶域 : 1 | 5:12 | 12,702 |
| 記憶域 : 4 | 1:18 | 11,623 |
| P10 | 0:12 | 9,234 |
P10 では、マルチスレッドで処理をすることで、処理時間が短縮されましたがその他のディスクについては処理時間は 1 スレッドと同様でした。
1 スレッドの状態で、1 ディスクの場合は、「9IOPS / 4MB/sec」4 ディスクの場合は、「35IOPS / 16MB/sec」 の I/O が発生しており、この傾向は 8 スレッドの状態と変わっていませんでした。
このことからディスク性能の上限に達してしまい、スレッド数が増加しても処理性能が向上しなかったと考えらられます。
P10 を使用した場合、1 スレッドでは「140IOPS / 70MB/sec」、8 スレッドでは「200 IOPS / 100MB/sec」発生させることができています。
ディスク性能が高い場合には、マルチスレッドでアクセスした際の効果が出てくることが確認できます。
この傾向は SQLIO で 512KB の結果を取得した時と同じになっています。
今回の先行読み取りは、きちんとデータが整列されている状態で発生していますので、シーケンシャルリードの特性を持っています。
この時の SQLIO の結果が以下になります。
| ブロックサイズ | スレッド数 | ディスク | 操作 | IOPS | MB/sec |
| 512K | 1 | 記憶域 : 1 | Seq : Read | 7.8 | 3.9 |
| 記憶域 : 4 | Seq : Read | 25.96 | 12.98 | ||
| P10 (1 ディスク) | Seq : Read | 94.6 | 47.3 | ||
| 4 | 記憶域 : 1 | Seq : Read | 7.85 | 3.92 | |
| 記憶域 : 4 | Seq : Read | 31.29 | 15.64 | ||
| P10 (1 ディスク) | Seq : Read | 194.53 | 97.26 | ||
| 8 | 記憶域 : 1 | Seq : Read | 7.88 | 3.94 | |
| 記憶域 : 4 | Seq : Read | 31.31 | 15.65 | ||
| P10 (1 ディスク) | Seq : Read | 194.69 | 97.34 |
512KB I/O を発生させた場合、1 ディスクについてはスレッド数を変更しても差はなく、4 スレッドについてはも誤差程度の差しかありませんでした。
# 測定された値と、SQL 実行中に確認した値も同程度となっています。
P10 を使用した場合は、倍近くのディスク I/O が発生しており、スレッドを増やしたことによる性能の向上が、実際に SQL を実行して発生させた I/O にも表れてきていると考えられます。
■データファイルの分割の効果
次にデータファイルの分割の効果を見てみたいと思います。
現状のデータベースは 1 データファイルで構成されていますのでこれを 4 データファイルにして、データを分散配置します。
MAXDOP が 1 の場合は、以下になります。
| ディスク | 処理時間 | CPU 時間 (ミリ秒) |
| 記憶域 : 1 | 5:11 | 9,734 |
| 記憶域 : 4 | 1:18 | 10,156 |
| P10 | 0:18 | 8,734 |
| ディスク | PAGE_IO_LATCH | 読み取りページ数 | |
| waiting_tasks_count | wait_time_ms | ||
| 記憶域 : 1 | 406 | 397,841 | 155,327 |
| 記憶域 : 4 | 368 | 71,811 | 155,327 |
| P10 | 55 | 743 | 155,327 |
MAXDOP を 0 にした場合は以下になります。
| ディスク | 処理時間 | CPU 時間 (ミリ秒) |
| 記憶域 : 1 | 5:11 | 12,903 |
| 記憶域 : 4 | 1:18 | 11,889 |
| P10 | 0:12 | 9,750 |
| ディスク | PAGE_IO_LATCH | 読み取りページ数 | |
| waiting_tasks_count | wait_time_ms | ||
| 記憶域 : 1 | 2,814 | 2,447,500 | 155,329 |
| 記憶域 : 4 | 2,476 | 618,654 | 155,328 |
| P10 | 767 | 91,091 | 155,328 |
データファイルを分散させた場合に効果が得られるケースも多々あるのですが、基本的なディスク性能を担保するのが、効果としては一番あらわれている形になっています。
また、以下のようなデータ圧縮により、データページが減る場合には、CPU 負荷 / 変更処理のスループットへの影響が懸念されますが、I/O 数が減りますので、データ取得の際のディスク負荷の負荷を低減させることが可能となります。
ALTER INDEX CIX_TestTable ON TestTable REBUILD WITH (DATA_COMPRESSION=PAGE)