1. 从一次性能告警说起Query Store 到底能帮我们看见什么数据库跑得好好的突然某天业务方反馈某个报表页面加载时间从两秒变成了二十秒。你登上服务器打开活动监视器发现 CPU 飙高但具体是哪条语句在捣鬼是执行计划变了还是统计信息过期了又或者是某个查询突然走了全表扫描在没有 Query Store 之前我们排查这类问题的常规手段无非是查 DMV、抓 Profiler 跟踪、翻缓存计划。但这些方法要么是瞬时的要么开销大要么历史数据保留时间太短等你反应过来现场早就没了。Query Store 的出现相当于给数据库装了一个“飞行记录仪”。它持续不断地把查询语句、执行计划、运行时统计信息落盘保存让你在问题发生之后还能回溯到那个时间点看看当时到底发生了什么。这个系列的前几篇已经聊过 Query Store 的基本配置和整体架构今天这篇聚焦一个非常具体、也非常高频的场景如何利用 Query Store 定位缺失索引并验证索引建议是否靠谱。缺失索引这个问题说大不大说小不小。往小了说加个索引可能就解决了往大了说加错索引会导致写入性能下降、存储膨胀、甚至让优化器选错计划。所以关键不在于“找到缺失索引”而在于“找到真正值得加的缺失索引”。Query Store 在这方面提供的信息比传统的sys.dm_db_missing_index_details要丰富得多因为它能告诉你这个查询跑了多少次、平均耗时多少、计划长什么样、有没有多个计划在竞争。这些上下文信息才是做决策的依据。这篇文章适合已经对 Query Store 有基本了解、想深入用它来解决实际性能问题的数据库从业者。如果你还没开启 Query Store建议先去把基础配置搞定否则后面的内容你只能干看着。我会用一个模拟的电商订单查询场景来贯穿全文所有数据都是虚构的但操作逻辑和真实环境完全一致。2. 为什么缺失索引的“官方建议”经常不靠谱2.1 DMV 给出的缺失索引到底缺了什么信息很多人第一次接触缺失索引都是从sys.dm_db_missing_index_details这个 DMV 开始的。它确实好用一条查询就能列出当前实例里所有被优化器标记为“如果有索引会更好”的列组合。但用久了你会发现这个视图给的建议经常让人哭笑不得它可能建议你在一个千万级的大表上建一个包含五六个列的索引或者对同一个表给出十几条互相重叠的建议甚至有些建议的“预计提升”高达 99%但你真把索引建上去性能纹丝不动。问题出在哪儿这个 DMV 只记录了优化器在编译某个语句时“当时觉得缺什么”它不告诉你这个语句跑了多少次、每次跑多久、有没有其他更优的计划被选中过。换句话说它给的是“症状”不是“诊断”。一个只跑过一次的报表查询和一个每秒跑几百次的交易查询在 DMV 眼里可能给出同样的缺失索引建议但它们的优先级天差地别。更麻烦的是DMV 的数据是实例级别的累积值重启之后就清空了。你没法知道这个建议是今天产生的还是上周产生的也没法知道它对应的是哪个具体的查询语句。这就导致很多人在生产环境里盲目地按照 DMV 建议批量建索引结果索引建了一堆性能没提升多少写入反而慢了不少。2.2 Query Store 补上了哪几块关键拼图Query Store 的优势在于它把“查询”作为核心实体来组织数据。每一条被执行的语句都有一个唯一的 Query ID围绕这个 ID你可以拿到它的执行计划历史、运行时统计、等待类型、以及最重要的——它在不同时间段的资源消耗趋势。当你要判断一个缺失索引建议是否值得采纳时Query Store 能提供以下几个维度的关键信息第一这个查询的执行频率和总资源消耗。一个平均耗时 500 毫秒但每天只跑一次的查询和一个平均耗时 50 毫秒但每秒跑一百次的查询后者对系统的影响可能是前者的上千倍。Query Store 的“总体资源消耗”视图能直接按总 CPU 时间、总执行次数、总持续时间排序帮你把注意力集中在真正的大头上面。第二这个查询是否有多个执行计划在竞争。有时候优化器选了一个带全表扫描的计划并不是因为没有索引而是因为统计信息不准或者参数嗅探导致它“误判”了。这种情况下你建了索引优化器可能还是不走。Query Store 的计划历史能让你看到同一个 Query ID 下有多少个不同的 Plan ID以及每个计划各自的平均耗时。如果某个计划明显更优但没被稳定选中那问题可能出在计划稳定性上而不是索引缺失。第三这个查询的等待类型分布。Query Store 在 SQL Server 2017 之后增加了等待统计的采集你可以看到某个查询主要卡在 CPU、IO 还是锁等待上。如果它主要等的是 IO那缺失索引的可能性就比较大如果主要等的是锁那加索引可能反而加剧阻塞。2.3 一个真实的误判案例索引建了计划没变我遇到过这样一个情况某张订单明细表上有一个按OrderDate过滤的查询DMV 一直提示缺失OrderDate列的索引。运维同事没多想就加了一个非聚集索引结果查询还是慢。后来用 Query Store 一查发现这个查询有两个计划一个走聚集索引扫描一个走新建的非聚集索引查找加键查找。优化器大部分时候选了后者但因为键查找的随机 IO 开销太大实际执行时间反而比全表扫描还长。更优的做法是建一个覆盖索引把查询需要的几个列都包含进去而不是只建一个单列索引。这个案例说明缺失索引建议只是起点真正的决策需要结合 Query Store 提供的计划信息和运行时数据。下面我就把完整的排查流程拆开来讲。3. 用 Query Store 定位高价值缺失索引的完整链路3.1 第一步开启并确认 Query Store 的采集状态在开始之前先确认你的数据库已经开启了 Query Store并且处于读写模式。执行下面这条语句SELECT actual_state_desc, readonly_reason, current_storage_size_mb, max_storage_size_mb, query_capture_mode_desc, size_based_cleanup_mode_desc, stale_query_threshold_days FROM sys.database_query_store_options;重点关注actual_state_desc是否为READ_WRITE以及query_capture_mode_desc是否为AUTO或ALL。如果是AUTOQuery Store 只会自动捕获那些“重要”的查询比如执行次数多、资源消耗大的。这个模式在大多数生产环境下是合适的因为它能避免采集过多冷查询导致存储膨胀。但如果你怀疑某个低频但关键的查询被漏掉了可以临时改成ALL排查完再改回来。另外注意stale_query_threshold_days这个参数默认是 30 天。意思是如果一个查询超过 30 天没执行它的统计信息会被标记为过期并最终清理。如果你要排查的是一个周期性任务比如每月跑一次的月结查询这个阈值可能就太短了需要适当调大。3.2 第二步从“总体资源消耗”视图锁定嫌疑查询Query Store 内置了几个报表视图在 SSMS 里可以直接右键数据库 - Query Store - 打开对应报表。但作为从业者我更习惯直接写查询因为灵活。下面这条语句按总 CPU 时间降序列出排名前 20 的查询SELECT TOP 20 q.query_id, qt.query_sql_text, SUM(rs.count_executions) AS total_executions, SUM(rs.avg_cpu_time * rs.count_executions) / 1000.0 AS total_cpu_ms, SUM(rs.avg_duration * rs.count_executions) / 1000.0 AS total_duration_ms, SUM(rs.avg_logical_io_reads * rs.count_executions) AS total_logical_reads, COUNT(DISTINCT p.plan_id) AS plan_count FROM sys.query_store_query q JOIN sys.query_store_query_text qt ON q.query_text_id qt.query_text_id JOIN sys.query_store_plan p ON q.query_id p.query_id JOIN sys.query_store_runtime_stats rs ON p.plan_id rs.plan_id JOIN sys.query_store_runtime_stats_interval rsi ON rs.runtime_stats_interval_id rsi.runtime_stats_interval_id WHERE rsi.start_time DATEADD(DAY, -7, GETUTCDATE()) GROUP BY q.query_id, qt.query_sql_text ORDER BY total_cpu_ms DESC;这条查询把最近七天的数据聚合了一下你可以看到每个查询的总执行次数、总 CPU 时间、总持续时间和逻辑读次数以及它有多少个不同的执行计划。plan_count这个字段很关键如果某个查询有多个计划说明它的执行计划不稳定可能存在参数嗅探或者统计信息波动的问题。拿到这个列表之后不要急着看第一条就去建索引。先扫一眼哪些查询的total_cpu_ms和total_logical_reads都很大并且plan_count比较小说明计划稳定就是单纯缺索引。这类查询才是缺失索引的高价值目标。3.3 第三步关联缺失索引 DMV但别全信它Query Store 本身不直接存储缺失索引信息但你可以把上一步查出来的 Query ID 和sys.dm_db_missing_index_details做关联。不过这个 DMV 没有 Query ID 字段所以没法直接 JOIN。一个变通的做法是先用 Query Store 找到嫌疑查询的 SQL 文本然后从文本里提取表名和过滤条件再去 DMV 里比对。更高效的方式是直接查sys.dm_db_missing_index_group_stats它有一个avg_total_user_cost和avg_user_impact字段可以帮你粗略排序。但记住这个成本估算是在编译时算出来的不一定反映真实运行时的开销。我的习惯是把它当作一个“候选清单”而不是“执行清单”。SELECT mid.statement AS table_name, mid.equality_columns, mid.inequality_columns, mid.included_columns, migs.avg_total_user_cost, migs.avg_user_impact, migs.user_seeks migs.user_scans AS total_seeks_scans FROM sys.dm_db_missing_index_details mid JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle mig.index_handle JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle migs.group_handle WHERE mid.database_id DB_ID() ORDER BY migs.avg_user_impact * migs.avg_total_user_cost DESC;把这张表的结果和 Query Store 的查询列表放在一起看如果某个表的缺失索引建议正好对应一个高 CPU 消耗的查询那这个建议的可信度就大大提升了。3.4 第四步查看具体执行计划确认瓶颈在 IO 还是 CPU找到嫌疑查询之后下一步是看它的实际执行计划。Query Store 里可以直接拿到计划 XML但更直观的方式是用sys.query_store_plan里的query_plan字段或者直接在 SSMS 的 Query Store 报表里点开“跟踪的查询” - 选中查询 - 查看计划。看计划的时候重点关注两件事一是是否有明显的全表扫描或聚集索引扫描并且扫描的行数远大于返回的行数二是是否有键查找Key Lookup并且键查找的预估执行次数很高。这两种情况都是缺失索引的典型信号。但还有一种情况容易被忽略计划里明明有索引查找但实际执行时却走了扫描。这通常是因为统计信息过期优化器低估了返回行数觉得扫描更划算。这种情况下更新统计信息可能比建索引更有效。Query Store 的计划历史能帮你判断如果同一个查询在不同时间段的计划不一样且性能差异明显那大概率是统计信息或参数嗅探的问题。4. 从建议到落地索引该不该加、怎么加4.1 用 Query Store 的运行时数据做成本收益估算假设你已经锁定了一个查询它每天执行 5000 次平均每次逻辑读 20000 页平均 CPU 时间 80 毫秒。缺失索引 DMV 建议在OrderDate列上建索引预估提升 85%。现在你要判断这个索引到底值不值得建先算收益。如果索引能把逻辑读从 20000 页降到 200 页那每次查询节省 19800 页的逻辑读。按每天 5000 次算一天节省约 9900 万页逻辑读。这个数字很可观尤其是当这个查询是 IO 瓶颈的时候。再算成本。索引本身占用的存储空间、每次 INSERT/UPDATE/DELETE 时维护索引的开销、以及可能导致的计划缓存膨胀。对于一张写入频繁的订单表每增加一个索引写入延迟可能增加 5% 到 15%。如果这张表的写入量本身就很大那这个成本就不能忽略。Query Store 能帮你量化收益侧的数据但成本侧需要你结合业务写入模式来判断。我的经验是对于读多写少的表索引可以大胆加对于读写比在 3:1 以内的表加索引前一定要做写入性能测试。4.2 覆盖索引 vs 单列索引一个决策表格缺失索引 DMV 经常给出的是单列或少数几列的建议但实际最优解往往是覆盖索引。下面这个表格对比了两种策略的适用场景对比维度单列/窄索引覆盖索引适用场景查询只返回少量列且过滤条件高度选择性查询返回多列且键查找开销占比高逻辑读优化减少扫描量但可能引入键查找消除键查找逻辑读最小化存储开销较小较大尤其是包含大字段时写入影响较小较大每次更新涉及更多列维护成本低中到高需要关注碎片和统计信息Query Store 信号计划中有 Index Seek Key Lookup计划中有 Clustered Index Scan 且返回列多判断依据很简单打开 Query Store 里的实际执行计划如果看到 Index Seek 后面跟着一个 Key Lookup并且 Key Lookup 的预估执行次数占总成本的 30% 以上那就应该考虑覆盖索引。如果只是单纯的 Clustered Index Scan且返回列不多那单列索引可能就够了。4.3 加索引之前必须做的三个验证在真正执行CREATE INDEX之前我习惯做三个验证避免建完就后悔。第一个验证用SET STATISTICS IO, TIME ON在测试环境跑一遍原始查询记录逻辑读和 CPU 时间。然后建索引再跑一遍对比数据。如果逻辑读下降但 CPU 时间没降甚至上升说明索引带来的额外开销抵消了 IO 收益需要重新评估。第二个验证检查这个查询是否在事务中频繁执行。如果是加索引可能加剧锁竞争。可以用 Query Store 的等待统计来确认如果这个查询的等待类型主要是LCK_M_*而不是PAGEIOLATCH_*那加索引的优先级就要降低。第三个验证模拟写入负载。在测试环境用相同的写入模式跑一段时间对比加索引前后的写入吞吐量和延迟。这一步最容易被跳过但恰恰是最关键的。我见过太多案例索引加完读快了但写入慢了一倍最后不得不回滚。5. 那些 Query Store 报表不会告诉你的坑5.1 参数嗅探导致的“假缺失索引”有一种情况特别迷惑人Query Store 显示某个查询有多个计划其中一个计划走索引查找很快另一个计划走全表扫描很慢。你以为是缺索引其实索引早就有了只是优化器在某些参数值下选择了扫描。这就是典型的参数嗅探问题。判断方法在 Query Store 的“跟踪的查询”里选中那个慢计划查看它的编译参数值。如果慢计划对应的参数值返回的行数特别多比如占了全表 30% 以上那优化器选扫描其实是合理的。这种情况下解决方案不是加索引而是考虑用OPTION (RECOMPILE)或者调整查询写法让优化器每次都能根据实际参数值做选择。5.2 索引建议的“重叠陷阱”sys.dm_db_missing_index_details经常对同一个表给出多条建议比如一条建议(A, B)另一条建议(A, C)还有一条建议(A, B, C)。如果你不加判断地全建了就会产生大量冗余索引。正确的做法是合并找出这些建议的公共前缀列建一个尽可能覆盖多个查询的宽索引而不是每个建议都单独建。Query Store 在这里的作用是帮你判断哪些查询的执行频率高、资源消耗大从而决定优先满足哪些查询的需求。比如(A, B)对应的查询每天跑一万次(A, C)对应的查询每天只跑十次那显然应该优先建(A, B)或者建(A, B) INCLUDE (C)来同时覆盖两者。5.3 索引建完之后 Query Store 数据没变化有时候你建了索引但 Query Store 里的查询统计还是老样子逻辑读没降CPU 时间没变。别慌先确认两件事一是查询是否真的用了新索引可以在计划里看二是 Query Store 的数据是否有延迟。Query Store 的运行时统计默认是按小时聚合落盘的刚建完索引可能还没到下一个聚合周期。你可以手动执行sys.sp_query_store_flush_db强制刷盘然后刷新报表。如果计划确实没走新索引那可能是统计信息没更新。建完索引后SQL Server 通常会自动更新统计信息但对于大表自动更新可能采样率很低。手动跑一次UPDATE STATISTICS带FULLSCAN往往能解决问题。6. 把缺失索引排查变成日常习惯6.1 每周花十分钟看三个报表Query Store 的价值不在于一次性排查而在于持续监控。我习惯每周花十分钟看三个东西一是“总体资源消耗”里排名前十的查询有没有变化二是“执行计划变化”里有没有查询出现了新的计划三是“缺失索引”DMV 里有没有新增的高影响建议。这三个报表结合起来基本能覆盖大部分性能退化场景。如果发现某个查询的 CPU 时间突然上升先看它的计划有没有变。如果计划没变但资源消耗涨了那可能是数据量增长导致的需要评估索引是否还够用。如果计划变了那就用计划强制Plan Forcing先稳住再慢慢排查原因。6.2 用 Query Store 的“回归查询”功能做版本对比SQL Server 2016 之后的 Query Store 有一个“回归查询”报表可以对比两个时间段内同一个查询的性能差异。这个功能在数据库版本升级、索引变更、统计信息更新之后特别有用。你可以把变更前一周和变更后一周的数据拉出来对比快速定位哪些查询变慢了、哪些变快了。我一般会在每次重大变更比如加索引、改配置、升级版本之后固定跑一次回归对比把结果存档。这样万一后续出问题至少有一个基线可以参考。6.3 清理策略别让 Query Store 自己把自己撑爆Query Store 默认的最大存储是 100MB对于繁忙的数据库来说这个值可能一两天就满了。满了之后 Query Store 会进入只读模式不再采集新数据你就失去了监控能力。所以一定要根据数据库的负载调整MAX_STORAGE_SIZE_MB并且设置合理的清理策略。我的建议是对于 OLTP 系统最大存储设到 1GB 到 2GB保留天数设 30 天清理模式设为AUTO。对于数据仓库系统查询数量少但单个查询消耗大可以设到 2GB 到 4GB保留 60 天。定期检查sys.database_query_store_options里的current_storage_size_mb如果持续接近上限就要考虑调大或者缩短保留期。另外STALE_QUERY_THRESHOLD_DAYS这个参数也值得关注。默认 30 天意思是超过 30 天没执行的查询会被清理。如果你有一些季度性的报表查询这个阈值就不够用需要调到 90 天以上。7. 一个完整的模拟排查过程为了把上面的内容串起来我用一个虚构的电商订单场景走一遍完整流程。假设有一张Orders表大约 500 万行每天新增 2 万行。业务反馈“订单查询页面”最近变慢平均响应时间从 1.5 秒涨到了 6 秒。第一步打开 Query Store 的“总体资源消耗”报表按总 CPU 时间排序发现排名第一的查询是SELECT OrderID, CustomerID, OrderDate, TotalAmount, Status FROM Orders WHERE OrderDate StartDate AND OrderDate EndDate AND Status Pending ORDER BY OrderDate DESC;这个查询每天执行约 8000 次总 CPU 时间在过去七天里排第一逻辑读总量也很大。它有两个执行计划一个走聚集索引扫描一个走IX_OrderDate索引查找加键查找。大部分时候走的是扫描计划。第二步查看扫描计划的详情发现预估行数是 500 万实际返回行数平均是 1200 行。优化器明显低估了Status Pending的选择性以为要返回大量行所以选了扫描。第三步查缺失索引 DMV发现它建议在(OrderDate, Status)上建索引并包含CustomerID, TotalAmount。这个建议和 Query Store 的信号吻合。第四步做成本收益估算。当前每次查询逻辑读约 45000 页建覆盖索引后预计降到 50 页左右。每天 8000 次一天节省约 3.6 亿页逻辑读。写入方面Orders表每天新增 2 万行更新约 5000 行加一个覆盖索引对写入的影响估计在 8% 左右可以接受。第五步在测试环境验证。建索引后查询逻辑读降到 45 页CPU 时间从 120 毫秒降到 8 毫秒。写入延迟从平均 3 毫秒增加到 3.3 毫秒在可接受范围内。第六步生产环境上线。选择业务低峰期执行CREATE INDEX并带上ONLINE ON避免阻塞。建完后手动UPDATE STATISTICS然后观察 Query Store 报表确认新计划被稳定选中。这个流程走下来从发现问题到解决大概需要半天时间。如果没有 Query Store光是定位到具体查询和验证计划变化可能就要花两三天。8. 一些个人体会Query Store 这个功能刚接触的时候觉得它就是个“查询历史记录器”用久了才发现它真正的价值在于把性能排查从“猜”变成了“看”。以前优化索引靠经验、靠试错现在有了运行时数据支撑决策的底气完全不一样。但也要清醒地认识到Query Store 不是银弹。它采集的数据本身也有开销在高并发场景下Query Store 的写入可能成为新的瓶颈。我一般会把它的数据采集间隔适当调大比如从默认的 60 分钟改成 30 分钟平衡监控精度和系统开销。另外Query Store 的报表在 SSMS 里偶尔会卡直接写查询往往更快更灵活建议把常用的几条查询存成脚本随时调用。最后说一个容易被忽略的点Query Store 的数据是 per-database 的如果你有几十个数据库逐个去看很不现实。可以考虑用中央管理服务器或者自己写脚本把各个库的 Query Store 数据汇总到一张表里做统一分析。这个后续可以单独展开聊。