如何在SQL Server 2022中编写存储过程解决参数嗅探-SQL-PHP中文网

判断参数嗅探影响需查sys.dm_exec_procedure_stats：avg_elapsed_time与last_elapsed_time差异大、last_logical_reads超avg值3倍以上即为典型迹象；排除缓存清空抖动，勿依赖SSMS警告图标。

如何在sql server 2022中编写存储过程解决参数嗅探

SQL Server 2022 中参数嗅探问题不能靠“写得更漂亮”来解决，必须主动干预执行计划生成逻辑——否则同一个存储过程在不同参数下可能慢 10 倍以上。

怎么判断当前存储过程正受参数嗅探影响

别猜，直接查动态管理视图。关键指标是 avg_elapsed_time 和 last_elapsed_time 差距巨大（比如平均 50ms，最近一次 2800ms），同时 total_logical_reads 波动剧烈：

SELECT 
  OBJECT_NAME(object_id, database_id) AS SPName,
  d.total_elapsed_time / d.execution_count / 1000000.0 AS avg_elapsed_time,
  d.last_elapsed_time / 1000000.0 AS last_elapsed_time,
  d.total_logical_reads / d.execution_count AS avg_logical_reads,
  d.last_logical_reads AS last_logical_reads
FROM sys.dm_exec_procedure_stats d
WHERE d.database_id = DB_ID()
  AND d.execution_count > 5
  AND ABS(d.last_elapsed_time - (d.total_elapsed_time / d.execution_count)) > 1000000;

如果 last_logical_reads 是 avg_logical_reads 的 3 倍以上，大概率是参数嗅探导致计划复用错配
注意排除缓存刚清空后的首次编译抖动——用 DBCC FREEPROCCACHE 后只跑一次不具代表性
不要依赖 SSMS 执行计划界面里的“警告图标”，它常漏报；真实问题藏在统计差异里

SQL Server 2022 新增的 QUERY_PLAN_HINTS 怎么用

2022 引入了 QUERY_PLAN_HINTS 数据库范围配置，配合 PARAMETER_SENSITIVE_PLAN 提示，能自动为不同参数值缓存多个计划——但前提是查询本身满足“可敏感”条件：

必须是参数化查询（不能是拼接 SQL 或本地变量赋值后使用）
WHERE 条件列需有直方图且数据分布明显倾斜（如 StatusID=1 占 95%，StatusID=99 占 0.01%）
启用方式：先打开数据库级开关 ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN = ON;
再在查询中显式声明提示：OPTION (USE HINT('ENABLE_PARAMETER_SENSITIVE_PLAN'))
注意：该提示仅对单语句生效，不能加在存储过程定义头，必须加在具体 SELECT/UPDATE 末尾

OPTION (RECOMPILE) 和 OPTIMIZE FOR UNKNOWN 的取舍

老方案依然有效，但选错代价更高——2022 的查询优化器更激进，错误计划复用的惩罚更大：

Verbatik

Verbatik是一款用于文本转语音、语音克隆和多语言 AI 配音的在线工具。

下载

OPTION (RECOMPILE)：适合低频调用（
OPTION (OPTIMIZE FOR UNKNOWN)：适合高频调用（>100 次/分钟）、参数分布虽不均但存在“典型值”的场景；它会让优化器忽略实际参数，按统计直方图密度中位数估算行数
绝对避免在存储过程开头加 WITH RECOMPILE——这会强制整个过程每次重编译，连变量声明、临时表创建都重复解析，开销远超收益
真正要重编译的，只是那条病灶 SQL，例如：SELECT * FROM Orders WHERE StatusID = @status OPTION (RECOMPILE);

为什么加了提示还是没效果

常见失效原因不是语法错，而是底层条件不满足：

参数被赋值给本地变量后再用于 WHERE（如 DECLARE @x INT = @input; WHERE col = @x），优化器彻底失去嗅探能力，PARAMETER_SENSITIVE_PLAN 直接退化为 OPTIMIZE FOR UNKNOWN
查询中混用 NOLOCK 和参数化条件，某些版本下会抑制敏感计划生成
目标列缺少统计信息更新——运行 UPDATE STATISTICS dbo.Orders WITH FULLSCAN; 再试
数据库兼容级别低于 160（SQL Server 2022 默认为 160），检查 SELECT compatibility_level FROM sys.databases WHERE name = DB_NAME();

参数敏感计划不是银弹，它依赖统计质量、查询结构和明确提示三者共存。最容易被忽略的是：你看到的“执行计划变了”，未必是敏感计划生效，很可能是优化器因统计过期而被迫重新估算——先确保统计新鲜，再谈智能计划。