参数探查（Parameter Sniffing）是SQL Server默认优化行为，首次执行时基于实际参数值生成并缓存执行计划，后续复用该计划导致不同参数下性能严重劣化；它非bug，但引发执行计划错配、资源消耗剧增等典型抖动现象。
SQL Server 2019 中的参数探查（Parameter Sniffing）问题，本质是执行计划缓存复用时“用错了一个参数值生成的计划”，导致后续不同参数值的执行严重劣化。它不是 bug，但表现常像 bug：同一存储过程，有时秒出，有时卡死几十秒，且 SET ARITHABORT ON/OFF 或重编译后立刻变快——这基本就是参数探查在作祟。
怎么看是不是参数探查导致的性能抖动
关键不是猜，而是比对不同参数值下的实际执行计划和资源消耗：
- 用
sys.dm_exec_query_stats+sys.dm_exec_sql_text查最近缓存的计划，筛选出该存储过程的plan_handle，再用sys.dm_exec_query_plan提取 XML 计划，检查<parameterlist></parameterlist>中记录的嗅探值是否与当前慢查询的实际输入值差异巨大（比如缓存计划里是@ProductID=897（仅 2 行），而你正跑@ProductID=870（4688 行）） - 运行两次相同存储过程（不同参数），分别捕获
STATISTICS XML ON输出，直接对比两个执行计划：若一个走Index Seek+Key Lookup，另一个却走Clustered Index Scan，大概率就是参数探查引发的计划分歧 - 检查
sys.dm_exec_cached_plans中该计划的usecounts是否很高，但avg_logical_reads或avg_worker_time波动剧烈——说明复用计划在不同参数下代价悬殊
SQL Server 2019 中最实用的临时缓解手段
2019 引入了查询提示 OPTIMIZE FOR UNKNOWN 和数据库级配置 PARAMETER_SNIFFING = OFF，但它们不是万能解药，得看场景：
-
OPTIMIZE FOR UNKNOWN最适合参数分布极不均匀、且无法预判典型值的 OLTP 存储过程。加在语句末尾即可：WHERE ProductID = @ProductID OPTION (OPTIMIZE FOR (@ProductID UNKNOWN))。它让优化器忽略具体值，按统计直方图平均密度估算行数，避免极端偏差 - 数据库级别关闭参数探查（
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF）影响全局，慎用。它等价于每个查询都隐式加OPTIMIZE FOR UNKNOWN，可能让本该走索引的轻量查询退化为扫描——只建议在确认 90%+ 查询都受参数探查拖累时启用 - 避免滥用
WITH RECOMPILE：它每次执行都重编译，解决探查问题但引入 CPU 开销，尤其高并发下易成瓶颈；更适合低频、逻辑复杂、参数差异极大的报表类过程
为什么 sp_recompile 有时有效，有时无效
执行 sp_recompile 'usp_GetOrderDetail' 会清除该对象的全部缓存计划，下次调用时强制重新编译——但它只解决“当前缓存计划太差”的问题，不解决“下次又缓存一个更差计划”的根本矛盾：
- 如果第一次调用传入的是边缘值（如空字符串、最大 ID），新生成的计划仍会基于该值优化，后续正常参数进来照样慢
- 若存储过程中有嵌套调用或动态 SQL，
sp_recompile不会递归清理子计划，残留旧计划仍可能被复用 - 在 AG 只读副本上执行
sp_recompile无效，因为只读副本不维护可写缓存
真正要盯住的细节往往藏在统计信息和参数声明里
参数探查问题常和底层数据状态强耦合，光改查询逻辑不够：
- 确保相关表的统计信息是更新的：
UPDATE STATISTICS Sales.SalesOrderDetail WITH FULLSCAN, NORECOMPUTE。过时直方图会让优化器对“某参数值对应多少行”判断完全失真 - 检查存储过程参数声明长度是否远超实际业务需求，比如
@SearchTerm NVARCHAR(4000)传入 "abc"，优化器可能因类型宽泛放弃 SARGability，转而选低效计划 - 留意
ANSI_NULLS和QUOTED_IDENTIFIER设置：不同设置会产生独立缓存计划，若应用连接字符串里没统一配置，可能多个相似计划并存，加剧排查难度
