SE の雑記

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

データ読み取りと CPU の利用について

leave a comment

最近のハードウェアはディスク性能が向上して、ディスク 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


image

これで準備が終わりましたので、以下のようなクエリを実行して 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 が発生するプランとなります。

image

それでは、このクエリの 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

実行結果は以下のようになります。

image

すでにメモリ上にデータが乗っているため、ディスク 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

image

image

クラスター化インデックスでデータを取得した場合と比較して CPU 時間が減っていることが確認できます。

クラスター化インデックスによる検索は行全体を取得しますが、非クラスター化インデックスのみで検索が完了する場合には、インデックスの利用のみで処理が済みますので、読み取るデータ数も減ります。

論理読み取りを減らすことで CPU 時間を減らすことができますので、ディスクが高速になってもこの辺のチューニング手法はきちんと把握する必要がありますね。

Written by masayuki.ozawa

6月 5th, 2014 at 8:41 am

Posted in SQL Server

Tagged with

Leave a Reply

*