Query Store 实战：数据库性能调优与计划翻转排查指南

1. 为什么性能调优总是变成考古现场做数据库运维的人大概都有过这种体验线上某个查询突然变慢业务方催得急你连上实例想看看它以前跑得怎么样、执行计划长什么样、什么时候开始退化的——结果发现什么历史记录都没有。你只能看到现在这一瞬间的状态至于过去几小时、几天里这个查询经历了什么全靠猜。这就是传统性能排查最难受的地方。DMV动态管理视图给的都是瞬时快照你查的时候是什么样就是什么样查完就没了。想留证据得自己写定时任务去采样、落表、做对比工作量不小而且采样间隔一粗关键的那次计划翻转就漏掉了。Query Store 就是冲着这个痛点来的。它相当于给数据库装了一个飞行数据记录仪把查询、执行计划、运行时统计这些关键信息持续地存下来而且是以一种可以按时间段回溯的方式存。查询变慢了你可以直接问它这个语句过去一周用的都是哪个计划什么时候换的计划换之前和换之后的耗时差多少——这些问题它都能答。这篇就围绕 Query Store 展开把它是什么、内部怎么组织、怎么开、怎么用、有哪些坑按我实际折腾下来的顺序讲一遍。适合已经在用云上关系型数据库、并且开始被性能问题折磨的开发和运维同学。哪怕你之前完全没接触过看完也能自己动手把它跑起来。2. Query Store 到底在数据库里存了什么2.1 它不是日志而是一套带时间维度的统计仓库很多人第一次听到 Query Store会以为它是个日志系统类似慢查询日志那种。其实不是。慢查询日志是事件流一条一条追加你只能顺着时间读Query Store 是结构化仓库它把信息按维度组织好让你能聚合、能对比、能按时间窗口切片。它核心存三类东西查询文本语句本身的规范化形式把字面量参数化掉这样同一个模板的不同参数会被归到一条记录上。执行计划每个被编译出来的计划包括计划本身的 XML 和它的哈希值。运行时统计按时间区间聚合的执行次数、总耗时、平均耗时、逻辑读、物理读、内存授予、等待类型等等。关键在于按时间区间聚合这几个字。它默认把时间切成一个个小格子interval每个格子里的统计单独存一份。这样你就能看到这个查询在 14:00 到 15:00 这个窗口里平均耗时突然从 5ms 涨到了 800ms而不是只有一个笼统的总平均值。2.2 计划翻转Query Store 最想帮你抓的凶手性能退化里最经典的一类叫计划翻转Plan Regression。同一个查询SQL 没改数据没大改但某一天优化器突然选了个不一样的执行计划然后就从秒回变成了转圈。为什么会这样优化器选计划靠的是统计信息和成本估算。统计信息更新了、数据分布变了、参数嗅探到了不同的值都可能让它在某个时刻改主意。问题是改主意的那一刻往往没人盯着等业务报警时已经过去很久了。Query Store 的价值就在这里它把每个计划都留了档还标了每个计划第一次出现和最后一次使用的时间。你打开某个查询的详情能直接看到它历史上用过几个计划、每个计划各自的平均耗时是多少。哪个计划是好计划、哪个是坏计划一目了然。更进一步它还能让你强制某个查询走指定的计划Plan Forcing相当于手动把优化器的错误决定纠正过来而且不用改一行 SQL。2.3 三个核心目录视图的分工Query Store 的数据落在数据库自己的几个系统视图里日常排查主要跟这三个打交道视图作用典型用途sys.query_store_query查询的规范化记录找到目标查询的 query_idsys.query_store_plan每个执行计划看某查询用过哪些计划、计划哈希sys.query_store_runtime_stats运行时统计按时间窗口看耗时、读次数等指标它们之间靠 id 关联一个 query 对应多个 plan一个 plan 对应多个时间区间的 runtime stats。理解这个一对多的关系后面写查询就顺了。比如你想找最近一天平均耗时超过 1 秒的查询就是从 runtime_stats 出发按 query_id 聚合再 join 回 query 拿文本。注意这些视图里的时间是 UTC。做时间对比时记得换算否则你会对着凌晨三点的数据纳闷为什么业务高峰期没记录。3. 开启 Query Store几个必须想清楚的参数3.1 开启本身很简单难的是配参数开启命令就一行ALTER DATABASE [你的库名] SET QUERY_STORE ON;但真正影响后续体验的是配套的几个参数。默认值能用但不一定适合你的库。我一般会显式配一遍ALTER DATABASE [你的库名] SET QUERY_STORE ( OPERATION_MODE READ_WRITE, CLEANUP_POLICY (STALE_QUERY_THRESHOLD_DAYS 30), DATA_FLUSH_INTERVAL_SECONDS 900, INTERVAL_LENGTH_MINUTES 60, MAX_STORAGE_SIZE_MB 1024, QUERY_CAPTURE_MODE AUTO, SIZE_BASED_CLEANUP_MODE AUTO );逐个说下为什么这么配。OPERATION_MODE有三个值OFF、READ_WRITE、READ_ONLY。READ_ONLY 适合你想冻结当前数据做分析、又不想它继续增长的场景。日常用 READ_WRITE。CLEANUP_POLICY控制数据保留多久。默认 30 天对大多数库够用。如果你的库写入量极大、Query Store 空间吃紧可以缩短反过来如果要做长期的性能趋势分析可以拉长但要配合加大存储上限。DATA_FLUSH_INTERVAL_SECONDS是内存数据落盘的间隔默认 900 秒15 分钟。这意味着最近 15 分钟的数据可能还在内存里没落盘。如果你在排查一个刚刚发生的性能抖动可能查不到最新数据。想更实时可以调小比如 300 秒代价是写入压力略增。INTERVAL_LENGTH_MINUTES是统计聚合的时间粒度默认 60 分钟。这个值直接决定你能看到多细的时间曲线。设成 60你看到的是每小时一个点设成 15就是每 15 分钟一个点曲线更细但数据量翻几倍。排查突发抖动建议设小一点做长期趋势分析设大一点更省空间。MAX_STORAGE_SIZE_MB是硬上限。一旦写满行为取决于 SIZE_BASED_CLEANUP_MODE设成 AUTO 会自动清理最旧的数据腾地方设成 OFF 则直接停止收集新数据。生产环境强烈建议 AUTO否则某天你会发现 Query Store 悄悄罢工了。QUERY_CAPTURE_MODE决定哪些查询被记录。三个选项ALL所有查询都记最全但最占空间。AUTO只记有意义的查询过滤掉那些执行一次、耗时极短的噪音。这是默认值也是我推荐的起点。NONE停止捕获新查询只保留已有的。3.2 怎么判断配置合不合理配完不是就完事了得回头看它实际跑得怎么样。有个现成的视图SELECT actual_state_desc, readonly_reason, current_storage_size_mb, max_storage_size_mb, (current_storage_size_mb * 100.0 / max_storage_size_mb) AS used_pct FROM sys.database_query_store_options;重点看两个actual_state_desc是不是 READ_WRITE如果是 READ_ONLY 或 ERROR说明出问题了以及used_pct用了多少。如果长期在 90% 以上要么加大上限要么缩短保留天数要么把 INTERVAL_LENGTH_MINUTES 调大。readonly_reason这个字段特别有用它用位标志告诉你为什么变成了只读。比如值是 1 通常表示达到了存储上限。这个字段很多人不知道排查Query Store 怎么不记数据了时能省不少时间。3.3 一个容易被忽略的坑不是所有版本行为都一样Query Store 在不同部署形态下的默认行为和可用参数有差异。比如某些托管实例上默认就是开启的而且部分参数不可改。另外只读副本上的 Query Store 行为也跟主副本不同——副本上通常只读用来做分析可以但别指望它记录副本自己的负载。我的建议是上线前先在测试库把参数配一遍观察一周的存储增长曲线再决定生产怎么配。直接照搬别人的参数很可能因为你的库写入模式不同而翻车。4. 用 Query Store 定位问题从哪个查询慢到为什么慢4.1 先找最贵的查询别一上来就钻细节排查性能问题第一步永远是排序找出资源消耗最大的那几个。Query Store 自带了一批内置报表图形界面里点几下就能看。但如果你习惯用 SQL或者环境里没有图形工具直接查视图更灵活。下面这个查询找最近一天里总耗时最高的查询SELECT TOP 20 q.query_id, qt.query_sql_text, SUM(rs.count_executions) AS total_executions, SUM(rs.avg_duration * rs.count_executions) / 1000.0 AS total_duration_ms, AVG(rs.avg_duration) / 1000.0 AS avg_duration_ms FROM sys.query_store_runtime_stats rs JOIN sys.query_store_plan p ON rs.plan_id p.plan_id JOIN sys.query_store_query q ON p.query_id q.query_id JOIN sys.query_store_query_text qt ON q.query_text_id qt.query_text_id WHERE rs.last_execution_time DATEADD(DAY, -1, GETUTCDATE()) GROUP BY q.query_id, qt.query_sql_text ORDER BY total_duration_ms DESC;这里有个细节avg_duration的单位是微秒所以除以 1000 换成毫秒。很多人第一次看这个数会懵以为单位是毫秒结果算出来的耗时大了三个数量级。排序维度可以换。总耗时高说明它贡献大但平均耗时高说明它单次慢。两个视角都要看一个执行一百万次、每次 1ms 的查询总耗时也很吓人但它未必是问题查询可能只是调用太频繁。而一个每次 5 秒、一天只跑几次的查询平均耗时榜上很显眼但实际影响可能有限。结合业务频率一起判断别被单一指标带偏。4.2 锁定查询后看它的计划变迁史找到可疑查询下一步就是看它历史上用过哪些计划。这一步是 Query Store 的精华所在SELECT p.plan_id, p.is_forced_plan, p.force_failure_count, rs.count_executions, rs.avg_duration / 1000.0 AS avg_duration_ms, rs.first_execution_time, rs.last_execution_time FROM sys.query_store_plan p JOIN sys.query_store_runtime_stats rs ON p.plan_id rs.plan_id WHERE p.query_id 目标query_id ORDER BY rs.last_execution_time DESC;如果结果里出现多个 plan_id而且它们的 avg_duration 差异巨大——比如一个 3ms、一个 900ms——那基本可以确认发生了计划翻转。再看时间线好计划的 last_execution_time 是什么时候坏计划的 first_execution_time 是什么时候两者一交接退化点就找到了。这时候你就能回答那个灵魂问题它是什么时候开始变慢的——答案精确到分钟。4.3 强制计划把优化器的错误决定按回去确认了哪个是好计划之后可以直接强制EXEC sys.sp_query_store_force_plan query_id 目标query_id, plan_id 好计划的plan_id;执行完这个查询以后就走你指定的计划了。想取消强制EXEC sys.sp_query_store_unforce_plan query_id 目标query_id, plan_id 好计划的plan_id;强制计划不是一劳永逸的银弹用之前想清楚几件事。第一计划会失效。如果底层表结构变了、索引被删了、统计信息大改被强制的计划可能无法再执行这时会出现强制失败。sys.query_store_plan里的force_failure_count就是记录失败次数的要定期看。失败次数一直涨说明这个强制已经名存实亡得重新评估。第二强制的是计划不是结果。它不会改变查询语义只是让优化器别自作主张。所以不用担心数据正确性问题。第三别滥用。如果某个查询频繁翻转根因往往是统计信息维护不当或者参数嗅探问题强制计划只是止血。真正该做的是去修根因比如调整统计信息更新策略、改写查询、加合适的索引。强制计划更像是临时绷带不是手术。提示强制计划后建议观察至少一个完整的业务周期比如一整天确认耗时确实回到正常水平再决定是否长期保留。5. 那些文档里不写、但实际会踩的坑5.1 数据延迟你查到的可能不是现在前面提过 DATA_FLUSH_INTERVAL_SECONDS 默认 900 秒。这意味着内存里的最新数据要等最多 15 分钟才落盘而视图查询读的是落盘数据。所以刚发生的性能问题你可能查不到。有个折中办法需要立即落盘时可以手动触发一次。但更实际的做法是排查刚刚发生的问题时先结合实时 DMV 看当前状态等十几分钟后再用 Query Store 做历史对比。两者配合而不是只依赖一个。5.2 空间满了会静默罢工这是最坑的一点。Query Store 写满后如果 SIZE_BASED_CLEANUP_MODE 是 OFF它会停止收集但不会主动报错。你的监控如果不盯sys.database_query_store_options可能几天后才发现最近的数据怎么断了。我的做法是把current_storage_size_mb / max_storage_size_mb做成一个监控指标超过 80% 就告警。同时确保 SIZE_BASED_CLEANUP_MODE AUTO让它自己会清理。5.3 高并发写入库要谨慎评估开销Query Store 不是完全免费的。它要捕获、要聚合、要落盘这些都有开销。在写入极其频繁的库上开启后可能会看到轻微的写入延迟增加。怎么评估开启前后对比一下关键指标事务日志写入量、CPU 使用率、写入延迟的 P99。如果增幅在可接受范围内通常是个位数百分比那就没问题。如果明显变差可以考虑把 QUERY_CAPTURE_MODE 从 ALL 调成 AUTO减少捕获量或者加大 DATA_FLUSH_INTERVAL_SECONDS降低落盘频率。5.4 参数化带来的合并与误伤Query Store 会把字面量不同的同类查询归并成一条记录这本来是好事方便你按模板分析。但有时候它会合并过度把语义上其实差异很大的查询归到一起导致统计被平均掉看不出问题。遇到这种情况可以去看sys.query_store_query里的query_parameterization_type字段了解它是怎么被参数化的。如果确实归并得不合理可以考虑对特定查询使用强制参数化提示或者调整数据库的参数化设置。这块比较进阶日常排查遇到概率不高但知道有这么回事遇到时不会抓瞎。5.5 清理策略和合规的平衡CLEANUP_POLICY 决定数据留多久。从排查角度留得越久越好但从存储和合规角度留太久未必合适。特别是涉及敏感业务数据的库查询文本本身可能包含信息保留周期要跟团队的数据管理规范对齐。这不是技术问题但配参数时得想到。6. 把 Query Store 用成日常习惯而不是救火工具我见过太多团队Query Store 是出事才想起来开的。等性能问题发生了才去开结果发现没有历史数据又得等它积累几天才能分析——黄花菜都凉了。正确的姿势是默认开启长期运行。它占的那点空间和开销跟它在你半夜被叫起来排查问题时省下的时间相比完全不值一提。平时不用管它让它默默记录真出问题时你手里有一份完整的案发经过。具体到日常我会做这几件事每周扫一眼内置报表里的回归查询Regressed Queries看看有没有查询悄悄变慢。早发现早处理别等业务报警。每月检查一次存储占用和readonly_reason确保它一直健康运行。对关键业务查询如果发现它计划不稳定主动评估是否强制计划而不是等它翻车。把 Query Store 的数据和你的监控体系打通比如定期把 Top 慢查询导出到自己的监控平台做长期趋势。Query Store 本身不解决性能问题它解决的是看不见的问题。把看不见变成看得见剩下的优化才有方向。这也是为什么我觉得它是云上数据库最值得花时间掌握的功能之一——它不花哨但每次排查都用得上。最后分享一个我自己的习惯每次做完一次性能排查我都会把当时用的那几条查询语句存成一个片段库。下次遇到类似问题改个时间范围、换个排序维度就能直接用。Query Store 的视图结构是固定的这套查询模板一旦攒起来排查效率会越来越高。