Parameter sniffing
Concept
SQL Server系エンジンの動作で、コンパイル時に見られたパラメータ値に基づいてクエリプランをコンパイルし、そのキャッシュ済みプランを再利用する挙動を指します。値が大きく異なる場合にパフォーマンスが低下することがあり、パラメータ感度とも呼ばれます。
Description
パラメータスニッフィング(Parameter sniffing)は、SQL Server系のデータベースエンジンに見られる動作で、コンパイル時に見られたパラメータ値に基づいてクエリプランがコンパイルされ、そのキャッシュ済みプランが再利用されるものです。パラメータ感度(parameter sensitivity)とも呼ばれます。この動作自体は欠陥ではありません。エンジンはクエリのコンパイルまたは再コンパイル時に現在のパラメータ値を調べ、それをクエリオプティマイザーに渡すことで、それらの値に対してより効率的な実行プランを生成できます。問題が生じるのは、キャッシュ済みプランが後で元の値とは大きく異なるパラメータ値に使われる場合です。ある値に適したプランが、別の値では性能不良を起こすことがあるためです。
SQL Server向けのMicrosoftのクエリ処理アーキテクチャガイドは、パラメータ感度に関するセクションでパラメータスニッフィングを説明しています。同ガイドによると、この処理はストアドプロシージャ、sp_executesqlを通じて実行されるクエリ、およびプリペアドクエリに適用されます。コンパイル済みプランはプランキャッシュに格納され、後の実行で再利用されるため、プラン再利用と直接関係します。したがって、コンパイルを引き起こした最初の値の組み合わせが、後続の呼び出しに渡されるプランの形を決めることになり、どの呼び出しがプランをコンパイルしたかによって結果が変わる理由がここにあります。
ガイドが挙げている対処法は、OPTION (RECOMPILE)クエリヒントです。このヒントを使うと、オプティマイザーはパラメータとローカル変数をコンパイル時定数として扱い、それらをリテラルに畳み込むことができます。使われる値は、元々渡された値とは限らず、ステートメントの実行直前に有効な値です。これは入力データの値のばらつきが大きい場合や偏りがある場合に役立つことがあります。一方でガイドは、このヒントはすべての実行でクエリを再コンパイルするため高コストになりうると警告し、採用する前にワークロード全体への影響を測定するよう助言しています。また、さらに詳しい情報として、Parameter Sensitive Plan最適化に関する別の記事も紹介されています。
同じドキュメントに含まれる関連する詳細として、ローカル変数があります。ローカル変数の値はコンパイル時にスニッフィングされません。代わりに、オプティマイザーは密度情報から見積もりを行い、統計情報がない場合は等価比較や範囲比較に対する固定の割合による推測値を使用します。遅いクエリを診断する際にはこの違いが重要で、パラメータとローカル変数では見積もりが異なり、その結果として異なるプランになることがあるためです。
実際には、パラメータスニッフィングは、ある入力では速く別の入力では遅いクエリとして現れたり、プランが削除されて再コンパイルされた後に性能が変わったりする形で表れます。M365.FMのエピソード「Fix Bad Data Upstream with T-SQL Contracts」は、パラメータスニッフィングを一般的なエンタープライズ障害の一つとして挙げ、ランダム性に見せかけることがあると説明しています。また同エピソードは、プランの形をガバナンスの問題として捉え、プランの形が変われば支出と信頼性も変わると述べ、プランの挙動がコストの安定性と運用リスクにつながることを示しています。
エピソード「Design Reliable and Resilient Azure Architectures」は、パラメータスニッフィングをAzure SQL Databaseのトラブルシューティングの流れの中に位置づけています。Intelligent InsightsとQuery Performance Insightを有効にし、長時間実行されるクエリを取得し、実行プランを確認して、インデックスの不足やパラメータスニッフィングを特定することを提案しています。さらに、Azure SQLの自動チューニングの推奨事項や、Azure Advisorによるインデックスの提案、クエリの最適化、適切なインデックス作成、DTUまたはvCoreのスケーリングやHyperscaleへの移行の検討にも触れています。
これらの資料を総合すると、パラメータスニッフィングはプランキャッシュにおけるトレードオフとして捉えられています。コンパイル済みプランを再利用すればコンパイルの作業を省けますが、ある値に合わせて調整されたプランは他の値には適さないことがあります。これに関する典型的な判断には、実行プランの確認、データ分布に偏りがあるかの確認、そして、より適したプランによる効果とOPTION (RECOMPILE)のようなオプションのコンパイルコストを比較検討することが含まれます。
Overview compiled with AI from M365.FM episodes and the facts on this page. You can improve it with the edit button below.
| Type | Concept |
|---|
Frequently asked questions
Questions marked with AI were written with AI from the facts on this page.
パラメータスニッフィングとは何ですか? (AI)
コンパイル時に見られたパラメータ値に対してクエリプランをコンパイルし、そのキャッシュ済みプランを再利用する、SQL Server系エンジンの動作です。後のパラメータ値が大きく異なる場合、性能が低下することがあります。パラメータ感度とも呼ばれます。
OPTION (RECOMPILE)はパラメータスニッフィングの問題にどう影響しますか? (AI)
Microsoftのクエリ処理ガイドによると、このヒントはオプティマイザーがパラメータとローカル変数をコンパイル時定数として扱い、それらをリテラルに畳み込めるようにするもので、値のばらつきが大きい、または偏りのあるデータに役立つことがあります。クエリはすべての実行で再コンパイルされるため高コストになりえ、そのためワークロード全体への影響を先に測定すべきです。
ローカル変数もパラメータと同じ方法でスニッフィングされますか? (AI)
いいえ。ドキュメントによると、ローカル変数の値はコンパイル時にスニッフィングされません。代わりにオプティマイザーは密度情報から見積もりを行い、統計情報がない場合は等価比較および範囲比較に対する固定の割合による推測値を使用します。
Community wiki
Short info above, community knowledge below: add practical tips, examples or corrections. New contributions are reviewed first; trusted members publish directly.
Experts and companies for this topic
Nobody has listed this topic yet. Add it to your profile.
Maintained by Mirko Peters (Microsoft MVP) · Last updated: