2026年2月24日火曜日

SQL Server パラメータスニッフィングの原因と対策｜キャッシュプラン最適化の落とし穴と解決アプローチ

同じクエリなのに“たまに遅い”原因はキャッシュプランにあり

SQL Serverで「同じクエリなのに、たまに極端に遅くなる」現象が発生することがあります。 その多くはパラメータスニッフィング（Parameter Sniffing）が原因です。
これは、最初に実行されたパラメータ値をもとに最適化した実行計画をキャッシュし、 以降の異なる値でも同じプランを再利用してしまうことで発生します。
本記事では、この仕組み・原因・具体的な対策を実務的に解説します。

目次

第1章　パラメータスニッフィングとは

SQL Serverでは、クエリの初回実行時に最適化エンジンが実行計画を生成し、そのプランをキャッシュします。 これにより、次回以降の実行では再コンパイルを省略して高速化します。

しかし、最初に使用したパラメータ値が「極端に偏ったデータ」だった場合、 そのプランが他の値にも適用され、行数推定のズレやインデックス選択の誤りを引き起こします。 これが「パラメータスニッフィング」です。

第2章　発生の典型例と再現パターン

次の例では、SalesテーブルにCustomerIDごとの売上が偏っているとします。

CREATE PROCEDURE dbo.GetSalesByCustomer
    @CustomerID INT
AS
SELECT * FROM dbo.Sales
WHERE CustomerID = @CustomerID;
GO

最初に `@CustomerID = 1`（数百万件）で実行されると、SQL Serverは「大量行向け」のプランを生成します。 しかし次に `@CustomerID = 5000`（数行）で実行しても、同じ重いプランが再利用され、 実行時間が大幅に悪化します。

EXEC dbo.GetSalesByCustomer @CustomerID = 1;    -- 初回実行
EXEC dbo.GetSalesByCustomer @CustomerID = 5000; -- 2回目：遅くなる

このように、最初の実行パラメータが全体の最適化結果を歪めるのが典型的症状です。

第3章　なぜ発生するのか（仕組みの理解）

最適化エンジンは、実行時に渡されたパラメータ値を“嗅ぎ取り”（Sniffing）、 その値に基づいて推定行数を計算します。 以降の実行では、この初回プランをキャッシュから再利用します。

Query Store（IT0032）で見ると、同じクエリIDに複数のプランが存在し、 最初のプランが“支配的”に再利用されているケースが見られます。

また、統計情報（IT0033）との関係も密接で、古い統計が残っていると推定誤差がさらに拡大します。

第4章　発生状況の検知と確認方法

パラメータスニッフィングの兆候は、Query Storeや実行計画比較で確認できます。

-- 同一クエリで複数のプランを持つものを抽出
SELECT q.query_id, COUNT(DISTINCT p.plan_id) AS PlanCount
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p ON q.query_id = p.query_id
GROUP BY q.query_id
HAVING COUNT(DISTINCT p.plan_id) > 1;

また、実行計画XMLで「ParameterCompiledValue」と「ParameterRuntimeValue」が異なる場合、 スニッフィングが起きていることを示します。

-- 実行計画の中で確認できる例（抜粋）
<ParameterList>
  <ParameterCompiledValue>(@CustomerID = 1)</ParameterCompiledValue>
  <ParameterRuntimeValue>(@CustomerID = 5000)</ParameterRuntimeValue>
</ParameterList>

第5章　主要な対策パターン

① OPTIMIZE FOR 句で最適値を指定

特定の代表値に基づいて最適化させる方法です。

CREATE PROCEDURE dbo.GetSalesByCustomer
    @CustomerID INT
AS
SELECT * FROM dbo.Sales
WHERE CustomerID = @CustomerID
OPTION (OPTIMIZE FOR (@CustomerID = 100));

“典型的なパラメータ”を指定することで、安定したプランを維持できます。

② OPTIMIZE FOR UNKNOWN で中庸プランを生成

パラメータを“未知”として最適化し、平均的なプランを採用します。

SELECT * FROM dbo.Sales
WHERE CustomerID = @CustomerID
OPTION (OPTIMIZE FOR UNKNOWN);

分布が偏ったデータでは、最も安全で汎用的な方法です。

③ RECOMPILE オプションで都度再最適化

再コンパイルを強制し、毎回パラメータに応じて最適化をやり直します。

SELECT * FROM dbo.Sales
WHERE CustomerID = @CustomerID
OPTION (RECOMPILE);

短時間クエリには適していますが、頻繁な再コンパイルはCPU負荷増につながるため注意が必要です。

④ ストアド分離（パラメータ範囲別プロシージャ）

アクセス傾向が明確に分かれている場合、範囲ごとにプロシージャを分けるのも実務的です。

IF @CustomerID < 100
    EXEC dbo.GetSales_LowVolume @CustomerID;
ELSE
    EXEC dbo.GetSales_HighVolume @CustomerID;

⑤ Query Storeで安定プランを固定（Force Plan）

Query Store（IT0032）の強制プラン機能を使い、良好なプランを固定化します。

EXEC sp_query_store_force_plan @query_id = 101, @plan_id = 5;

アプリ改修なしで即対応可能ですが、統計変更などで再コンパイルが必要になる場合もあります。

第6章　ベストプラクティスとチューニング指針

  • 統計情報を最新に保つ（古い統計がスニッフィング悪化の主因）
  • 代表的なパラメータを選定し、OPTIMIZE FORで固定する
  • 性能差が極端な場合はRECOMPILEまたはQuery Storeで強制プラン
  • Query Storeの「Automatic Tuning」で自動回帰補正を有効化
  • 再現性確認には実行計画XMLのParameter値比較が有効

スニッフィングは“悪”ではなく、キャッシュ効率と最適化精度のトレードオフです。 正しく制御すれば、性能を安定化しつつキャッシュ恩恵を最大化できます。

まとめ：スニッフィングを理解して安定性能へ

パラメータスニッフィングは、SQL Serverの最適化の本質を理解する上で避けて通れないテーマです。 Query Store（IT0032）でプラン履歴を確認し、統計（IT0033）を最新化したうえで、 必要に応じて OPTIMIZE FOR・RECOMPILE・Force Plan を使い分ければ、 安定したクエリ性能を維持できます。
“なぜ遅いのか”を分析できる環境を整え、性能トラブルを再現・予防できるDB設計を目指しましょう。

SQL Server学習シリーズ

参考リンク（公式ドキュメント）

SQL Server 解説用イメージ

on
Labels: ,

0 件のコメント:

コメントを投稿

登録: コメントの投稿 (Atom)