【ITニュース解説】SQL Server Parameter Sniffing: What It Is and How to Fix It

データベースを使うアプリケーションを開発したり運用したりしていると、「昨日まで速かった処理が、今日になったら突然遅くなった。何も変更していないはずなのに」という現象に遭遇することがある。このような問題の背景には、「パラメーター・スニッフィング」というSQL Serverの特性が隠れていることが多い。これはSQL Serverにおけるパフォーマンス低下の一般的な原因の一つであり、その仕組みを理解すれば、診断と対処が容易になる。

パラメーター・スニッフィングとは、パラメーターを使ってデータを取り出すクエリや、ストアドプロシージャが初めて実行される時に発生する現象のことだ。SQL Serverは、そのクエリが実行された際のパラメーターの値を「嗅ぎ取る（スニッフする）」。例えば、「顧客IDが100の注文を取得する」というクエリが最初に実行されたら、SQL Serverは「顧客ID=100」という値を読み取る。そして、その「顧客ID=100」という値に最も適したデータ取得方法、つまり「実行プラン」を作成する。この実行プランは、その後の同じクエリの実行のためにキャッシュされる。つまり、一度作成されたプランは、その後、異なるパラメーター値（例えば「顧客ID=200」）でクエリが実行されても再利用される仕組みになっている。

通常、この仕組みは良い方向に働く。なぜなら、クエリを実行するたびに実行プランを新たに作る手間（コンパイルコスト）を省けるため、CPUの負荷が減り、処理が高速になるからだ。しかし、データベースのデータに大きな偏りがある場合、この仕組みが問題を引き起こす。例えば、注文テーブルがあり、特定の大きな顧客の注文が200万件ある一方で、ほとんどの顧客の注文は50件しかないと想像してみよう。このような状況で、最初に「注文が50件の小さな顧客」のIDでクエリが実行されると、SQL Serverはその50件のデータ取得に最適なプラン（例えばインデックスを使った高速な検索）を作成し、キャッシュする。しかし、次に「注文が200万件の大きな顧客」のIDで同じクエリが実行されても、キャッシュされた「50件向け」のプランがそのまま使われてしまう。50件には最適でも、200万件のデータ取得には非常に効率の悪いプランなので、処理は途端に遅くなる。逆に、最初に「注文が200万件の大きな顧客」のIDでクエリが実行された場合、200万件のデータ取得に最適なプラン（例えばテーブル全体を読み込むスキャン）が作成されキャッシュされる。今度は、50件の小さな顧客のクエリが実行された際に、この「200万件向け」のプランが再利用され、無駄な処理が発生し、やはり処理が遅くなる。このように、データベースの再起動や統計情報の更新、キャッシュからのプランの削除（プランエビクション）といったタイミングで、最初にどのようなパラメーター値でクエリが実行されるかによって、その後の全てのクエリのパフォーマンスが決まってしまう。これが、パフォーマンス問題が突然現れたり消えたりするように見える理由である。

パラメーター・スニッフィングが原因であることを確認するには、いくつかの方法がある。まず、特定のプロシージャやクエリが、パラメーター値によっては速く、別のパラメーター値では遅くなる場合、あるいはコード変更がないのにパフォーマンスが突然変化する場合は、パラメーター・スニッフィングの兆候である。実際にパフォーマンスの問題が発生した際には、SQL Server Management Studioなどのツールで「実際の実行プラン」を確認するのが有効だ。実行プラン上で、SQL Serverが「推定した行数」と「実際に読み込んだ行数」との間に大きな差がある場合、それが典型的なパラメーター・スニッフィングのサインである。特に、メインのテーブルアクセス部分でこの乖離が見られる場合は、ほぼ間違いなく原因の一つと考えてよい。また、実行プランのルートオペレーター（一番上の操作）のプロパティを確認すると、「コンパイル時のパラメーター値」と「実行時のパラメーター値」が表示される。これらが大きく異なる場合も、問題の発見につながる。さらに、SQL Server 2016以降で利用できる「Query Store（クエリストア）」機能は非常に強力だ。Query Storeの「遅延したクエリ（Regressed Queries）」レポートを見れば、プランが変更されてパフォーマンスが悪化したクエリを特定でき、異なる実行プランを並べて比較することが可能だ。

この問題に対処するための解決策はいくつか存在する。手軽なものから、より根本的なものまで、状況に応じて選択できる。

一つ目の方法は、Query Storeを活用することだ。Query Storeに「良い実行プラン」と「悪い実行プラン」の両方が記録されている場合、Query Storeのユーザーインターフェースから良いプランを強制的に適用できる。sp_query_store_force_plan というストアドプロシージャを使っても良い。これはコード変更なしで即座に問題の症状を抑えることができるが、特定のプランを固定してしまうため、一時的な対処と捉え、後でより恒久的な解決策を検討する必要がある。

二つ目の方法は、クエリに OPTION (RECOMPILE) ヒントを追加することだ。このヒントを追加すると、そのクエリが実行されるたびに、SQL Serverは現在のパラメーター値に基づいて新しい実行プランを毎回生成するようになる。これにより、常にその時のパラメーターに最適なプランが使われるため、パラメーター・スニッフィングの問題は解消される。しかし、毎回プランをコンパイルするコストが発生するため、1秒間に何千回も実行されるような非常に頻繁に呼び出されるクエリには不向きだ。一方で、1分間に数回程度しか実行されないようなクエリであれば、非常に効果的な解決策となる。

三つ目の方法は、OPTIMIZE FOR ヒントを使用することだ。もし、ほとんどのパラメーター値に対して「これくらいのプランがあれば十分」という基準がある場合、SQL Serverに「このパラメーター値に最適化してプランを作ってほしい」と指示できる。例えば、OPTION (OPTIMIZE FOR (@CustomerId = 42)) のように、典型的な顧客IDを指定する。あるいは、OPTION (OPTIMIZE FOR UNKNOWN) を使うことで、SQL Serverがパラメーター値を嗅ぎ取らずに、統計情報の平均密度に基づいてプランを作成するようになる。この方法は、安定した実行プランを得られるが、非常に特殊な値（外れ値）に対しては、最適なプランにはならない可能性がある。

四つ目の方法は、偏った値に対応するためのコードパスを分離することだ。もし、一部のパラメーター値（例えば、ごく少数の巨大な顧客ID）が極端に処理に時間がかかる原因になっていると分かっている場合、それらの特殊な値は別のストアドプロシージャや別のクエリパスを経由するようにコードを修正する。これにより、通常のパラメーター値に対するプランと、特殊なパラメーター値に対するプランがそれぞれ個別にキャッシュされ、両方のグループで最適なプランを利用できるようになる。コードの変更は必要になるが、リコンパイルのコストをかけることなく、両者に最適なパフォーマンスを提供できる。

五つ目の方法は、より良いインデックスを作成することだ。時には、パラメーター・スニッフィングの問題自体が、適切なインデックスが存在しないことに起因している場合がある。例えば、先ほどの注文テーブルの例であれば、CustomerId 列をキーとし、OrderDate と Total 列をINCLUDEしたカバリングインデックスを作成することで、CustomerIdでの検索時に必要な全ての情報がインデックス内に含まれるようになる。これにより、SQL Serverはテーブル本体へのアクセス（キー参照）をせずにインデックスのみで処理を完結させられるため、少数のデータにも多数のデータにも効率的なインデックスシークプランが適用できるようになる。これは、根本的な解決策となることが多い。

最後に、SQL Server 2022以降のバージョンでは、「Parameter Sensitive Plan (PSP) optimization」という機能が利用できる。データベースの互換性レベルが160である場合、この機能は同じクエリに対して複数の実行プランをキャッシュし、実行時のパラメーター値に応じて適切なプランを自動的に選択する。これにより、これまでの多くのパラメーター・スニッフィング問題をSQL Serverが自動的に解決してくれる。ただし、全てのケースに対応しているわけではないため、利用する前にデータベースの互換性レベルを確認し、その効果を評価することが重要だ。

一方で、パラメーター・スニッフィングの問題を解決しようとして、避けるべき対処法も存在する。プロダクション環境のサーバーで、特定のクエリのパフォーマンス問題を解決するために、データベース全体のプランキャッシュをクリアする DBCC FREEPROCCACHE コマンドを使うのはやめるべきだ。これは一時的に問題を解消するかもしれないが、キャッシュ全体がクリアされることで、全てのクエリが再度コンパイルされる必要が生じ、サーバー全体の負荷が急増する可能性がある。また、多くの場合、再び最初に実行されるパラメーター値によってパフォーマンスが決定されてしまい、結局同じ問題が再発するだけになる。同様に、パラメーターの値をローカル変数にコピーすることでスニッフィングを回避しようとする方法も、一見効果があるように見えるが、これは OPTIMIZE FOR UNKNOWN と同様に、SQL Serverの最適化を制御不能な形で無効化してしまうため、意図しない形で他のクエリのパフォーマンスを悪化させる可能性がある。安易な全体的な修正策として採用すべきではない。

以上のことから、パラメーター・スニッフィング問題に直面した際のクイックチェックリストとして、まずは実行プランでコンパイル時と実行時のパラメーター値を比較すること、Query Storeでプランの変更やパフォーマンス低下が起きていないか確認すること、そして一時的な対処として既知の良いプランを強制することが挙げられる。その後、RECOMPILE、OPTIMIZE FOR、コードパスの分離、またはインデックスの改善といった永続的な解決策を検討し、SQL Server 2022以降を利用している場合は、PSP最適化の活用も視野に入れると良いだろう。