SQL Server 2008 R2 SP2 適用後のクエリ動作について
皆さん、こんにちは。 今回は、SQL Server 2008 R2 SP2 適用後に、クエリのパフォーマンスに影響を及ぼす可能性があるクエリ例 及び クエリの動作について、紹介したいと思います。
SQL Server 2008 R2 SP1 では、クエリの実行プランを作成時、特定の条件下において、本来 クエリ完了までに正しく整合性を保持するために必要となる “Table Spool (Eager Spool)” オペレータが使用されず、誤ったクエリ結果を返すという問題がございました。
そこで、SQL Server 2008 R2 SP2 では、特定の条件においても、クエリ完了までに正しく整合性を保持できるよう、“Table Spool (Eager Spool)” オペレータ が使われるように修正したものが含まれております。
この修正により、本来 “Table Spool (Eager Spool)” オペレータを追加する必要があったクエリに、正しく “Table Spool (Eager Spool)” オペレータを追加できるようになった一方で、本オペレータが追加されることに伴い、クエリのパフォーマンスに影響を及ぼす可能性があります。
それでは、クエリ例 及び クエリ動作を見ていきましょう。
クエリ例 及び クエリ動作を紹介するにあたり、まずは、以下のスクリプトを実行しサンプルデータベース 及び テーブルを作成します。
---
CREATE DATABASE DB_TEST;
GO
USE DB_TEST
GO
CREATE TABLE [tbMain](
[c1] int NOT NULL,
[c2] int NOT NULL,
[c3] datetime NOT NULL
);
GO
CREATE TABLE [tbSub](
[c1] int NOT NULL,
[c2] int NOT NULL,
[c3] datetime NOT NULL
);
GO
ALTER TABLE [tbMain] ADD
CONSTRAINT pk_tbMain_c1 PRIMARY KEY NONCLUSTERED ( c1 ),
CONSTRAINT uk_tbMain_c2 UNIQUE CLUSTERED (c3, c1, c2 );
GO
ALTER TABLE [tbSub] ADD
CONSTRAINT pk_tbSub_c1c2
PRIMARY KEY CLUSTERED ( c1, c2 );
GO
ALTER TABLE [tbSub] ADD
CONSTRAINT [fk_tbSub_c1] FOREIGN KEY
(
[c1]
)
REFERENCES [tbMain]
(
[c1]
)
ON DELETE CASCADE;
GO
INSERT INTO [tbMain]
VALUES (1,1,'2013-06-12'),(2,2,'2013-06-13'),(3,3,'2013-06-14');
GO
INSERT INTO [tbSub]
VALUES (1,1,'2013-06-12'),(2,2,'2013-06-13'),(3,3,'2013-06-14');
GO
---
スクリプトで作成したテーブルに対して、以下のクエリを SQL Server 2008 R2 SP1 の環境 及び SP2 の環境上で実行します。
---
UPDATE [tbSub]
SET c1 = 1,
c2 = 1,
c3 = '2013-06-15'
WHERE c1 = 1
AND c2 = 1;
GO
---
[実行プラン] SQL Server 2008 R2 SP1 環境
UPDATE [tbSub] set [c1] = @1,[c2] = @2,[c3] = @3 WHERE [c1]=@4 AND [c2]=@5
|--Assert(WHERE:(CASE WHEN [Expr1019] IS NULL THEN (0) ELSE NULL END))
|--Nested Loops(Left Semi Join, OUTER REFERENCES:([DB_TEST].[dbo].[tbSub].[c1]), DEFINE:([Expr1019] = [PROBE VALUE]))
|--Clustered Index Update(OBJECT:([DB_TEST].[dbo].[tbSub].[pk_tbSub_c1c2]), SET:([DB_TEST].[dbo].[tbSub].[c1] = RaiseIfNullUpdate([@1]),[DB_TEST]..
|--Index Seek(OBJECT:([DB_TEST].[dbo].[tbMain].[pk_tbMain_c1]), SEEK:([DB_TEST].[dbo].[tbMain].[c1]=[DB_TEST].[dbo].[tbSub].[c1]) ORDERED FORWARD)
[実行プラン] SQL Server 2008 R2 SP2 環境
UPDATE [tbSub] set [c1] = @1,[c2] = @2,[c3] = @3 WHERE [c1]=@4 AND [c2]=@5
|--Assert(WHERE:(CASE WHEN [Expr1031] IS NULL THEN (0) ELSE NULL END))
|--Nested Loops(Left Semi Join, OUTER REFERENCES:([DB_TEST].[dbo].[tbSub].[c1]), DEFINE:([Expr1031] = [PROBE VALUE]))
|--Clustered Index Update(OBJECT:([DB_TEST].[dbo].[tbSub].[pk_tbSub_c1c2]), SET:([DB_TEST].[dbo].[tbSub].[c1] = ..
| |--Compute Scalar(DEFINE:([Expr1023]=(1), [Expr1024]=(1)))
| |--Compute Scalar(DEFINE:([Expr1003]='2013-06-15 00:00:00.000'))
| |--Table Spool
| |--Clustered Index Seek(OBJECT:([DB_TEST].[dbo].[tbSub].[pk_tbSub_c1c2]), SEEK:([DB_TEST].[dbo].[tbSub].[c1]=(1) AND ..
|--Index Seek(OBJECT:([DB_TEST].[dbo].[tbMain].[pk_tbMain_c1]), SEEK:([DB_TEST].[dbo].[tbMain].[c1]=[DB_TEST].[dbo].[tbSub].[c1]) ORDERED FORWARD)
同じクエリを実行しましたが、SQL Server 2008 R2 SP1 環境と、SQL Server 2008 R2 SP2 環境とで、実行プランが異なることが確認でき、また、SQL Server 2008 R2 SP2 の環境では、”Table Spool (Eager Spool)” というオペレータが追加されていることが確認できると思います。
この “Table Spool (Eager Spool)” は、クエリの実行プランを作成する際、条件に合致した行データを取得後、クエリが完了するまでの動作の中で取得した行データが変更される恐れがあり、かつ、変更される恐れがある行データが他のオブジェクトなどを参照するためのキーとして使用されているなど、取得時の行データの一貫性を保つ必要があるとオプティマイザが判断した場合に、追加されるオペレータの一つとなります。
SQL Server 2008 R2 SP1 環境の場合、本クエリが実行される際、本来 追加されるべき “Table Spool (Eager Spool)” オペレータが追加されないという問題があったため、“Table Spool (Eager Spool)” オペレータが追加されていませんが、SQL Server 2008 R2 SP2 環境では、正しく “Table Spool (Eager Spool)” オペレータを追加できるように改修されたため、“Table Spool (Eager Spool)” オペレータが追加されています。
なお、今回のUPDATE文を実行した場合、以下のような条件例の場合に合致し、“Table Spool (Eager Spool)” が追加されるようになります。
[条件例]
UPDATE の WHERE句に指定した条件と同じ値を、SET句に指定している。
UPDATE の SET句に指定された列が、インデックスのキー列に含まれている。
UPDATE の SET句に指定された列は、外部キーとして別テーブルを参照している。
一般的に “Table Spool (Eager Spool)” オペレータ が存在する場合と存在しない場合とを比較した場合、“Table Spool (Eager Spool)” オペレータは実行コストが高く、存在しない場合と比較し、CPU タイムを多く必要する可能性があるため、クエリのパフォーマンスに影響を及ぼす可能性がございます。
今回のクエリ例の場合、UPDATE の WHERE句に指定した条件と同じ値を、SET句に指定しないようクエリを修正することにより、“Table Spool (Eager Spool)” オペレータ を必要としない、クエリの実行プランが作成されることになります。
---
UPDATE [tbSub]
SET c3 = '2013-06-15'
WHERE c1 = 1
AND c2 = 1;
GO
---
[実行プラン] SQL Server 2008 R2 SP2 環境
UPDATE [tbSub] set [c3] = @1 WHERE [c1]=@2 AND [c2]=@3
|--Clustered Index Update(OBJECT:([DB_TEST].[dbo].[tbSub].[pk_tbSub_c1c2]), SET:([DB_TEST].[dbo].[tbSub].[c3] = RaiseIfNullUpdate([Expr1003])), ..
最後に、クエリ完了までに正しく整合性を保持できず、誤ったクエリ結果を返す現象のことを、ハロウィーン問題と呼んでいます。
今回紹介しましたクエリでは、SQL Server 2008 R2 SP2 適用後、“Table Spool (Eager Spool)” オペレータ が追加されますが、これは ハロウィーン問題への対策 (ハロウィーン保護 (Halloween Protection)) を実施したためとなります。
マイクロソフトでは、ハロウィーン問題の可能性が発見された場合は、データの一貫性を最優先事項として、ハロウィーン保護 (Halloween Protection) を製品に導入しております。
ハロウィーン保護 (Halloween Protection) は、データの一貫性を保つために、必要な対策である点をご理解頂ければ幸いです。
ハロウィーン保護 (Halloween Protection) の詳細につきましては、Halloween Protection (HP) について を参照して頂ければと思います。