SQL Serverの性能問題を調査していると、突然クエリの実行時間が遅くなる原因としてParameter Sniffing（パラメータスニッフィング）という言葉を目にすることがあります。
Parameter Sniffingは、ストアドプロシージャなどで使用されるパラメータ値をSQL Serverが初回コンパイル時に参照し、その値に適した実行プランを作成してキャッシュする仕組みです。しかし、その後に異なるパラメータ値で実行された場合、最初に作られたプランが必ずしも適切とは限らず、性能低下につながることがあります。
この記事では、Parameter Sniffingの仕組み、発生する理由、実際の影響、代表的な対策方法について解説します。
Parameter Sniffingとは初回パラメータ値を利用して実行計画を作成する仕組み
Parameter Sniffingとは、SQL Serverがクエリをコンパイルするときに、渡されたパラメータ値を確認して、その値に適した実行プランを生成する動作を指します。
つまり質問にあるように、「初回コンパイル時のパラメータ値に最適化されたプランが再利用される現象」と考えることができます。
ただし、正確にはSQL Serverが意図的に問題を起こしているわけではありません。SQL Serverは最初に取得できた情報を利用して、最も効率的だと判断した実行プランをキャッシュし、次回以降の処理速度を向上させようとしています。
Parameter Sniffingが発生する具体的な例
例えば、顧客検索を行うストアドプロシージャがあるとします。
CREATE PROCEDURE GetOrders
@CustomerID INT
AS
SELECT * FROM Orders WHERE CustomerID = @CustomerID;最初に実行した値が、注文数が非常に少ない顧客IDだった場合、SQL Serverは「少ないデータを探す処理」と判断し、インデックスシークを利用する実行プランを作成する可能性があります。
しかし、その後に大量の注文データを持つ顧客IDで実行すると、同じプランでは効率が悪くなり、テーブルスキャンや別の結合方法のほうが高速だったとしても、以前作られたプランが再利用されます。
このように、最初のパラメータ値には適しているが、別の値では性能が悪化する状態がParameter Sniffingによる問題です。
なぜParameter Sniffingによる性能低下が発生するのか
SQL Serverはクエリ実行時に毎回ゼロから実行計画を作成しているわけではありません。コンパイル処理にはCPUコストがかかるため、一度作成した実行プランをプランキャッシュに保存します。
通常、この仕組みは性能向上につながります。しかし、データ分布に偏りがある場合、1つの実行プランをすべてのパラメータ値に適用することが問題になります。
例えば、全顧客の99%が数件しか注文していない一方で、一部の大口顧客だけ数百万件の注文を持っているようなシステムでは、同じ検索条件でも最適な処理方法が大きく変わります。
Parameter Sniffingが発生しているか確認する方法
Parameter Sniffingの調査では、同じクエリなのにパラメータによって実行時間が大きく変化するか確認します。
代表的な確認ポイントは以下の通りです。
- 同じストアドプロシージャなのに実行時間が極端に違う
- 再起動直後やプランキャッシュ削除後だけ速い、または遅い
- 実行プランを見ると、実際の行数と推定行数に大きな差がある
- 特定のパラメータ値だけ処理が遅い
SQL Server Management Studioの実行プラン表示やQuery Storeを利用すると、過去に使用された実行プランや性能変化を確認できます。
Parameter Sniffingへの代表的な対策方法
Parameter Sniffingの対策方法はいくつかありますが、状況によって適切な方法を選択する必要があります。
OPTION(RECOMPILE)を利用する
クエリごとに最新のパラメータ値で実行計画を作成する方法です。
SELECT * FROM Orders WHERE CustomerID = @CustomerID OPTION(RECOMPILE);常に最適なプランを作成できますが、毎回コンパイルが発生するため、実行頻度が高い処理ではCPU負荷に注意が必要です。
OPTIMIZE FOR UNKNOWNを利用する
特定のパラメータ値ではなく、統計情報から平均的な実行計画を作成させる方法です。
SELECT * FROM Orders WHERE CustomerID = @CustomerID OPTION(OPTIMIZE FOR UNKNOWN);データの偏りが大きい場合には有効ですが、特定の値に対して最適なプランにはならない場合があります。
パラメータをローカル変数へコピーする
ストアドプロシージャ内でパラメータをローカル変数へ代入することで、SQL Serverに特定値を直接認識させない方法もあります。
DECLARE @LocalCustomerID INT;
SET @LocalCustomerID = @CustomerID;ただし、この方法はSQL Serverの推定方法に影響するため、必ず性能確認を行う必要があります。
Parameter Sniffingは必ず悪いものではない
Parameter Sniffingという名前から問題のように感じますが、この仕組み自体はSQL Serverの重要な性能改善機能です。
多くのケースでは、最初に作成された実行プランがそのまま高速処理につながります。問題になるのは、データ分布の偏りが大きく、パラメータによって最適な処理方法が大きく変わる場合です。
そのため、すべてのストアドプロシージャで対策を入れるのではなく、実際の性能問題を確認したうえで対応することが重要です。
まとめ
SQL ServerのParameter Sniffingは、初回コンパイル時に使用されたパラメータ値を参考に作成された実行プランがキャッシュされ、その後の実行でも再利用される仕組みです。
この仕組みにより高速化できる場合もありますが、データ量や分布が大きく異なるパラメータを扱う場合には、最初に作成されたプランが別のケースで不適切になり、性能低下を引き起こすことがあります。
対策としてはOPTION(RECOMPILE)、OPTIMIZE FOR UNKNOWN、クエリ設計の見直しなどがありますが、重要なのは実行計画やデータ特性を確認し、原因に合わせた方法を選択することです。
コメント