罗罗编程学习记录
本页内容 展开查看

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 主要靠索引提示与查询改写。

参考来源