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 と合わせてこの動作を見ていきたいと思います。
■パラメータ スニッフィング
10 万件のデータが入っているテーブルがあります。
データの内訳は以下のようになっています。
Col2 = N’AAAAAA’ | 99999件 |
Col2 = N’ZZZZZZ’ | 1 件 |
このテーブルに対して、以下のパラメーター化クエリを実行してみます。
DECLARE @param nchar(100) = N’AAAAAA’ |
パラメーターは ‘AAAAAA’ で実行しています。
パラメータを ‘AAAAAA’ で実行していますので予測行数は [999999] となっています。
この状態でパラメータを ‘ZZZZZZ’ に変えて実行してみます。
DECLARE @param nchar(100) = N’ZZZZZZ’ |
既に同一のクエリハッシュのクエリで実行をしていますので、前回実行時のプランが使用されます。
そのため、予測行数も初回実行時の ‘AAAAAA’ で実行された際の 999999 となっています。
一度プロシージャキャッシュをクリアして ‘ZZZZZZ’ で実行した場合、予測行数は 1 となっています。
パラメータクエリを実行した場合、最初に実行したパラメータでクエリがコンパイルされますので、リコンパイルされるまでは、パラメータが変わってもそのプランが使用されることになります。
今回はどちらのパラメータでもプランに差はないので大きな問題にはならないと思いますが、パラメータの差によって 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’ |
パラメータは ‘AAAAAA’ で実行していますが、OPTIMIZE FOR で ‘ZZZZZZ’ としてヒントを指定していますので、パラメータとしては ‘ZZZZZZ’ が渡された状態でクエリがコンパイルされます。
OPTIMIZE FOR ヒントを使うことで同じのパラメータが渡された状態でクエリを実行することが可能となります。
■OPTIMIZE FOR UNKNOWN
SQL Server 2008 以降では OPTIMIZE FOR に UNKN
OWN というオプションが追加されています。
クエリ ヒント (Transact-SQL)
クエリ オプティマイザーでクエリをコンパイルおよび最適化するときに、強制パラメーター化によって作成されたパラメーターも含め、すべてのローカル変数に対して初期値の代わりに統計データを使用することを指定します。
このオプションを使うとパラメータではなく統計データをもとにクエリのコンパイルが行われるようになります。
UNKNOWN の指定には二種類があり、特定のパラメータだけを UNKNOWN として指定、
DECLARE @param nchar(100) = N’AAAAAA’ |
クエリのパラメータ全体を UNKNOWN として指定することができます。
–DBCC FREEPROCCACHE |
UNKNOWN を使用すると統計情報をもとにクエリがコンパイルされるため予測行数が ‘AAAAAA’ / ‘ZZZZZZ’ とも違っていることが確認できます。
■トレースフラグ 4136 を指定して実行
トレースフラグ 4136 を指定するとストアドプロシージャやパラメータ化クエリのデフォルトの動作を UNKNOWN にすることができるようです。
先ほどのクエリを以下のように変更してみます。
DBCC TRACEON(4136, -1) |
左がトレースフラグを指定しないで実行したクエリ、右がトレースフラグを指定して実行したクエリとなります。
トレースフラグを実行した場合は予測行数が UNKNOWN を指定した場合と同じになっています。
具体的にプランが変わるようなデータを持っていなかったので、概略だけになりますが、クエリ実行は奥が深いですね~。