SQL Server 2022如何优化SQL存储过程参数嗅探-SQL-PHP中文网

sql server 2022 中参数敏感计划（psp）需满足非均匀统计分布、前三个谓词、完全优化、启用查询存储且兼容级别≥160等条件才能自动启用，否则仍需手动干预。

sql server 2022如何优化sql存储过程参数嗅探

SQL Server 2022 中参数嗅探不是要“消灭”，而是用 PSP（参数敏感计划）自动分流——但必须满足条件，否则仍得手动干预。

怎么确认你的查询是否启用了参数敏感计划（PSP）？

不是所有带参数的查询都能自动获得多个执行计划。PSP 有明确触发门槛：

列统计信息直方图必须显示**非均匀分布**（比如某字段 95% 值是 'A'，5% 是 'B'～'Z'）
优化器最多只对**前三个谓词**做 PSP 评估（WHERE 中多个 @param = col 时，只挑最敏感的三个）
必须是**完全优化**的计划（StatementOptmLevel="FULL"），带 OPTION (RECOMPILE) 或分布式查询不参与
查询需启用查询存储（QUERY_STORE ON）且处于 READ_WRITE 模式

查实际效果：运行慢查询后，查 sys.query_store_plan，看 is_forced_plan 和 query_plan_hash 是否出现多个不同哈希值；再结合 sys.dm_exec_query_stats 中相同 sql_handle 但不同 plan_handle 的记录数。

为什么加了 PSP 还是单计划？常见失效场景

PSP 不是开关一开就生效，很多情况下它根本不会被激活：

@param 类型和长度每次调用不一致（比如一次传 nvarchar(10)，一次传 nvarchar(50)），导致计划缓存分裂，PSP 失去比较基础
WHERE 条件里混用参数和字面量：WHERE Company = @Company AND Status = 'Active' —— 后者破坏谓词独立性，PSP 不识别
统计信息过期或缺失直方图（UPDATE STATISTICS 没跑，或采样率太低），优化器无法判断“哪些值范围会导出大结果集”
数据库兼容级别低于 160（SQL Server 2022 对应版本号），PSP 功能不可用

验证方式：在 SSMS 中执行 DBCC SHOW_STATISTICS('YourTable', 'YourIndex')，检查 Histogram 步骤数是否 ≥ 200，且 AVG_RANGE_ROWS 差异明显（比如从 1 跳到 10000）。

局部变量 + PSP 组合用法才真正稳

单独依赖 PSP 有盲区；局部变量能兜底，但写错等于没写：

声明和赋值必须在同一行：DECLARE @LocalCompany NVARCHAR(100) = @Company; —— 分两行（DECLARE + SET）可能被优化器绕过
类型长度必须严格一致：@Company 是 NVARCHAR(50)，就不能声明成 NVARCHAR(20)，否则隐式转换让索引失效
只屏蔽关键参数：如果 WHERE 有 @Company 和 @Region，两个都得用局部变量，漏一个就可能被穿透嗅探
PSP 仍可生效：局部变量写法不阻止 PSP，只是把“参数值不确定性”转移到变量上，PSP 依然能基于 @LocalCompany 的运行时值选择变体

示例正确结构：

CREATE PROCEDURE sp_get_sales 
    @Company NVARCHAR(100), 
    @Region CHAR(2)
AS
BEGIN
    DECLARE @LocalCompany NVARCHAR(100) = @Company;
    DECLARE @LocalRegion CHAR(2) = @Region;
<pre class="brush:php;toolbar:false;">SELECT SUM(SalesAmount) 
FROM SalesTable 
WHERE Company = @LocalCompany 
  AND Region = @LocalRegion
OPTION (OPTIMIZE FOR (@LocalCompany UNKNOWN)); -- 可选：给 PSP 加一层提示

END

OPTION (RECOMPILE) 还要不要加？加在哪？

2022 年 PSP 上线后，OPTION (RECOMPILE) 不是过时了，而是使用场景更聚焦：

只加在**具体慢语句末尾**，不是整个存储过程头，例如：SELECT ... WHERE id = @x OPTION (RECOMPILE);
适合参数跨度极大且执行频次低的场景：分页 OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY，@skip 从 10 到 1000000
避免加在 INSERT/UPDATE 的子查询里却漏掉主 DML 语句——子查询重编译了，主语句仍套旧计划
别用 WITH RECOMPILE 创建过程，那会让整个过程每次执行都重编译，CPU 翻倍

注意：PSP 和 OPTION (RECOMPILE) 互斥——加了后者，PSP 就不生效。二者选其一，别叠 Buff。

真正麻烦的不是选哪种方案，而是混合场景：一个存储过程里既有高频小结果集查询（适合 PSP），又有低频巨量扫描（必须 RECOMPILE），这时得拆成独立语句并分别处理。没人替你做这个判断，监控 sys.dm_exec_query_stats 中各语句的 last_logical_reads 和 last_elapsed_time 才是起点。