SQL Server 参数嗅探排查实战：从执行计划缓存到 Query Store 强制计划

dbaai · 昨天 07:48

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

您需要登录才可以下载或查看，没有账号？用户注册

×

SQL Server 参数嗅探排查实战：从执行计划缓存到 Query Store 强制计划

一、具体的问题

一个很典型的报障：订单列表查询上午点开 200 毫秒出头，下午三点以后变成 4 秒多，晚高峰偶尔冲到 8 秒。运维先重启应用，没用；重启 SQL Server 实例，立刻恢复正常，第二天下午照旧。

群里给了三个方案：给语句加 WITH RECOMPILE、每天定时跑一次 UPDATE STATISTICS、把内存从 64GB 加到 128GB。前两个都试了，都是短期有效——加 RECOMPILE 当天顺畅，第二天 CPU 反而比之前高；定时更新统计信息管用两天，之后又坏。

这类"清缓存就好、过一阵又坏"的现象，九成不是硬件不够，也不是索引建错，而是执行计划缓存里存着一份用错误的参数编译出来的计划。要修它，先得把现象拆开。

二、核心原理

第一件事要记牢：执行计划是按语句结构缓存，按首次编译时的实参生成。同一条语句（同一个句柄）第二次、第两千次执行，都会复用第一次编译出来的计划，参数值变了也不会自动重编译。这就是参数嗅探（Parameter Sniffing）。

参数嗅探本身是特性不是缺陷。编译时把实参当常量代入，优化器能借助统计信息算出更精确的基数估算，多数场景下这比"盲目猜平均行数"更准。只有当一个参数的实际结果集大小和编译时那个参数的差了几个数量级，复用才变成灾难。

要把三件事分清楚，否则一定会用错药：

参数嗅探：语句只有一句，缓存里只有一个计划，但这个计划是按上次编译那个参数选的。热值走 Seek、稀疏值也被迫走 Seek（或反过来），于是"时快时慢"。
计划缓存污染：动态 SQL 用字符串拼接，每个参数值都是一条新语句，缓存里堆满只执行过一次的计划，句柄爆炸、内存被吃。
计划回归：打 CU、改兼容级别、统计信息更新后，同一语句结构被重编译出更差的计划，表现为"某天开始整体变慢"。

判别工具是两个 DMV：sys.dm_exec_query_stats 给每个缓存计划的历史消耗（total_worker_time 看 CPU、total_logical_reads 看逻辑读、plan_generation_num 看重编译次数）；sys.dm_exec_query_plan 给计划 XML。

三个"救急手段"的边界必须清楚：

OPTION (RECOMPILE)：每次执行都重新编译，参数必然代入正确，代价是每次付编译 CPU。低频复杂语句可以用，高频语句加它等于给 CPU 加税。
OPTION (OPTIMIZE FOR (@p UNKNOWN))：按列的平均密度估算行数，不再被某个极端值绑架。它对倾斜数据也是赌，只是赌另一个方向。
OPTION (OPTIMIZE FOR (@p = 值))：把估算锁定在指定值上。只有"确实只有一个稳定热点参数"时才成立，否则是硬编码的新坑。

真正可持久、可回退的方案是 Query Store：它把每个计划的历史性能记录下来，允许你把好的计划强制住，也能看到强制是否失效。

三、实例参考（动手步骤）

步骤 0：确认现状与基线

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- 兼容级别决定优化器版本，是判断"计划回归"的前提
SELECT name, compatibility_level, is_query_store_on, is_parameterization_forced
FROM sys.databases WHERE name = DB_NAME();

复制代码

步骤 1：从缓存里抓出消耗最高的语句

SELECT TOP 20
qs.query_hash,
qs.plan_generation_num, -- >1 表示发生过重编译
qs.execution_count,
qs.total_worker_time / NULLIF(qs.execution_count,0) AS avg_cpu_us,
qs.total_logical_reads / NULLIF(qs.execution_count,0) AS avg_reads,
SUBSTRING(st.text, qs.statement_start_offset/2 + 1,
(CASE WHEN qs.statement_end_offset = -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2 + 1) AS stmt_text,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC;

复制代码

看两列：avg_reads 明显偏大的语句是嫌疑对象；同一 query_hash 只有一条记录、execution_count 很高，说明所有参数共用一个计划——这正是参数嗅探的土壤。

步骤 2：复现"同一语句、两个计划"的差异

DECLARE @cid int = 1; -- 覆盖 80% 数据的热点客户
SELECT COUNT(*) FROM dbo.Orders WHERE CustomerID = @cid;
DECLARE @cid2 int = 99999; -- 只覆盖几十行的稀疏客户
SELECT COUNT(*) FROM dbo.Orders WHERE CustomerID = @cid2;

复制代码

先跑第一条再跑第二条，第二条会复用第一条编译出的计划（此时 execution_count 变成 2）。盯着 STATISTICS IO 的输出：如果两句的 logical reads 完全一样、且都很大，说明稀疏值被"热点计划"绑架了，只能扫。

步骤 3：先分清是统计信息问题还是参数嗅探

-- 路径 A：全量更新统计后变好 → 偏向统计信息陈旧/采样不足
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;
-- 路径 B：加 RECOMPILE 后每次都变好 → 典型参数嗅探
SELECT COUNT(*) FROM dbo.Orders WHERE CustomerID = @cid2 OPTION (RECOMPILE);

复制代码

这两条路都能"治好"，代价完全不同。路径 A 是一劳永逸（本次统计有效期内），路径 B 是每次付编译成本。不先分清就动手，等于在赌。

步骤 4：按代价从低到高选择正解

-- ① 确认只有一个稳定热点参数时，锁定估算值
SELECT ... FROM dbo.Orders WHERE CustomerID = @cid
OPTION (OPTIMIZE FOR (@cid = 1));
-- ② 数据倾斜、参数不确定时，用平均密度估算，避免被极端值绑架
SELECT ... FROM dbo.Orders WHERE CustomerID = @cid
OPTION (OPTIMIZE FOR (@cid UNKNOWN));
-- ③ 低频、单次编译成本可忽略的复杂报表，每次重编译保正确
SELECT ... FROM dbo.Orders WHERE ... OPTION (RECOMPILE);

复制代码

步骤 5：用 Query Store 固定稳定计划（唯一可持久、可回退的方案）

-- 开启：先只写观察，不要立刻上强制计划
ALTER DATABASE SalesDB SET QUERY_STORE = ON
(OPERATION_MODE = READ_WRITE,
QUERY_CAPTURE_MODE = AUTO,
DATA_FLUSH_INTERVAL_SECONDS = 900,
INTERVAL_LENGTH_MINUTES = 30,
MAX_STORAGE_SIZE_MB = 1024, -- 不设上限会一直涨
STALE_QUERY_THRESHOLD_DAYS = 30);
-- 找"同一查询持续变差"的计划（用步骤 1 抓到的 query_hash）
SELECT q.query_id, p.plan_id, p.is_forced_plan,
p.avg_duration, p.avg_logical_io_reads
FROM sys.query_store_query q
JOIN sys.query_store_plan p ON q.query_id = p.query_id
WHERE q.query_hash = 0x8A3C1F0D2B7E4419
ORDER BY p.avg_duration;
-- 强制那个好计划
EXEC sys.sp_query_store_force_plan @query_id = 88, @plan_id = 4;
-- 关键：强制计划会"静默失效"，必须监控
SELECT q.query_id, p.plan_id, p.is_forced_plan,
p.force_failure_count, p.last_force_failure_reason_desc
FROM sys.query_store_plan p
JOIN sys.query_store_query q ON p.query_id = q.query_id
WHERE p.is_forced_plan = 1 AND p.force_failure_count > 0;

复制代码

步骤 6：前后对比

指标	治理前	治理后
订单查询 P95 耗时	4.8 s（下午波峰）	210 ms
该查询平均逻辑读	184,200	1,340
该 query_hash 在缓存中的计划数	1（被热点参数绑架）	1（Query Store 强制稳定计划）
每日重编译次数	260+	0
参数嗅探相关工单	2~3 起/周	0（连续 45 天）
计划回归响应时间	等用户报障，平均 2 天	Query Store 告警 15 分钟内定位

四、实操检查清单

先归类，再动手：确认是参数嗅探 / 计划缓存污染 / 计划回归，三类问题的解法互不通用。
证据链要齐：plan_generation_num（重编译次数）+ 两个不同参数的 STATISTICS IO 对比 + 该 query_hash 在缓存中的计划条数。
动态 SQL 一律改成 sp_executesql 传参，拼接串会让同一逻辑产生无数句柄，任何 hint 都管不住。
OPTIMIZE FOR UNKNOWN 上线前，用真实分布的 3~5 个参数各跑一遍验证，它不是普适解。
OPTION (RECOMPILE) 只给低频、编译成本可忽略的语句用，高频语句加它会明显抬高 CPU。
Query Store 先以 READ_WRITE 观察 1~2 天再考虑强制计划；MAX_STORAGE_SIZE_MB 必须设。
强制计划后必须持续监控 force_failure_count 与 last_force_failure_reason_desc。
每次打 CU 或改兼容级别后，重跑一遍 Query Store 里的 TOP 回归查询，确认没有被新优化器推翻。
统计信息维护是前置条件：统计不准，拿到正确参数也算不出正确行数。
留一份计划变更时间线，把强制记录与发布窗口对齐，事后复盘才有据可查。

几个容易踩的坑

一、DBCC FREEPROCCACHE 之后的"变快"是假象。 它只是让所有语句重新编译一次，把"这次执行的参数"变成新计划依据；换成另一个参数照样坏。更麻烦的是它会造成全库硬解析风暴，CPU 尖刺往往比原问题更严重。如果"清缓存就好"反复出现，说明根因一次都没被碰到。

二、plan_generation_num > 1 不等于有参数嗅探。 它只说明发生过重编译，原因可能是统计更新、索引重建、schema 修改、SET 选项变化，OPTION (RECOMPILE) 每次执行也会 +1。这个数字是线索，不是结论。

三、OPTIMIZE FOR UNKNOWN 也会算错。 它按列的平均密度估行数，遇到 80% 数据落在单一值的倾斜分布时，估出来的行数既不是热点也不是稀疏，可能同时选错 Seek 和 Scan。它只是"更稳定的赌注"。

四、Query Store 强制计划会静默失效。 目标计划涉及的表结构变更、索引被删，或优化器重新生成后计划无法复用时，is_forced_plan 仍然是 1，但引擎实际已回落成普通计划，只把失败次数记进 force_failure_count。不看这个字段，会误以为"强制了就好了"。

五、拼接 SQL 里的 hint 只能管住"这一条拼接结果"。 值不同即语句文本不同，句柄、query_hash 都不同，写 OPTION (RECOMPILE) 语法上有效，但因为每条语句本来只跑一次、编译一次，等于没起到固定计划的作用。改成 sp_executesql 传参，让同一结构共用一个计划，hint 才有作用面。

六、只看平均耗时会骗人。 一条查询 24 小时里 23 小时是 200ms、1 小时是 8s，平均值可能只有 520ms，看起来"还行"。要按 Query Store 的 INTERVAL_LENGTH_MINUTES 分段看 avg_duration 的波动，而不是看单条汇总值。

[开发应用] SQL Server 参数嗅探排查实战：从执行计划缓存到 Query Store 强制计划

[开发应用] SQL Server 参数嗅探排查实战：从执行计划缓存到 Query Store 强制计划

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

账号		用户注册	自动登录	找回密码
密码			用户注册