最近のハードウェアはディスク性能が向上して、ディスク I/O がネックになることも少なくなってきたかと思います。
ディスクが高速になることで大量のデータを短時間で読み取れるようになりましたが、その分負荷が CPU に表れてくることになったかと。
簡単にではありますがこの辺のデータをとってみたいと思います。
今回は、50 万件データが格納されているテーブルを使用しています。
先ずはディスクの性能を無視したいので全てのデータをキャッシュにあげます。
今回は
- クラスター化インデックス
- 非クラスター化インデックス
を設定しているテーブルですので、各インデックスでフルスキャンをさせて、メモリ上にデータを全てキャッシュさせています。
その後、プランキャッシュをクリアしています。
SET NOCOUNT ON SELECT FORMAT(COUNT(*), '#,###') FROM Table_1 WITH(INDEX=1) SELECT FORMAT(COUNT(*), '#,###') FROM Table_1 WITH(INDEX=2) DBCC FREEPROCCACHE GO
これで準備が終わりましたので、以下のようなクエリを実行して CPU の使用時間を調べてみます。
DECLARE @i int SELECT TOP 100 @i = Col1 FROM Table_1 WITH(INDEX=1) GO DECLARE @i int SELECT TOP 1000 @i = Col1 FROM Table_1 WITH(INDEX=1) GO DECLARE @i int SELECT TOP 10000 @i = Col1 FROM Table_1 WITH(INDEX=1) GO
使用するインデックスを固定して、取得行数を TOP で調整しています。
今回のインデックスの設定状況ですと、Clustered Index Scan が発生するプランとなります。
それでは、このクエリの CPU の使用状況を見てみます。
今回は sys.dm_exec_query_stats の total_worker_time を基準値としてみたいと思います。
# DMV から取得したほうが実運用で便利ですので。
情報の取得に使用したクエリがこちらです。
SELECT TOP 10 [total_elapsed_time], [total_worker_time], [total_physical_reads], [total_logical_reads], [execution_count], [creation_time], [last_execution_time ], SUBSTRING(text, ([statement_start_offset] / 2) + 1, ((CASE [statement_end_offset] WHEN -1 THEN DATALENGTH(text) ELSE [statement_end_offset] END - [statement_start_offset]) / 2) + 1) AS [sqltext], query_plan FROM [sys].[dm_exec_query_stats] CROSS APPLY [sys].[dm_exec_sql_text]([sql_handle]) CROSS APPLY [sys].[dm_exec_query_plan]([plan_handle]) WHERE SUBSTRING(text, ([statement_start_offset] / 2) + 1, ((CASE [statement_end_offset] WHEN -1 THEN DATALENGTH(text) ELSE [statement_end_offset] END - [statement_start_offset]) / 2) + 1) LIKE '%Table_1%' ORDER BY 2 DESC
実行結果は以下のようになります。
すでにメモリ上にデータが乗っているため、ディスク I/O はどのクエリでも発生していませんが (physical_reads が 0 で、全てが logical_reads) 、CPU 時間には差があります。
キャッシュをクリアせず複数回実行した場合もこのトレンドは変わらないかと思います。
メモリ上に全データがキャッシュされていても、取得する行数によって CPU の使用時間が変わってきます。
取得する行が多ければ CPU 時間が増えますので、ディスクが高速になっても [必要なデータのみを取得する] という考えは変わってきません。
それでは、検索を非クラスター化インデックス (Col1 に設定してあります) で実施してみたいと思います。
DECLARE @i int SELECT TOP 100 @i = Col1 FROM Table_1 WITH(INDEX=2) GO DECLARE @i int SELECT TOP 1000 @i = Col1 FROM Table_1 WITH(INDEX=2) GO DECLARE @i int SELECT TOP 10000 @i = Col1 FROM Table_1 WITH(INDEX=2) GO
クラスター化インデックスでデータを取得した場合と比較して CPU 時間が減っていることが確認できます。
クラスター化インデックスによる検索は行全体を取得しますが、非クラスター化インデックスのみで検索が完了する場合には、インデックスの利用のみで処理が済みますので、読み取るデータ数も減ります。
論理読み取りを減らすことで CPU 時間を減らすことができますので、ディスクが高速になってもこの辺のチューニング手法はきちんと把握する必要がありますね。