05.4.2.2 统计信息与参数嗅探
所属:全栈开发面试题库 / 05 数据库与存储 / 05.4 SQL Server / 05.4.2 T-SQL 与执行计划
覆盖范围
- 统计信息构成:直方图、密度向量、列长度
- 自动/手动更新、采样比例、增量统计与筛选统计
- 成本模型与行数估算、CE 版本
- 参数嗅探现象、局部变量差异与排查
- RECOMPILE、OPTIMIZE FOR、计划指南、Query Store 强制计划
- 计划缓存污染、参数化、典型抖动案例与跨库对照
高频题速览
| 编号 | 题目 | 难度 | 频率 |
|---|---|---|---|
| 05.4.2.2-001 | 统计信息包含什么 | 进阶 | ★★★★☆ |
| 05.4.2.2-004 | 统计过期对计划的影响 | 高级 | ★★★★★ |
| 05.4.2.2-005 | 参数嗅探现象与排查 | 高级 | ★★★★★ |
| 05.4.2.2-008 | RECOMPILE / OPTIMIZE FOR | 高级 | ★★★★☆ |
| 05.4.2.2-010 | Query Store 与强制计划 | 高级 | ★★★★☆ |
| 05.4.2.2-014 | 查看统计信息与更新时间 | 进阶 | ★★★★☆ |
| 05.4.2.2-018 | 计划缓存污染与参数化 | 高级 | ★★★☆☆ |
| 05.4.2.2-019 | 存过程偶发慢案例 | 高级 | ★★★★☆ |
统计信息 → 计划决策
列值分布:直方图(高频键区间)+ 密度向量(重复率)+ 长度统计
→ 优化器估算选择性与行数 → 选择访问路径/JOIN 算法/内存授予
数据变化 → 统计过期 → 估算偏差 → 选错计划
参数嗅探 → 首次编译的参数值定型计划 → 其他参数复用差计划
05.4.2.2-001 统计信息包含哪些内容?
频率 ★★★★☆ | 难度 进阶
一句话结论
统计信息描述键值分布,主要由直方图(区间高频值与行数)、密度向量(重复率/选择性)和列长度统计组成,供优化器估算行数。
概念解释
- 直方图(Histogram):按键列采样的区间,记录每个区间的行数与重复值。
- 密度向量(Density):列组合的重复密度,用于 JOIN/分组估算。
- 长度统计:平均列长度等,影响行宽与 IO 成本估算。
原理流程
- 优化器用谓词匹配直方图区间,估算满足条件的行数与选择性。
代码 / 命令
-- 查看某表统计信息及其更新时间
SELECT s.name AS stats_name,
STATS_DATE(s.object_id, s.stats_id) AS last_updated
FROM sys.stats s
WHERE s.object_id = OBJECT_ID('dbo.Orders');
-- 详细直方图与密度
DBCC SHOW_STATISTICS ('dbo.Orders', 'IX_Orders_Status');
常见误区
- 误区:统计信息就是索引列表。正解:统计是分布数据,可独立于索引存在。
- 误区:直方图覆盖所有值。正解:它是采样区间表示,极端值未必逐条列出。
追问
- 问:统计信息按列还是按表?答:可按单列/多列组合,索引自动生成对应统计。
- 问:密度低(重复少)说明什么?答:选择性高,等值过滤收敛性好。
05.4.2.2-002 统计信息什么时候自动更新?
频率 ★★★☆☆ | 难度 进阶
一句话结论
表数据变更达到阈值后自动触发统计更新;阈值随行规模增大而比例降低,大表可能长时间不触发自动更新。
概念解释
- 更新阈值:基于行数变化百分比与固定行数的组合规则。
- 自动更新:查询编译时发现统计过旧,可能先更新再编译(阻塞或异步视配置)。
- 大批量增删后统计容易滞后。
原理流程
行数变化超阈值 → 标记统计过期 → 编译时更新统计 → 重新估算
代码 / 命令
-- 查看自动统计更新选项
SELECT name, auto_update_statistics, auto_update_statistics_async
FROM sys.databases WHERE name = N'DemoDB';
常见误区
- 误区:插入一行就更新一次统计。正解:达到阈值才更新,避免频繁采样开销。
- 误区:小表和大表用同一阈值比例。正解:阈值规则随表规模变化,大表相对更难触发。
追问
- 问:统计更新会阻塞查询吗?答:同步更新在编译时等待;异步更新(2016+ 选项)可先旧计划执行、后台更新。
- 问:怎么强制刷新?答:
UPDATE STATISTICS或重建索引。
05.4.2.2-003 UPDATE STATISTICS 怎么用?采样比例怎么选?
频率 ★★★☆☆ | 难度 进阶
一句话结论
UPDATE STATISTICS 手动刷新指定表/索引/列的统计;可指定全量扫描或采样比例,大表平衡准确性与开销。
概念解释
- 全量扫描:最准确,大表成本高。
- 采样比例:按比例读取页估算,速度快,分布均匀时足够准确。
- 增量统计:只合并新增变化(2014+ 特性,需按版本核对)。
原理流程
UPDATE STATISTICS dbo.Orders; -- 全表所有统计
UPDATE STATISTICS dbo.Orders IX_Orders_Status; -- 单个统计
UPDATE STATISTICS dbo.Orders WITH (SAMPLE 20 PERCENT); -- 采样
代码 / 命令
-- 查看统计最后更新时间,判断是否需要刷新
SELECT OBJECT_NAME(object_id) AS tbl, name,
STATS_DATE(object_id, stats_id) AS last_updated
FROM sys.stats WHERE object_id = OBJECT_ID('dbo.Orders');
常见误区
- 误区:统计越新计划一定越好。正解:采样偏差或数据倾斜下,新统计也可能估算不准。
- 误区:大表全量更新统计无成本。正解:全表扫描占用 IO 与时间,应低峰执行。
追问
- 问:重建索引会更新统计吗?答:会;重组不一定,必要时显式更新。
- 问:采样比例越低越好吗?答:过低分布估计偏差增大,按数据均匀度选择。
05.4.2.2-004 统计信息过期为什么会导致选错索引?
频率 ★★★★★ | 难度 高级
一句话结论
优化器按过期分布估算选择性:低估扫描成本会选全扫,高估收敛性会选低区分度索引;更新统计后计划往往恢复正常。
概念解释
- 估算偏差:实际某状态占绝大多数,直方图仍按均匀分布估算。
- 错误决策:成本模型选错 JOIN 顺序、索引或算法。
- 典型表现:计划走全表扫描,或回表大量行仍选 Lookup。
原理流程
大批量增删 → 分布改变 → 统计未更新
→ 估算行数失真 → 成本误判 → 差计划
代码 / 命令
-- 对比估算与实际行数后刷新统计
UPDATE STATISTICS dbo.Orders;
-- 重新执行并对比计划
常见误区
- 误区:计划变差一定是缺索引。正解:先核对统计时间与行数偏差,再谈加索引。
- 误区:刷新统计后差计划立刻消失。正解:参数嗅探缓存的旧计划可能仍被复用,需清缓存或强制重编译。
追问
- 问:怎么判断根因是统计还是索引?答:更新统计后复测;计划恢复即统计问题,仍差再评估索引。
- 问:倾斜数据在统计上怎么体现?答:高频值集中在少数直方图区间,平均值不代表分布。
05.4.2.2-005 什么是参数嗅探(Parameter Sniffing)?
频率 ★★★★★ | 难度 高级
一句话结论
存储过程/参数化语句首次编译时按当时参数值生成计划,后续不同数据分布的参数复用该计划;计划对某类参数最优,对另一类参数很差。
概念解释
- 嗅探:优化器编译时"看到"具体参数值,据此估算。
- 复用:缓存计划被后续不同参数复用。
- 典型场景:热参数命中行数少、冷参数命中行数多,共用一个嵌套循环计划。
原理流程
首次用参数 A(少量行)编译 → Nested Loops 计划入缓存
后续参数 B(大量行)复用该计划 → 逐行回表,极慢
代码 / 命令
-- 存储过程参数化调用
CREATE PROCEDURE dbo.GetOrders @Status INT AS
BEGIN
SELECT * FROM dbo.Orders WHERE Status = @Status;
END;
常见误区
- 误区:参数嗅探是 bug。正解:它是计划复用的正常副作用,问题在于单一计划不适应多分布参数。
- 误区:把参数换成局部变量就能解决。正解:局部变量编译时无具体值,估算走固定假设,可能更差(见 05.4.2.2-006)。
追问
- 问:哪些语句容易触发?答:带参数的存储过程、参数化 SQL、复用缓存计划的批处理。
- 问:症状是什么?答:同一语句不同参数性能差异巨大,偶发慢、计划来回抖动。
05.4.2.2-006 参数和局部变量在编译估算上有什么区别?
频率 ★★★☆☆ | 难度 高级
一句话结论
参数在编译时携带具体值可供嗅探估算;局部变量编译时无具体值,优化器按固定假设(如平均密度)估算,往往偏差更大。
概念解释
- 参数:编译时可见值,直方图按值定位区间。
- 局部变量:运行时才赋值,编译期只能用密度估算。
- 把参数赋给局部变量再查询,会丢失嗅探信息。
原理流程
参数 @Status = 1 → 编译期按 1 的直方图估算
局部变量 @s = @Status → 编译期无值 → 按密度平均估算
代码 / 命令
-- 直接用参数:可嗅探
CREATE PROC dbo.P1 @Status INT AS
SELECT * FROM dbo.Orders WHERE Status = @Status;
-- 改局部变量:丢失参数嗅探
CREATE PROC dbo.P2 @Status INT AS
BEGIN
DECLARE @s INT = @Status;
SELECT * FROM dbo.Orders WHERE Status = @s;
END;
常见误区
- 误区:局部变量写法能规避嗅探问题且计划更稳。正解:它只是让估算更粗,不保证更优。
- 误区:参数嗅探只发生在存储过程。正解:参数化即席 SQL 同样存在。
追问
- 问:
OPTIMIZE FOR UNKNOWN模拟哪种估算?答:按密度/平均值估算,类似局部变量效果。 - 问:怎么让编译期使用指定值估算?答:
OPTIMIZE FOR (@var = value)。
05.4.2.2-007 参数嗅探怎么排查?
频率 ★★★★☆ | 难度 高级
一句话结论
对比不同参数值的实际计划与行数偏差,查看缓存计划是否被复用,确认是否首次参数定型了不通用的计划。
概念解释
- 排查步骤:抓慢语句 → 分别用好/坏参数执行实际计划 → 对比算子与行数 → 检查缓存句柄与复用次数。
- 证据:同一语句不同参数计划差异大;估算与实际行数偏差集中在某分支。
原理流程
慢参数执行计划 → 行数/算子
快参数执行计划 → 对比是否同一缓存计划
缓存复用计数 → 确认计划被复用
代码 / 命令
-- 查看缓存中该语句的计划与复用次数
SELECT cp.usecounts, cp.plan_handle, t.text
FROM sys.dm_exec_cached_plans cp
OUTER APPLY sys.dm_exec_sql_text(cp.plan_handle) t
WHERE t.text LIKE '%Orders%';
常见误区
- 误区:偶发慢就是服务器压力问题。正解:先排除计划抖动与参数嗅探,再看资源。
- 误区:每个参数都应有独立计划。正解:计划复用有编译成本收益,问题在于差计划被复用。
追问
- 问:Query Store 怎么辅助排查?答:按查询看不同计划的耗时与行数,识别计划回归。
- 问:怎么确认是嗅探而不是统计过期?答:刷新统计后问题依旧,且不同参数计划差异显著。
05.4.2.2-008 处理参数嗅探有哪些手段?
频率 ★★★★☆ | 难度 高级
一句话结论
常用手段:语句级 RECOMPILE 按每次参数重编译、OPTIMIZE FOR 指定估算值或 UNKNOWN、拆分查询,长期用 Query Store 固定好计划。
概念解释
| 手段 | 作用 | 代价 |
|---|---|---|
OPTION (RECOMPILE) |
每次执行按当前参数编译 | 编译开销增加 |
OPTIMIZE FOR (@p = v) |
按指定值估算 | 绑定特定场景 |
OPTIMIZE FOR UNKNOWN |
按密度平均估算 | 放弃嗅探精度 |
| 拆分/改写 | 把不同分布分支分开处理 | 代码复杂度上升 |
| QS 强制计划 | 库侧固定好计划 | 分布变化后需复查 |
原理流程
嗅探致差计划 → 按场景选重编译/固定估算/强制计划
代码 / 命令
-- 按当前参数重编译
SELECT * FROM dbo.Orders WHERE Status = @Status
OPTION (RECOMPILE);
-- 按指定值估算
SELECT * FROM dbo.Orders WHERE Status = @Status
OPTION (OPTIMIZE FOR (@Status = 1));
常见误区
- 误区:全存储过程加
WITH RECOMPILE是通用解法。正解:高频语句每次编译成本高,应只对问题语句用语句级提示。 - 误区:
OPTIMIZE FOR能适配所有参数分布。正解:它固定估算假设,混合分布需评估。
追问
- 问:重编译能根治嗅探吗?答:消除了跨参数复用,但每次编译消耗 CPU;低频问题语句适用。
- 问:计划指南与 QS 强制计划怎么选?答:优先 Query Store,可观测且无需改语句。
05.4.2.2-009 计划指南是什么?
频率 ★★☆☆☆ | 难度 高级
一句话结论
计划指南在库侧为指定语句附加提示或固定计划,不改语句文本;现代环境优先用 Query Store 完成同类目标。
概念解释
- 对象/SQL/模板指南:匹配存储过程语句、SQL 文本或参数化模板。
- 作用:对无法修改源码的语句绑定提示/计划。
原理流程
语句到达 → 匹配计划指南 → 应用附加提示/固定计划
代码 / 命令
-- 创建计划指南(示例,语句文本需与实际完全匹配)
EXEC sys.sp_create_plan_guide
@name = N'Guide_Orders',
@stmt = N'SELECT * FROM dbo.Orders WHERE Status = @Status',
@type = N'SQL',
@module_batch = NULL,
@hints = N'OPTION (RECOMPILE)';
常见误区
- 误区:计划指南对语句文本容错匹配。正解:批处理文本需精确匹配,格式化差异会导致不命中。
- 误区:计划指南比 QS 更易维护。正解:指南脆弱且不可观测,QS 是现代推荐。
追问
- 问:计划指南会随版本升级失效吗?答:语句/计划变化后需复查维护。
- 问:哪些场景还在用指南?答:无法改源码的遗留系统临时纠正计划。
05.4.2.2-010 Query Store 是什么?能做什么?
频率 ★★★★☆ | 难度 高级
一句话结论
Query Store 持续记录查询、计划与运行时统计,可回放历史计划、识别计划回归、强制固定好计划,是排查计划抖动的核心工具。
概念解释
- 数据存储:查询文本、计划、执行次数、耗时/IO/行数统计。
- 能力:找 Top 慢查询、对比同一查询多计划、强制(force)计划、解除强制。
- 启用:数据库级别开启,设置保留时间与查询捕获模式。
原理流程
执行 → QS 采集计划与统计 → 识别回归 → 强制好计划 → 复查解除
代码 / 命令
-- 启用 Query Store
ALTER DATABASE DemoDB SET QUERY_STORE = ON (
OPERATION_MODE = READ_WRITE,
MAX_STORAGE_SIZE_MB = 1000,
QUERY_CAPTURE_MODE = AUTO);
-- 强制/解除强制计划
EXEC sys.sp_query_store_force_plan @query_id = 123, @plan_id = 456;
EXEC sys.sp_query_store_unforce_plan @query_id = 123, @plan_id = 456;
常见误区
- 误区:QS 开启后自动优化查询。正解:它只采集与辅助决策,优化仍需人工/索引/改写。
- 误区:强制计划永久有效。正解:数据分布变化后需复查,旧计划可能变差。
追问
- 问:QS 占多少额外存储?答:按配置上限管理,需监控大小与清理策略。
- 问:QS 能看到被清除的历史计划吗?答:保留期内可见,超期清理后不可追溯。
05.4.2.2-011 索引和统计信息是什么关系?
频率 ★★★☆☆ | 难度 进阶
一句话结论
建索引自动生成对应统计;优化器同时依赖索引路径与统计估算;删除索引其统计一并移除。
概念解释
- 自动统计:索引键列上自动创建统计对象。
- 手动统计:可为非索引列/多列组合补充统计。
- 统计不准会让已有索引不被选用。
原理流程
建索引 → 生成统计 → 优化器按统计选是否走该索引
代码 / 命令
-- 区分索引统计与手动统计
SELECT name, auto_created, user_created
FROM sys.stats WHERE object_id = OBJECT_ID('dbo.Orders');
常见误区
- 误区:有索引就有准确统计。正解:索引存在不代表统计新鲜或覆盖所有谓词列。
- 误区:非索引列无法有统计。正解:可手动创建列/多列统计辅助估算。
追问
- 问:联合索引的统计覆盖哪些列?答:前缀组合统计,非前缀列估算可能依赖密度。
- 问:多列统计什么时候有用?答:列间存在关联、单列估算偏差大时。
05.4.2.2-012 什么是多列统计和筛选统计?
频率 ★★☆☆☆ | 难度 高级
一句话结论
多列统计记录列组合的密度与相关性;筛选统计只针对筛选索引/谓词子集建模,提升子集查询估算准确性。
概念解释
- 多列统计:描述列组合重复率,辅助 JOIN/GROUP BY 估算。
- 筛选统计:配合筛选索引,只为满足谓词的行集建直方图。
原理流程
普通统计:全表分布
筛选统计:WHERE Status=0 子集分布,估算更贴近子集查询
代码 / 命令
-- 多列统计
CREATE STATISTICS Stat_Customer_Time ON dbo.Orders (CustomerId, CreateTime);
-- 筛选统计
CREATE STATISTICS Stat_Open_Amount ON dbo.Orders (Amount)
WHERE Status = 0;
常见误区
- 误区:多列统计会建模列间相关关系。正解:主要记录组合密度,不建模完整相关性。
- 误区:筛选统计可以服务全表查询。正解:只覆盖筛选子集。
追问
- 问:筛选统计与筛选索引配套吗?答:常配套使用,子集查询估算与路径都更准。
- 问:统计对象有数量上限顾虑吗?答:过多统计增加维护成本,按高频谓词建立。
05.4.2.2-013 密度、选择性和基数估算版本(CE)是什么?
频率 ★★★☆☆ | 难度 高级
一句话结论
密度是列值重复程度的度量,选择性由密度推导;基数估算(CE)模型决定行数推算方式,不同版本 CE 对相同统计可能给出不同估算。
概念解释
- 密度:
1 / 不同值数,越低越唯一。 - 选择性:满足谓词的行比例。
- CE 版本:优化器估算模型世代(如 70/120/130/140/150 等,以实例版本与兼容级别为准),不同版本假设不同。
原理流程
直方图/密度 → CE 模型 → 估算满足谓词行数 → 成本
代码 / 命令
-- 查看数据库兼容级别(影响 CE 版本选择)
SELECT name, compatibility_level FROM sys.databases WHERE name = N'DemoDB';
常见误区
- 误区:统计相同,所有版本估算结果一致。正解:CE 模型假设不同,估算可差异明显。
- 误区:新版 CE 一定更差或更好。正解:需按 workload 实测,版本回归可在语句级切换旧 CE 评估。
追问
- 问:怎么对比新旧 CE?答:语句级
USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION')(按版本核对)复测。 - 问:选择性低的列为什么索引价值有限?答:过滤后仍返回大量行,回表/扫描成本高。
05.4.2.2-014 怎么查看统计内容与最后更新时间?
频率 ★★★★☆ | 难度 进阶
一句话结论
sys.stats 列出统计对象,STATS_DATE 取更新时间,DBCC SHOW_STATISTICS 查看直方图与密度详情。
概念解释
- 更新时间:判断是否过期的第一依据。
- 直方图:核对谓词值落在哪个区间、估算行数是否合理。
原理流程
SELECT name, STATS_DATE(object_id, stats_id) AS last_updated
FROM sys.stats WHERE object_id = OBJECT_ID('dbo.Orders');
DBCC SHOW_STATISTICS ('dbo.Orders', 'IX_Orders_Status');
代码 / 命令
-- SHOW_STATISTICS 输出三部分:头部、密度、直方图
DBCC SHOW_STATISTICS ('dbo.Orders', 'IX_Orders_Status') WITH HISTOGRAM;
常见误区
- 误区:
STATS_DATE返回的是表更新时间。正解:它返回该统计对象最后更新时间。 - 误区:直方图条目越多数值越准。正解:条目数有限,尾部区间靠密度估算。
追问
- 问:怎么看某值的估算行数?答:在直方图定位区间,结合密度与区间行数推算。
- 问:统计多久更新一次合适?答:按数据变化频率与慢查询表现,低峰周期刷新。
05.4.2.2-015 大表统计采样与增量统计要注意什么?
频率 ★★☆☆☆ | 难度 高级
一句话结论
大表全量采样成本高,默认采样估算分布;增量统计只合并新增变化以降低刷新成本,适用条件与版本需核对。
概念解释
- 采样页:随机读取部分索引页估算分布。
- 增量统计:在已有统计基础上追加新数据统计,减少全表扫描。
- 分布剧烈变化时增量结果可能不准,需全量重建。
原理流程
大表 → 采样统计(快,近似)→ 分布剧变时全量刷新(准,慢)
代码 / 命令
-- 采样比例更新
UPDATE STATISTICS dbo.BigTable WITH (SAMPLE 5 PERCENT);
-- 增量统计(特性支持情况下)
UPDATE STATISTICS dbo.BigTable WITH (INCREMENTAL = ON);
常见误区
- 误区:采样统计在倾斜表上也足够准。正解:热点值未被采样到时偏差放大。
- 误区:增量统计永远优于全量。正解:长期累积后需全量重建校正。
追问
- 问:采样页不足会怎样?答:直方图代表性差,估算偏差增大。
- 问:增量统计有版本限制吗?答:需按目标 SQL Server 版本文档核对(待确认具体版本边界)。
05.4.2.2-016 选择性差的谓词为什么常走全表扫描?
频率 ★★★☆☆ | 难度 进阶
一句话结论
谓词过滤后返回大部分行时,索引 Seek + 回表的成本高于顺序扫描;优化器按成本选择全扫。
概念解释
- 低选择性:满足条件的行占比高。
- 回表放大:非聚集索引逐行回表,行数多时成本超过全扫。
- 即使有索引,优化器也可能放弃。
原理流程
性别列(两值)→ 索引 Seek 命中 50% 行 → 逐行回表
成本 > 聚集索引扫描 → 选扫描
代码 / 命令
-- 高比例更新状态查询,索引未必划算
SELECT * FROM dbo.Orders WHERE IsDeleted = 0;
常见误区
- 误区:有索引就一定会被使用。正解:成本模型按选择性判断,低选择性别看全扫。
- 误区:强制索引一定更快。正解:低选择性下强制 Seek 回表更慢。
追问
- 问:低选择性别列怎么优化?答:联合高选择性列、筛选索引、覆盖索引或拆分冷热数据。
- 问:怎么估算选择性?答:
COUNT(DISTINCT col)/COUNT(*),结合直方图核对。
05.4.2.2-017 统计信息可以异步自动更新吗?
频率 ★★☆☆☆ | 难度 高级
一句话结论
可配置异步自动更新:查询先按旧计划执行,后台更新统计,避免编译时阻塞等待统计刷新(2016+ 特性,按版本核对)。
概念解释
- 同步更新:编译时发现过期,先更新统计再出计划,阻塞编译。
- 异步更新:后台刷新,当前查询用旧计划,后续编译用新统计。
原理流程
过期统计 → 同步:等待更新 → 新计划
→ 异步:旧计划先跑 + 后台更新
代码 / 命令
ALTER DATABASE DemoDB SET AUTO_UPDATE_STATISTICS_ASYNC ON;
常见误区
- 误区:异步更新立刻修正当前慢查询。正解:当前查询仍用旧计划,受益从下次编译开始。
- 误区:异步适用于所有库。正解:强一致计划要求的场景需评估同步行为。
追问
- 问:异步更新受哪个开关控制?答:数据库级
auto_update_statistics_async。 - 问:开启后还需要手动更新统计吗?答:大批量变更后仍建议低峰手动刷新核对。
05.4.2.2-018 计划缓存污染和参数化是怎么回事?
频率 ★★★☆☆ | 难度 高级
一句话结论
大量即席 SQL 文本各占一份缓存计划,污染计划缓存、挤占内存;参数化(简单/强制参数化)让相似语句共享计划。
概念解释
- 即席污染:字面量不同被视为不同语句,各自编译入缓存。
- 简单参数化:引擎对安全语句自动参数化。
- 强制参数化:数据库级把更多语句按参数化处理。
原理流程
文本 '...Status=1' 与 '...Status=2' → 未参数化 → 两份计划
参数化后 → 同一计划模板复用
代码 / 命令
-- 查看即席负载缓存占用
SELECT SUM(size_in_bytes) / 1024 / 1024 AS cache_mb, COUNT(*) AS plans
FROM sys.dm_exec_cached_plans;
-- 数据库级强制参数化(需评估风险)
ALTER DATABASE DemoDB SET PARAMETERIZATION FORCED;
常见误区
- 误区:强制参数化对所有语句安全。正解:个别语句参数化后估算/计划变化,需回归验证。
- 误区:清理缓存能解决污染。正解:根因是即席写法,应从应用层参数化。
追问
- 问:
optimize for ad hoc workloads是什么?答:即席 workload 优化,首次编译只存轻量计划 stub,重复执行再存完整计划。 - 问:预编译/参数化查询在客户端怎么做?答:使用参数化命令对象,避免拼接字面量。
05.4.2.2-019 典型案例:存储过程偶发慢,怎么定位是参数嗅探?
频率 ★★★★☆ | 难度 高级
一句话结论
同一存储过程不同参数时快时慢,慢参数计划走嵌套循环大量回表;对比快/慢参数计划与行数,确认首次编译计划被复用后,用重编译/固定计划/QS 处置。
概念解释
- 症状:过程整体成功率高,个别参数调用极慢;重启/清缓存后偶发恢复。
- 证据:慢参数实际行数远大于估算,计划与快参数不同或复用了快参数计划。
原理流程
抓慢过程 → 分别用好/坏参数跑实际计划 → 行数偏差 + 算子差异
→ 查缓存复用 → 处置:RECOMPILE / OPTIMIZE FOR / QS 强制
代码 / 命令
-- 用慢参数单独执行并开实际计划
EXEC dbo.GetOrders @Status = <慢参数>;
-- 用快参数对照
EXEC dbo.GetOrders @Status = <快参数>;
常见误区
- 误区:偶发慢是偶发服务器抖动。正解:先按参数与计划分组,识别稳定复现的坏分支。
- 误区:直接清缓存解决问题。正解:清缓存只暂时缓解,下次嗅探仍可能复现。
追问
- 问:坏计划被固定在缓存怎么办?答:按句柄清除或用 QS 强制好计划。
- 问:怎么防止同类问题复发?答:参数设计、语句重编译策略、QS 监控计划回归。
05.4.2.2-020 统计与计划稳定性相关的 DMV/计数器有哪些?
频率 ★★☆☆☆ | 难度 进阶
一句话结论
统计对象看 sys.stats,计划缓存看 sys.dm_exec_cached_plans 与 SQL 文本,重编译/计划缓存看性能计数器,运行统计看 Query Store。
概念解释
- 统计:
sys.stats、STATS_DATE、DBCC SHOW_STATISTICS。 - 缓存:
sys.dm_exec_cached_plans、sys.dm_exec_sql_text。 - 计数器:重编译次数、缓存命中/缺失。
原理流程
统计新鲜度 → sys.stats / STATS_DATE
计划复用 → cached_plans.usecounts
重编译频率 → 性能计数器
运行回归 → Query Store
代码 / 命令
-- 重编译计数器
SELECT counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%Compilations%' OR counter_name LIKE '%Recompilations%';
常见误区
- 误区:DMV 数据永久保留。正解:缓存与计数器自实例重启累计,需结合时间窗口。
- 误区:重编译计数高就一定有问题。正解:需对比执行次数,判断重编译比例。
追问
- 问:怎么找最占缓存的语句?答:按缓存大小与复用次数排序
cached_plans。 - 问:QS 与 DMV 计划信息互补吗?答:DMV 看当前缓存,QS 看历史与回归趋势。
05.4.2.2-021 SQL Server 统计机制与 MySQL、PostgreSQL 怎么对照?
频率 ★★★☆☆ | 难度 高级
一句话结论
三库都用采样统计支撑优化器:SQL Server 直方图+密度,MySQL InnoDB 采样基数,PG pg_statistic 存储 Most-Common-Value 与直方图;机制不同,目标都是行数估算。
概念解释
| 维度 | SQL Server | MySQL InnoDB | PostgreSQL |
|---|---|---|---|
| 统计位置 | sys.stats + 直方图/密度 | mysql.innodb_table_stats/index_stats | pg_statistic |
| 触发更新 | 阈值/手动/重建索引 | ANALYZE TABLE / 自动 | ANALYZE / autovacuum |
| 采样 | 页采样,可配比例 | 采样页估算基数 | 列采样,MCV+直方图 |
| 计划稳定性 | QS/计划指南/提示 | 执行计划绑定/索引提示 | 计划管理扩展较少 |
原理流程
- 对照主线:统计 → 估算选择性 → 成本模型 → 计划;采样偏差在三库都可能导致选错路径。
代码 / 命令
-- SQL Server
UPDATE STATISTICS dbo.Orders;
-- MySQL: ANALYZE TABLE Orders;
-- PG: ANALYZE Orders;
常见误区
- 误区:三库统计信息结构一一对应。正解:存储与采样算法不同,核对时按各自文档。
- 误区:MySQL 基数是精确值。正解:InnoDB 为采样估算,与 SQL Server 直方图思路类似但实现不同。
追问
- 问:PG 统计扩展更细的是什么?答:
extended statistics处理列间关联,对应 SQL Server 多列统计思路。 - 问:三库都有计划强制工具吗?答:SQL Server QS 最完善;MySQL/PG 主要靠索引提示与查询改写。
参考来源
- Microsoft Learn:统计信息:https://learn.microsoft.com/sql/relational-databases/statistics/statistics
- Microsoft Learn:参数嗅探:https://learn.microsoft.com/sql/relational-databases/performance/parameter-sniffing
- Microsoft Learn:Query Store:https://learn.microsoft.com/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store
- Microsoft Learn:基数估计:https://learn.microsoft.com/sql/relational-databases/performance/cardinality-estimation-sql-server