SE の雑記

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

パラメータ スニッフィングとトレースフラグ4136

leave a comment

SQL Server でストアドプロシージャやパラメータ化クエリを実行する場合、パラメータ スニッフィング (パラメータ傍受) によって、初回に実行された際のパラメータを元に実行プランが作成されます。
MVP Erland氏がSQL Serverのパフォーマンス概念について検討

そのため、初回の実行時に特殊な結果を返すようなパラメータでクエリが実行されると、それが他のパラメータでも効率的な最適化されたプランとならない可能性があるため、RECOMPILE ヒントや OPTMIZE FOR ヒントを使用して非効率なプランで実行されないような対応をすることがあるかと思います。

SQL Server 2008 以降では、OPTMIZE FOR ヒントに UNKNOWN というオプションが追加されています。

今回は SQL Server 2005 SP3 CU9 / SQL Server 2008 SP1 CU7 / SQL Server 2008 R2 CU2 以降で使用できるようになった、トレースフラグ 4136 と合わせてこの動作を見ていきたいと思います。

「スニッフィング」パラメーターの処理を無効にするために使用できるトレース フラグ 4136 SQL Server 2008 R2 累積的な更新プログラム 2、SQL Server 2008 SP1 累積的な更新プログラム 7 および SQL Server 2005 SP3 累積的な更新プログラム 9 を導入します。

■パラメータ スニッフィング


10 万件のデータが入っているテーブルがあります。

データの内訳は以下のようになっています。

Col2 = N’AAAAAA’ 99999件
Col2 = N’ZZZZZZ’ 1 件

 

このテーブルに対して、以下のパラメーター化クエリを実行してみます。

DECLARE @param nchar(100) = N’AAAAAA’
EXEC sp_executesql
N’DECLARE @tmp uniqueidentifier;SELECT @tmp = Col1 FROM Table_1 WHERE Col2 = @param1′
, @params = N’@param1 nchar(100)’
, @param1 = @param

パラメーターは ‘AAAAAA’ で実行しています。

この時の実行プランは以下のようになります。
image

パラメータを ‘AAAAAA’ で実行していますので予測行数は [999999] となっています。

この状態でパラメータを ‘ZZZZZZ’ に変えて実行してみます。

DECLARE @param nchar(100) = N’ZZZZZZ’
EXEC sp_executesql
N’DECLARE @tmp uniqueidentifier;SELECT @tmp = Col1 FROM Table_1 WHERE Col2 = @param1′
, @params = N’@param1 nchar(100)’
, @param1 = @param

既に同一のクエリハッシュのクエリで実行をしていますので、前回実行時のプランが使用されます。
image

そのため、予測行数も初回実行時の ‘AAAAAA’ で実行された際の 999999 となっています。

一度プロシージャキャッシュをクリアして ‘ZZZZZZ’ で実行した場合、予測行数は 1 となっています。
image

パラメータクエリを実行した場合、最初に実行したパラメータでクエリがコンパイルされますので、リコンパイルされるまでは、パラメータが変わってもそのプランが使用されることになります。

今回はどちらのパラメータでもプランに差はないので大きな問題にはならないと思いますが、パラメータの差によって Seek が Scan になってしまう場合などは大きく影響が出てきます。

 

■OPTIMIZE FOR を使用したパラメータの指定


SQL Server では OPTIMIZE FOR を使用することで、実際に渡されたパラメータではなく、クエリヒントとして実行時のパラメータを明示的に指定することができます。

先ほど、’AAAAAA’ で実行した場合、予測行数が 999999 となっていたかと思います。
これは渡されたパラメータである ‘AAAAAA’ (999999 行) を元にクエリの実行プランが組み立てられているためです。

‘AAAAAA’ で実行しても、’ZZZZZZ’ で実行された場合のプランを使用したいという場合があります。

そのような場合には OPTMIZE FOR を使用します。

‘AAAAAA’ で実行していた場合のクエリを以下のように変更します。
# DBCC FREEPROCCACHE でプロシージャキャッシュはクリアしてあります。

DECLARE @param nchar(100) = N’AAAAAA’
EXEC sp_executesql
N’DECLARE @tmp uniqueidentifier;SELECT @tmp = Col1 FROM Table_1 WHERE Col2 = @param1
OPTION (OPTIMIZE FOR (@param1 = N”ZZZZZZ”))’
, @params = N’@param1 nchar(100)’
, @param1 = @param

パラメータは ‘AAAAAA’ で実行していますが、OPTIMIZE FOR で ‘ZZZZZZ’ としてヒントを指定していますので、パラメータとしては ‘ZZZZZZ’ が渡された状態でクエリがコンパイルされます。
image

OPTIMIZE FOR ヒントを使うことで同じのパラメータが渡された状態でクエリを実行することが可能となります。

■OPTIMIZE FOR UNKNOWN


SQL Server 2008 以降では OPTIMIZE FOR に UNKN
OWN というオプションが追加されています。
クエリ ヒント (Transact-SQL)

クエリ オプティマイザーでクエリをコンパイルおよび最適化するときに、強制パラメーター化によって作成されたパラメーターも含め、すべてのローカル変数に対して初期値の代わりに統計データを使用することを指定します。

このオプションを使うとパラメータではなく統計データをもとにクエリのコンパイルが行われるようになります。

UNKNOWN の指定には二種類があり、特定のパラメータだけを UNKNOWN として指定、

DECLARE @param nchar(100) = N’AAAAAA’
EXEC sp_executesql
N’DECLARE @tmp uniqueidentifier;SELECT @tmp = Col1 FROM Table_1 WHERE Col2 = @param1
OPTION (OPTIMIZE FOR (@param1 UNKNOWN))’
, @params = N’@param1 nchar(100)’
, @param1 = @param

クエリのパラメータ全体を UNKNOWN として指定することができます。

–DBCC FREEPROCCACHE
DECLARE @param nchar(100) = N’AAAAAA’
–DECLARE @param nchar(100) = N’ZZZZZZ’
EXEC sp_executesql
N’DECLARE @tmp uniqueidentifier;SELECT @tmp = Col1 FROM Table_1 WHERE Col2 = @param1
OPTION (OPTIMIZE FOR UNKNOWN)’
, @params = N’@param1 nchar(100)’
, @param1 = @param

 

UNKNOWN を使用すると統計情報をもとにクエリがコンパイルされるため予測行数が ‘AAAAAA’ / ‘ZZZZZZ’ とも違っていることが確認できます。

image

 

■トレースフラグ 4136 を指定して実行


トレースフラグ 4136 を指定するとストアドプロシージャやパラメータ化クエリのデフォルトの動作を UNKNOWN にすることができるようです。

先ほどのクエリを以下のように変更してみます。

DBCC TRACEON(4136, -1)
DECLARE @param nchar(100) = N’AAAAAA’
EXEC sp_executesql
N’DECLARE @tmp uniqueidentifier;SELECT @tmp = Col1 FROM Table_1 WHERE Col2 = @param1′
, @params = N’@param1 nchar(100)’
, @param1 = @param

左がトレースフラグを指定しないで実行したクエリ、右がトレースフラグを指定して実行したクエリとなります。
imageimage

トレースフラグを実行した場合は予測行数が UNKNOWN を指定した場合と同じになっています。

具体的にプランが変わるようなデータを持っていなかったので、概略だけになりますが、クエリ実行は奥が深いですね~。

Share

Written by Masayuki.Ozawa

1月 8th, 2012 at 1:33 pm

Posted in SQL Server

Tagged with ,

Leave a Reply