【SQL Server】パラメータスニッフィングとは｜実行計画が変わる原因と対処法

SQL Serverで「普段は速いストアドプロシージャが急に遅い」「同じSQLなのにパラメータ値によって速い時と遅い時がある」「実行計画を取り直すと直る」といった現象が起きることがあります。この原因のひとつが、パラメータスニッフィングです。

パラメータスニッフィングは、SQL Serverがコンパイル時のパラメータ値を使って実行計画を作る仕組みです。多くの場合は性能向上に役立ちますが、データ分布が偏っているテーブルでは、ある値に最適化された実行計画が別の値には合わず、SQLが急に遅くなることがあります。

まず疑う症状

次のような症状がある場合は、パラメータスニッフィングを候補に入れて調査します。ただし、ロック待ち、統計情報の古さ、インデックス不足でも似た症状になるため、実行計画と待機情報で切り分けます。

1. 同じストアドプロシージャなのに、パラメータ値によって極端に速さが違う
2. 再コンパイル、統計情報更新、サーバー再起動の直後だけ速くなる
3. 実行計画の推定行数と実際の行数が、特定値で大きくズレる
4. ある値では Index Seek、別の値ではスキャンのほうが妥当そうに見える
5. 少数行向けの Key Lookup が大量行で繰り返されている

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

パラメータスニッフィングとは、SQL Serverがストアドプロシージャ、sp_executesql、準備済みクエリなどをコンパイルするときに、その時点のパラメータ値を見て実行計画を作る動きです。Microsoft Learnでも、SQL Serverはコンパイルまたは再コンパイル時に現在のパラメータ値を使い、より効率的な実行計画を生成しようとする、と説明されています。

たとえば、注文テーブルに Status 列があり、'Cancelled' は少数、'Completed' は大量にあるとします。少数行を返す値で最初にコンパイルされると、Index Seek と Key Lookup に向いた計画が作られます。その計画が大量行を返す値にも再利用されると、Lookupが大量発生して遅くなることがあります。

この問題を切り分けるには、まず実行計画の読み方を押さえておく必要があります。

SQL Serverの実行計画の見方

パラメータスニッフィングが問題になる条件

パラメータスニッフィングは常に悪いものではありません。むしろ、実際のパラメータ値に合わせて計画を作れるため、通常は有益です。問題になりやすいのは、同じSQLに対して最適な実行計画がパラメータ値によって大きく変わる場合です。

統計情報が古い場合は、パラメータスニッフィング以前に行数見積もりが外れていることがあります。統計情報の確認と更新は次の記事で整理しています。

SQL Serverで統計情報を更新する方法

再現しやすい例

次の例では、@Status の値によって返る行数が大きく違う前提です。少数行の値で最初にコンパイルされると、その値に合う計画がキャッシュされ、大量行の値でも再利用されることがあります。

CREATE OR ALTER PROCEDURE dbo.SearchOrdersByStatus
    @Status varchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        OrderID,
        CustomerID,
        OrderDate,
        Status,
        TotalAmount
    FROM dbo.Orders
    WHERE Status = @Status
    ORDER BY OrderDate DESC;
END;
GO

-- 少数行の値で初回実行
EXEC dbo.SearchOrdersByStatus @Status = 'Cancelled';

-- 大量行の値で同じ計画が再利用される可能性がある
EXEC dbo.SearchOrdersByStatus @Status = 'Completed';

このとき、少数行では問題なかった Key Lookup が、大量行では数万回、数十万回繰り返されることがあります。逆に、大量行向けのスキャン計画が少数行にも再利用され、少数検索が遅くなることもあります。

まず確認すること

パラメータスニッフィングを疑うときは、いきなりヒント句を付ける前に、実行計画と実測値を確認します。特に、推定行数と実際の行数の差、キャッシュされた計画、実行時のパラメータ値を見ます。

SELECT
    DB_NAME(st.dbid) AS database_name,
    OBJECT_NAME(st.objectid, st.dbid) AS object_name,
    qs.execution_count,
    qs.total_worker_time / NULLIF(qs.execution_count, 0) AS avg_cpu_time,
    qs.total_elapsed_time / NULLIF(qs.execution_count, 0) AS avg_elapsed_time,
    qs.total_logical_reads / NULLIF(qs.execution_count, 0) AS avg_logical_reads,
    qs.last_execution_time,
    qp.query_plan,
    st.text AS sql_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE st.objectid = OBJECT_ID(N'dbo.SearchOrdersByStatus')
ORDER BY qs.last_execution_time DESC;

プランキャッシュの情報は、キャッシュから消えると確認できません。継続的に調査したい場合は、Query Storeを使うと、実行計画の変化や回帰を追いやすくなります。

統計情報とデータ分布を確認する

パラメータスニッフィングの問題は、データ分布の偏りとセットで起きやすいです。まず対象列の統計情報を確認し、行数、サンプリング行数、変更行数を見ます。

SELECT
    s.name AS stats_name,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID(N'dbo.Orders')
ORDER BY sp.modification_counter DESC;

値の偏りも確認します。同じ列でも、値ごとの件数が大きく違うなら、パラメータ値によって最適な計画が変わりやすくなります。

SELECT
    Status,
    COUNT(*) AS row_count
FROM dbo.Orders
GROUP BY Status
ORDER BY row_count DESC;

日付条件で検索範囲が大きく変わる場合も同じです。日付列を関数で加工すると、インデックスや統計情報を活かしにくくなることがあります。日付のみ比較する場合は、列側を加工せず範囲条件にするのが基本です。

SQLで時間を無視して日付のみで比較する方法

対策1: 統計情報を更新する

統計情報が古い場合は、まず統計情報の更新を検討します。ただし、統計情報更新は万能ではありません。データ分布が偏っていて、値によって最適な計画が本当に違う場合は、統計情報を最新にしても1つのキャッシュ計画では吸収しきれないことがあります。

-- 対象テーブルの統計情報を更新
UPDATE STATISTICS dbo.Orders;

-- 対象統計が明確なら統計単位で更新
UPDATE STATISTICS dbo.Orders IX_Orders_Status;

-- 偏りが強い重要統計ではFULLSCANも検証する
UPDATE STATISTICS dbo.Orders IX_Orders_Status WITH FULLSCAN;

対策2: OPTION (RECOMPILE) を使う

OPTION (RECOMPILE) を付けると、そのステートメントは実行時に再コンパイルされ、現在のパラメータ値に合わせた実行計画を作れます。パラメータ値によって最適な計画が大きく違うSQLでは効果が出やすいです。

CREATE OR ALTER PROCEDURE dbo.SearchOrdersByStatus
    @Status varchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        OrderID,
        CustomerID,
        OrderDate,
        Status,
        TotalAmount
    FROM dbo.Orders
    WHERE Status = @Status
    ORDER BY OrderDate DESC
    OPTION (RECOMPILE);
END;

ただし、実行のたびにコンパイルコストが発生します。頻繁に実行される軽いSQLに安易に付けると、CPU負荷が増えることがあります。重い検索SQL、実行頻度が低い管理系SQL、パラメータ値で大きく計画が変わるSQLに絞って使います。

対策3: OPTIMIZE FOR UNKNOWNを使う

OPTIMIZE FOR UNKNOWN は、コンパイル時の実際のパラメータ値ではなく、平均的な選択性を使って計画を作るためのヒントです。特定の初回パラメータに引っ張られたくない場合に使えます。

CREATE OR ALTER PROCEDURE dbo.SearchOrdersByStatus
    @Status varchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        OrderID,
        CustomerID,
        OrderDate,
        Status,
        TotalAmount
    FROM dbo.Orders
    WHERE Status = @Status
    ORDER BY OrderDate DESC
    OPTION (OPTIMIZE FOR UNKNOWN);
END;

平均的な計画になるため、極端に少ない値や極端に多い値では最適ではないことがあります。それでも、特定値に最適化された計画が別の値で大きく外れるより、全体として安定する場合があります。

対策4: OPTIMIZE FOR 特定値を使う

業務上よく使う代表的な値が決まっている場合は、OPTIMIZE FOR (@parameter = value) で特定値に合わせた計画を作る方法もあります。ただし、代表値の選び方を間違えると別の値で遅くなります。

SELECT
    OrderID,
    CustomerID,
    OrderDate,
    Status,
    TotalAmount
FROM dbo.Orders
WHERE Status = @Status
ORDER BY OrderDate DESC
OPTION (OPTIMIZE FOR (@Status = 'Completed'));

この方法は、よく使われる値が明確で、他の値の性能が許容できる場合に検討します。値の分布や業務上の利用割合が変わると、ヒントが合わなくなる点に注意します。

対策5: ローカル変数へ代入する方法は慎重に使う

昔からある回避策として、パラメータをローカル変数へ代入して使う方法があります。ローカル変数の値はコンパイル時にスニッフされにくく、平均的な見積もりに近い計画になります。ただし、見積もり精度が下がることも多く、現在は第一候補にはしにくいです。

CREATE OR ALTER PROCEDURE dbo.SearchOrdersByStatus
    @Status varchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @LocalStatus varchar(20) = @Status;

    SELECT
        OrderID,
        CustomerID,
        OrderDate,
        Status,
        TotalAmount
    FROM dbo.Orders
    WHERE Status = @LocalStatus
    ORDER BY OrderDate DESC;
END;

Microsoft Learnでも、ローカル変数ではコンパイル時に値をスニッフできず、統計情報やヒューリスティックに基づく一般的な見積もりになると説明されています。使う場合は、OPTIMIZE FOR UNKNOWN と比較し、実測で判断します。

対策6: SQLを分岐する

少数行向けと大量行向けで明らかに最適なSQLが違う場合は、条件によってSQLを分ける方法もあります。1つの汎用SQLに無理に寄せるより、処理を分けたほうが安定することがあります。

CREATE OR ALTER PROCEDURE dbo.SearchOrdersByStatus
    @Status varchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    IF @Status = 'Completed'
    BEGIN
        SELECT OrderID, CustomerID, OrderDate, Status, TotalAmount
        FROM dbo.Orders
        WHERE Status = @Status
        ORDER BY OrderDate DESC
        OPTION (RECOMPILE);
    END
    ELSE
    BEGIN
        SELECT OrderID, CustomerID, OrderDate, Status, TotalAmount
        FROM dbo.Orders
        WHERE Status = @Status
        ORDER BY OrderDate DESC;
    END
END;

分岐は可読性と保守性が下がるため、値の偏りが明確で、実行計画が大きく違い、他の対策では安定しない場合に検討します。

SQL Server 2022以降のParameter Sensitive Plan最適化

SQL Server 2022以降では、Parameter Sensitive Plan Optimization、通称PSP最適化があります。これは、単一のキャッシュ計画ではすべてのパラメータ値に合わないケースに対して、パラメータ値の範囲に応じた複数の計画を使えるようにする機能です。Microsoft Learnでは、データ分布が均一でない場合に、単一のキャッシュ計画がすべての値に最適とは限らない問題を扱う機能として説明されています。

SELECT
    name,
    compatibility_level
FROM sys.databases
WHERE name = DB_NAME();

ALTER DATABASE YourDatabaseName
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    QUERY_CAPTURE_MODE = AUTO
);

PSP最適化が使える環境なら、まずSQL Server側の機能で解決できないかを確認します。ただし、SQLの書き方、統計情報、インデックスが悪い場合は、PSPだけで根本解決しないことがあります。また、USE HINT('DISABLE_PARAMETER_SNIFFING')、トレースフラグ4136、データベーススコープ設定の PARAMETER_SNIFFING = OFF などでパラメータスニッフィングを無効化している場合、PSP最適化も働きません。

Query Storeで計画変更を確認する

パラメータスニッフィングでは、同じSQLの実行計画がいつ変わったか、どの計画で遅くなったかを追うことが重要です。Query Storeを使うと、実行計画、実行時間、回帰を確認しやすくなります。

SELECT TOP (30)
    qsq.query_id,
    qsp.plan_id,
    rs.avg_duration,
    rs.avg_cpu_time,
    rs.avg_logical_io_reads,
    qst.query_sql_text
FROM sys.query_store_query AS qsq
JOIN sys.query_store_query_text AS qst
    ON qst.query_text_id = qsq.query_text_id
JOIN sys.query_store_plan AS qsp
    ON qsp.query_id = qsq.query_id
JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = qsp.plan_id
ORDER BY rs.avg_duration DESC;

Query Storeでは計画の強制もできますが、強制した計画が将来も最適とは限りません。データ量や分布が変わると、強制計画が逆に性能劣化の原因になることがあります。一時的な回避策として使い、根本原因の確認も続けます。

プランキャッシュ削除は最終手段にする

遅いSQLが再コンパイルで直ると、プランキャッシュ削除をしたくなることがあります。しかし、インスタンス全体のキャッシュを消すと、多数のSQLが再コンパイルされ、CPU負荷が跳ねることがあります。対象が明確なら、全体ではなく対象プロシージャや対象プランに絞ります。

-- 対象プロシージャを次回実行時に再コンパイルさせる
EXEC sp_recompile N'dbo.SearchOrdersByStatus';

-- プロシージャ定義として毎回再コンパイルする方法もあるが、常用は慎重に
CREATE OR ALTER PROCEDURE dbo.SearchOrdersByStatus
    @Status varchar(20)
WITH RECOMPILE
AS
BEGIN
    SELECT OrderID, CustomerID, OrderDate, Status, TotalAmount
    FROM dbo.Orders
    WHERE Status = @Status;
END;

DBCC FREEPROCCACHE のように広い範囲へ影響する操作は、本番では安易に実行しません。実行する場合は、影響範囲、時間帯、戻し方、監視項目を決めてから行います。

DISABLE_PARAMETER_SNIFFINGは最終寄りの選択肢

SQL Server 2016 SP1以降では、クエリ単位で USE HINT('DISABLE_PARAMETER_SNIFFING') を使う方法があります。これは、実際のパラメータ値を使った見積もりではなく、平均的なデータ分布に基づく見積もりを使わせる方向の対策です。OPTIMIZE FOR UNKNOWN に近い目的で使われます。

SELECT
    OrderID,
    CustomerID,
    OrderDate,
    Status,
    TotalAmount
FROM dbo.Orders
WHERE Status = @Status
ORDER BY OrderDate DESC
OPTION (USE HINT('DISABLE_PARAMETER_SNIFFING'));

データベース単位では PARAMETER_SNIFFING をOFFにする設定もあります。ただし、影響範囲が広く、多くのSQLでパラメータ値を活かした最適化が効かなくなります。基本的には、問題SQLを特定してクエリ単位で対処するほうが安全です。

SELECT
    name,
    value,
    value_for_secondary
FROM sys.database_scoped_configurations
WHERE name IN (
    'PARAMETER_SNIFFING',
    'PARAMETER_SENSITIVE_PLAN_OPTIMIZATION'
);

-- 影響範囲が広いため、本番では安易に実行しない
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;

-- 戻す場合
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;

ロック待ちやデッドロックと切り分ける

パラメータスニッフィングに見えても、実際にはロック待ちやデッドロックが原因の場合があります。実行計画が悪いのか、他セッション待ちなのかを切り分けるため、実行中の待機情報も確認します。

SELECT
    r.session_id,
    r.status,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    r.cpu_time,
    r.logical_reads,
    t.text AS running_sql
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id <> @@SPID
ORDER BY r.wait_time DESC;

SQL Serverでテーブルロックを確認するSQL

SQL Serverのデッドロックの原因と確認方法

対策の選び方

パラメータスニッフィングの対策は、どれか1つが常に正解ではありません。実行頻度、値の偏り、SQLの重さ、影響範囲によって選びます。

やってはいけない対応

本番障害時ほど、広すぎる対策を選びがちです。次の対応は影響範囲が大きいため、基本的には避けます。

よくある質問

パラメータスニッフィングは無効化すべきですか？

基本的には無効化から考えません。多くのSQLでは、実際のパラメータ値を使って計画を作ることが有益です。問題SQLを特定し、そのSQLに合う対策を選ぶのが現実的です。

OPTION (RECOMPILE) は安全ですか？

効果的な場面はありますが、実行ごとにコンパイルコストが発生します。高頻度で実行されるSQLに付けるとCPU負荷が増えるため、実行頻度とSQLの重さを見て判断します。

OPTIMIZE FOR UNKNOWN とローカル変数は同じですか？

似た結果になることはありますが、同じものとして扱わないほうが安全です。ローカル変数は見積もりが粗くなることがあり、意図も伝わりにくいです。平均的な計画を狙うなら OPTIMIZE FOR UNKNOWN を優先して検討します。

統計情報を更新すれば直りますか？

統計情報が古いことが原因なら改善する可能性があります。ただし、値によって最適な計画が大きく違うデータ分布では、統計情報を最新にしても再発することがあります。

SQL Server 2022ならPSP最適化で解決しますか？

解決するケースはありますが、すべてではありません。互換性レベル、対象SQL、述語、データ分布などの条件があります。Query Storeで計画と実行状況を確認しながら判断します。

tempdbが増えるのも関係ありますか？

関係することがあります。パラメータ値に合わない計画で Sort や Hash Match が大きくなり、tempdb にSpillすることがあります。

SQL Serverでtempdbが肥大化する原因と確認方法

まとめ

SQL Serverのパラメータスニッフィングは、コンパイル時のパラメータ値を使って実行計画を作る仕組みです。通常は性能向上に役立ちますが、データ分布が偏っている場合、ある値に最適化された計画が別の値に合わず、SQLが急に遅くなることがあります。

調査では、実行計画、推定行数と実際の行数、logical reads、統計情報、キャッシュ計画、実行時の待機情報を確認します。対策は、統計情報更新、インデックスやSQLの見直し、OPTION (RECOMPILE)、OPTIMIZE FOR UNKNOWN、OPTIMIZE FOR、SQL分岐、Query Store、SQL Server 2022以降のPSP最適化などから、影響範囲に合わせて選びます。広すぎるプランキャッシュ削除や無差別なRECOMPILEではなく、問題SQLに絞って実測しながら対応しましょう。