SE の雑記

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

データのキャッシュと CPU について

leave a comment

SQL Server ではデータはメモリ上にキャッシュされます。

キャッシュされたデータの読み込みと CPU の関連性について少しまとめてみたいと思います。

SQL Server のデータをすべてメモリ上にキャッシュされれば、ハードウェアリソースの利用には問題ないかというとそういうことはありません。

以下のようなテーブルがあります。


CREATE TABLE [dbo].[Table_1](
	[c1] [uniqueidentifier] NOT NULL,
	[c2] [int] NULL,
	[c3] [int] NULL,
	[c4] [int] NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
([c1] ASC)
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_Table_1] ON [dbo].[Table_1]
(	[c2] ASC)
GO

 

このテーブルに以下のクエリでデータを挿入してみます。

SET NOCOUNT ON
GO
DECLARE @i int , @cnt int = 1
SELECT @i = COUNT(*) + 1 FROM Table_1

WHILE (@cnt <= 100000)
BEGIN
	INSERT INTO Table_1 VALUES(NEWID(), @cnt, @cnt, @cnt)
	SET @i+=1
	SET @cnt+=1
END

SELECT COUNT(*) FROM Table_1

 

このテーブルに対して、以下の 2 種類のクエリを実行してみます。

SET NOCOUNT ON
SET STATISTICS TIME ON
GO
SELECT * FROM Table_1 WITH (INDEX=1)
SELECT * FROM Table_1 WITH (INDEX=2)
GO

DECLARE @start DATETIME = GETDATE()
DECLARE @i int
SELECT @i = COUNT(*) FROM Table_1 WITH(INDEX=0) WHERE c2 = 5000
SELECT DATEDIFF (ms, @start, GETDATE())


DECLARE @start DATETIME = GETDATE()
DECLARE @i int
SELECT @i = COUNT(*) FROM Table_1 WHERE c2 = 5000
SELECT DATEDIFF (ms, @start, GETDATE())

 

一つはインデックスを使用しないクエリ、一つはインデックスを使用するクエリとなっています。

各クエリを実行する前にはインデックスのデータをキャッシュするクエリを実行しています。

全データがキャッシュされている状態でインデックスの使用有無によって CPU 時間の違いを見てみます。

INDEX=0 (テーブルスキャン) を発生させた場合には、6ms ほど実行に時間がかかっています。

image

image

Index Seek で検索をした場合には、0ms となっています。

# コンパイルが発生した場合にはこれ以上かかります。

image

image

すべてのデータをキャッシュしていてもテーブルのスキャンが発生すると経過時間が長くなり、CPU には負荷がかかります。

以下は、[sys.dm_exec_query_stats] から last_worker_time  (マイクロ秒) を取得したものなります。

上が Index_Seek、下が Index_Scan (Table_Scan) が実行された場合の CPU 時間の内容となります。

メモリにデータがキャッシュされていてもスキャンをすると CPU 時間が大きい値となります。

image


これは大容量のメモリや高速なディスクを搭載していても同じ状況になります。

昨今、高速なディスクや大容量なメモリが低価格で実装できるようになってきていますが、クエリの基本である [必要となるデータだけを読む] というのは CPU の負荷にも影響してきますので、基本的なアプローチは大事ですね。

Written by masayuki.ozawa

11月 10th, 2013 at 9:20 pm

Posted in SQL Server

Tagged with

Leave a Reply

*