Parameter Sniffing（パラメータスニッフィング）

Parameter Sniffingとは

Parameter Sniffing（パラメータスニッフィング）は、SQL Serverがストアドプロシージャやパラメータ化されたクエリのコンパイル時に、**パラメータ値を"嗅ぎ取る"（sniff）**プロセスです。

SQL Serverは最初のコンパイル時に渡されたパラメータ値を使用して、その値に最適化された実行プランを生成します。このプランはプランキャッシュに保存され、後続の実行でも再利用されます。

プラン生成のフロー

┌────────────────────────────────┐
│ 1. ストアドプロシージャ呼び出し │
│    EXEC GetOrders @Status = 'A'│
└──────────┬─────────────────────┘
           │
           ▼
┌────────────────────────────────┐
│ 2. パラメータスニッフィング    │
│    @Status = 'A' を認識        │
└──────────┬─────────────────────┘
           │
           ▼
┌────────────────────────────────┐
│ 3. クエリオプティマイザ        │
│    'A'に最適化されたプランを生成│
└──────────┬─────────────────────┘
           │
           ▼
┌────────────────────────────────┐
│ 4. プランキャッシュに保存      │
└──────────┬─────────────────────┘
           │
           ▼
┌────────────────────────────────┐
│ 5. 後続の実行                  │
│    EXEC GetOrders @Status = 'B'│
│    → 同じプランを再利用        │
└────────────────────────────────┘

Parameter Sniffingが発生するシナリオ

Parameter Sniffingは以下のタイプのクエリで発生します：

1. ストアドプロシージャ
2. sp_executesql経由のクエリ
3. 準備されたクエリ（Prepared Queries）

-- 例1: ストアドプロシージャ
CREATE PROCEDURE GetOrdersByStatus
    @Status NVARCHAR(10)
AS
BEGIN
    SELECT OrderID, OrderDate, CustomerID
    FROM Orders
    WHERE Status = @Status;
END;

-- 例2: sp_executesql
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'SELECT * FROM Orders WHERE Status = @Status';
EXEC sp_executesql @SQL, N'@Status NVARCHAR(10)', @Status = 'Active';

-- 例3: 準備されたクエリ（ADO.NETなどから）
-- Parameter markers を使用
SELECT * FROM Orders WHERE Status = ?

Parameter Sniffing問題（PSP: Parameter Sensitive Plan）

問題の本質

最初のコンパイル時のパラメータ値に最適化されたプランが、異なるパラメータ値では最適でない場合に問題が発生します。

具体例

-- Ordersテーブル
-- Status列の分布:
-- 'Active': 100,000行 (95%)
-- 'Completed': 5,000行 (5%)

CREATE PROCEDURE GetOrdersByStatus
    @Status NVARCHAR(20)
AS
BEGIN
    SELECT OrderID, OrderDate, CustomerID, TotalAmount
    FROM Orders
    WHERE Status = @Status;
END;

シナリオ1: 'Active'で最初にコンパイル

-- 最初の実行
EXEC GetOrdersByStatus @Status = 'Active';

生成されるプラン:

• Statusが'Active'の行は全体の95%（100,000行）
• オプティマイザは**Table Scan（全表スキャン）**を選択
• 大量の行を返す場合に効率的

実行プラン:
┌─────────────────┐
│  Table Scan     │
│  (Orders)       │
│  Filter: Status │
└─────────────────┘

問題:

-- 後続の実行
EXEC GetOrdersByStatus @Status = 'Completed';

• Statusが'Completed'の行はわずか5,000行（5%）
• しかし、'Active'用のプラン（Table Scan）が再利用される
• Index Seekの方が効率的なのに使われない
• 不要な95,000行もスキャンしてしまう

シナリオ2: 'Completed'で最初にコンパイル

-- 最初の実行
EXEC GetOrdersByStatus @Status = 'Completed';

生成されるプラン:

• Statusが'Completed'の行は5%（5,000行）
• オプティマイザはIndex Seekを選択
• 少量の行を効率的に取得

実行プラン:
┌─────────────────┐
│  Index Seek     │
│  (IX_Status)    │
│  Seek: Status   │
└─────────────────┘

問題:

-- 後続の実行
EXEC GetOrdersByStatus @Status = 'Active';

• Statusが'Active'の行は100,000行
• しかし、'Completed'用のプラン（Index Seek）が再利用される
• 100,000行に対してIndex Seekを使うのは非効率
• CPUとI/Oを大量に消費

パフォーマンスへの影響

シナリオ	最適なプラン	使用されるプラン	影響
'Active'で初回コンパイル → 'Completed'実行	Index Seek	Table Scan	不要な95,000行をスキャン
'Completed'で初回コンパイル → 'Active'実行	Table Scan	Index Seek	100,000個のシーク操作で非効率

Parameter Sniffing問題の検出

1. プランキャッシュの確認

-- 同じクエリで異なるプランが存在するか確認
SELECT 
    cp.plan_handle,
    cp.usecounts,
    qs.execution_count,
    qs.total_worker_time / qs.execution_count AS avg_cpu,
    qs.total_elapsed_time / qs.execution_count AS avg_duration,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_cached_plans AS cp
INNER JOIN sys.dm_exec_query_stats AS qs 
    ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE st.text LIKE '%GetOrdersByStatus%'
ORDER BY avg_cpu DESC;

2. Query Storeの利用

-- Query Storeで同じクエリの異なるプランを確認
SELECT 
    q.query_id,
    qt.query_sql_text,
    p.plan_id,
    rs.avg_duration,
    rs.avg_cpu_time,
    rs.count_executions
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt 
    ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS p 
    ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs 
    ON p.plan_id = rs.plan_id
WHERE qt.query_sql_text LIKE '%GetOrdersByStatus%'
ORDER BY q.query_id, rs.avg_duration DESC;

3. パフォーマンスの急激な変化

-- 実行時間の大きな変動を検出
SELECT 
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_ms,
    qs.min_elapsed_time AS min_elapsed_ms,
    qs.max_elapsed_time AS max_elapsed_ms,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE qs.max_elapsed_time > (qs.min_elapsed_time * 10)  -- 10倍以上の差
ORDER BY qs.max_elapsed_time - qs.min_elapsed_time DESC;

Parameter Sniffing問題の解決策

解決策1: OPTION (RECOMPILE) ヒント

毎回クエリを再コンパイルし、現在のパラメータ値で最適なプランを生成します。

CREATE PROCEDURE GetOrdersByStatus
    @Status NVARCHAR(20)
AS
BEGIN
    SELECT OrderID, OrderDate, CustomerID, TotalAmount
    FROM Orders
    WHERE Status = @Status
    OPTION (RECOMPILE);
END;

メリット:

• ✅ 常に最適なプラン
• ✅ 実装が簡単

デメリット:

• ❌ コンパイルオーバーヘッド（CPU使用量増加）
• ❌ 高頻度で実行されるクエリには不向き

適用場面:

• 実行頻度が低いクエリ
• パラメータ値による実行プランの違いが大きいクエリ

解決策2: OPTION (OPTIMIZE FOR @parameter = value)

特定のパラメータ値でプランを最適化します。

CREATE PROCEDURE GetOrdersByStatus
    @Status NVARCHAR(20)
AS
BEGIN
    SELECT OrderID, OrderDate, CustomerID, TotalAmount
    FROM Orders
    WHERE Status = @Status
    OPTION (OPTIMIZE FOR (@Status = 'Active'));
END;

メリット:

• ✅ プランキャッシュの再利用
• ✅ 予測可能なパフォーマンス

デメリット:

• ❌ 固定値に最適化（柔軟性なし）
• ❌ 最適値の特定が必要

適用場面:

• 特定のパラメータ値が頻繁に使用される
• ワークロードの95%が特定の値で実行される

解決策3: OPTION (OPTIMIZE FOR UNKNOWN)

統計の平均密度を使用してプランを生成します。

CREATE PROCEDURE GetOrdersByStatus
    @Status NVARCHAR(20)
AS
BEGIN
    SELECT OrderID, OrderDate, CustomerID, TotalAmount
    FROM Orders
    WHERE Status = @Status
    OPTION (OPTIMIZE FOR UNKNOWN);
END;

等価な方法: ローカル変数の使用

CREATE PROCEDURE GetOrdersByStatus
    @Status NVARCHAR(20)
AS
BEGIN
    -- パラメータをローカル変数にコピー
    DECLARE @LocalStatus NVARCHAR(20);
    SET @LocalStatus = @Status;
    
    SELECT OrderID, OrderDate, CustomerID, TotalAmount
    FROM Orders
    WHERE Status = @LocalStatus;  -- ローカル変数を使用
END;

メリット:

• ✅ プランキャッシュの再利用
• ✅ 「そこそこ良い」プランを安定的に取得

デメリット:

• ❌ 最適ではない可能性
• ❌ データ分布が偏っている場合に非効率

適用場面:

• パラメータ値が均等に分散
• 安定したパフォーマンスが重要

解決策4: OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'))

Parameter Sniffingを完全に無効化します。

CREATE PROCEDURE GetOrdersByStatus
    @Status NVARCHAR(20)
AS
BEGIN
    SELECT OrderID, OrderDate, CustomerID, TotalAmount
    FROM Orders
    WHERE Status = @Status
    OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'));
END;

メリット:

• ✅ OPTIMIZE FOR UNKNOWNと同じ効果
• ✅ 明示的な意図の表現

デメリット:

• ❌ SQL Server 2016以降でのみ利用可能

解決策5: OPTION (KEEPFIXED PLAN)

プランの再コンパイルを防ぎます。

CREATE PROCEDURE GetOrdersByStatus
    @Status NVARCHAR(20)
AS
BEGIN
    SELECT OrderID, OrderDate, CustomerID, TotalAmount
    FROM Orders
    WHERE Status = @Status
    OPTION (KEEPFIXED PLAN);
END;

メリット:

• ✅ 統計更新によるプラン変更を防止
• ✅ 「良いプラン」をキャッシュに保持

デメリット:

• ❌ データ変化に対応できない
• ❌ 最初のプランが重要

適用場面:

• 安定したデータ分布
• プランの頻繁な再コンパイルを避けたい

解決策6: DBCC FREEPROCCACHE（一時的対処）

プランキャッシュをクリアします。

-- 特定のプランのみクリア
DBCC FREEPROCCACHE (plan_handle);

-- すべてのプランをクリア（非推奨）
DBCC FREEPROCCACHE;

注意:

• ⚠️ 一時的な対処法
• ⚠️ すべてのプランが再コンパイルされる
• ⚠️ 本番環境での使用は慎重に

解決策7: Parameter Sensitive Plan Optimization (SQL Server 2022+)

SQL Server 2022以降では、PSP最適化機能が導入されました。

-- データベース互換性レベル160で自動的に有効
ALTER DATABASE MyDatabase
SET COMPATIBILITY_LEVEL = 160;

動作:

• パラメータ値の範囲ごとに複数のプランを生成
• 実行時に適切なプランを自動選択

┌─────────────────────────────┐
│ Dispatcher Plan             │
│ (複数のプラン変種を管理)   │
└──────────┬──────────────────┘
           │
    ┌──────┴──────┬──────────┐
    │             │          │
    ▼             ▼          ▼
┌────────┐  ┌────────┐  ┌────────┐
│Plan 1  │  │Plan 2  │  │Plan 3  │
│Range:  │  │Range:  │  │Range:  │
│1-1000  │  │1001-   │  │Unknown │
│        │  │10000   │  │        │
└────────┘  └────────┘  └────────┘

有効化:

-- データベースレベル（デフォルトで有効）
ALTER DATABASE SCOPED CONFIGURATION 
SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON;

-- クエリレベルで無効化
SELECT * FROM Orders WHERE Status = @Status
OPTION (USE HINT ('DISABLE_PARAMETER_SENSITIVE_PLAN'));

メリット:

• ✅ 自動的に最適なプランを選択
• ✅ アプリケーション変更不要

制約:

• ⚠️ SQL Server 2022以降のみ
• ⚠️ 互換性レベル160が必要

解決策の選択ガイド

シナリオ	推奨解決策	理由
実行頻度が低い	RECOMPILE	コンパイルコストが許容範囲
特定の値が頻繁に使用される	OPTIMIZE FOR value	多数派に最適化
パラメータ値が均等分散	OPTIMIZE FOR UNKNOWN	安定したパフォーマンス
SQL Server 2022以降	PSP Optimization	自動最適化
データ分布が変化しやすい	RECOMPILE	常に最新の統計を使用
高頻度実行 + 安定したプラン	KEEPFIXED PLAN	再コンパイル防止

実践例: 包括的な対処

動的な解決策

CREATE PROCEDURE GetOrdersByStatus
    @Status NVARCHAR(20),
    @OptimizationHint NVARCHAR(20) = NULL  -- 'RECOMPILE', 'UNKNOWN', NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 統計情報を取得
    DECLARE @RowCount INT;
    SELECT @RowCount = COUNT(*)
    FROM Orders
    WHERE Status = @Status;
    
    -- 行数に基づいて動的に処理
    IF @RowCount < 1000
    BEGIN
        -- 少量データ: Index Seekを強制
        SELECT OrderID, OrderDate, CustomerID, TotalAmount
        FROM Orders WITH (INDEX(IX_Orders_Status))
        WHERE Status = @Status;
    END
    ELSE IF @OptimizationHint = 'RECOMPILE'
    BEGIN
        -- RECOMPILEを使用
        SELECT OrderID, OrderDate, CustomerID, TotalAmount
        FROM Orders
        WHERE Status = @Status
        OPTION (RECOMPILE);
    END
    ELSE
    BEGIN
        -- デフォルト: OPTIMIZE FOR UNKNOWN
        SELECT OrderID, OrderDate, CustomerID, TotalAmount
        FROM Orders
        WHERE Status = @Status
        OPTION (OPTIMIZE FOR UNKNOWN);
    END
END;

監視とアラート

-- プランキャッシュでParameter Sniffing問題を検出
WITH PlanStats AS
(
    SELECT 
        qs.sql_handle,
        qs.plan_handle,
        qs.execution_count,
        qs.total_elapsed_time / qs.execution_count AS avg_elapsed,
        qs.min_elapsed_time,
        qs.max_elapsed_time,
        CASE 
            WHEN qs.min_elapsed_time > 0 
            THEN CAST(qs.max_elapsed_time AS FLOAT) / qs.min_elapsed_time
            ELSE 0
        END AS elapsed_variance_ratio
    FROM sys.dm_exec_query_stats AS qs
)
SELECT 
    ps.execution_count,
    ps.avg_elapsed / 1000 AS avg_elapsed_ms,
    ps.min_elapsed_time / 1000 AS min_elapsed_ms,
    ps.max_elapsed_time / 1000 AS max_elapsed_ms,
    ps.elapsed_variance_ratio,
    SUBSTRING(st.text, 1, 500) AS query_text
FROM PlanStats ps
CROSS APPLY sys.dm_exec_sql_text(ps.sql_handle) AS st
WHERE ps.elapsed_variance_ratio > 10  -- 10倍以上の差
  AND ps.execution_count > 10  -- 十分な実行回数
ORDER BY ps.elapsed_variance_ratio DESC;

ベストプラクティス

1. 設計段階

• ✅ パラメータ値の分布を理解する
• ✅ 統計を最新に保つ
• ✅ Query Storeを有効化して監視

-- Query Storeの有効化
ALTER DATABASE MyDatabase
SET QUERY_STORE = ON
(
    OPERATION_MODE = READ_WRITE,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_STORAGE_SIZE_MB = 1024
);

2. 開発段階

• ✅ 様々なパラメータ値でテスト
• ✅ 実行プランを確認
• ✅ パフォーマンスメトリクスを記録

-- 実行プランの確認
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

EXEC GetOrdersByStatus @Status = 'Active';
EXEC GetOrdersByStatus @Status = 'Completed';

3. 運用段階

• ✅ 定期的にQuery Storeを確認
• ✅ パフォーマンスの異常を監視
• ✅ 必要に応じてプランをフラッシュ

4. SQL Server 2022以降

• ✅ PSP Optimizationを活用
• ✅ 互換性レベル160を使用
• ✅ Query Storeで複数プランを確認

まとめ

Parameter Sniffingについて

• ✅ Parameter Sniffingは通常は有益な機能
• ⚠️ パラメータ値による実行プランの最適性が異なる場合に問題化
• 🎯 「正しいプランを正しいパラメータで」が重要

問題の兆候

• 同じクエリで実行時間が大きく変動
• プランキャッシュに異なるプランが存在
• 特定のパラメータ値で著しく遅い

解決策の選択

優先順位	解決策	条件
1	PSP Optimization	SQL Server 2022以降
2	OPTIMIZE FOR value	特定の値が頻繁
3	OPTIMIZE FOR UNKNOWN	均等分散
4	RECOMPILE	低頻度実行

重要な注意点

• ❌ DBCC FREEPROCCACHEは一時的対処のみ
• ❌ すべてのクエリにRECOMPILEは不要
• ✅ データ分布と実行パターンを理解する
• ✅ Query Storeで継続的に監視

参考リンク

• Microsoft Learn: Parameter Sensitivity
• Microsoft Learn: Parameter Sensitive Plan Optimization
• Troubleshoot Parameter Sniffing Issues