SQL Server 參數嗅探問題：動態 SQL 部分參數化快速解決方案

參數嗅探本質

重用其他參數生成的執行計劃，導致當前參數採用非最優計劃。典型觸發：參數化預存程序 + 資料分布不均。

重現場景建構

CREATE TABLE ParameterSniffProblem (
    Id INT IDENTITY(1,1), CustomerId INT, OrderId INT, 
    OrderStatus INT, CreateDate DATETIME, Remark VARCHAR(200)
);

-- 正常分布：50萬筆，CustomerId 0~9999 均勻
DECLARE @I INT = 0;
WHILE @I < 500000 BEGIN
    INSERT ParameterSniffProblem VALUES (@I % 10000, @I, RAND()*10, GETDATE()-RAND()*100, NEWID());
    SET @I += 1;
END

-- 偏態資料：CustomerId=6666 佔 10 萬筆
INSERT ParameterSniffProblem VALUES (6666, RAND()*100000, 1, GETDATE()-RAND()*100, NEWID()) GO 100000;

CREATE CLUSTERED INDEX IDX_CreateDate ON ParameterSniffProblem(CreateDate);
CREATE INDEX IDX_CustomerId ON ParameterSniffProblem(CustomerId);

參數化預存程序（有嗅探風險）

CREATE PROCEDURE ParameterSniffTest
    @CustomerId INT, @Status INT, @FromDate DATETIME, @ToDate DATETIME
AS
BEGIN
    DECLARE @Sql NVARCHAR(MAX) = N'SELECT * FROM ParameterSniffProblem WHERE 1=1';
    IF @CustomerId IS NOT NULL SET @Sql += N' AND CustomerId=@CustomerId';
    IF @Status IS NOT NULL SET @Sql += N' AND OrderStatus=@Status';
    IF @FromDate IS NOT NULL SET @Sql += N' AND CreateDate>=@FromDate';
    IF @ToDate IS NOT NULL SET @Sql += N' AND CreateDate<=@ToDate';
    
    EXEC Sp_ExecuteSql @Sql, 
        N'@CustomerId INT, @Status INT, @FromDate DATETIME, @ToDate DATETIME',
        @CustomerId, @Status, @FromDate, @ToDate;
END;

問題演示

1. 首次執行 @CustomerId=100（正常分布）→ Index Seek，計劃緩存
2. 再執行 @CustomerId=6666（偏態分布）→ 重用上述計劃，仍走 Index Seek，實際需 Table Scan 更優
3. DBCC FREEPROCCACHE 後重跑 6666 → 正確選擇 Table Scan

解決方案：針對性參數化

將易產生嗅探的參數（如 CustomerId）直接拼接入 SQL 字串，其他參數保持參數化：

IF @CustomerId IS NOT NULL 
    SET @Sql += N' AND CustomerId=' + CAST(@CustomerId AS NVARCHAR(20));
-- 其他參數維持參數化
IF @Status IS NOT NULL SET @Sql += N' AND OrderStatus=@Status';

效果驗證

1. @CustomerId=1 → Index Seek，計劃緩存
2. @CustomerId=6666 → 觸發重編譯，正確選 Table Scan
3. 同一 CustomerId 不同時間參數 → 重用計劃，避免重編譯開銷

方案優缺點權衡

其他解決方案對比

• OPTION (RECOMPILE)：每次重編譯，CPU 開銷大
• OPTIMIZE FOR：固定優化值，偏態動態變化無效
• 區域變數：優化器用密度估算，不精準
• Plan Guide：維護成本高，靜態偏態適用

總結

部分參數化（嗅探參數拼接、其餘參數化）在開發效率、執行效能、計劃重用三者間取得實用平衡。核心原則：以最小代價解決問題，避免不合理計劃重用造成系統級效能災難。