★ FEATURED ARTICLE 1. 从一个真实场景说起为什么我盯上了Query Store数据库性能问题最让人头疼的地方不是问题本身有多复杂而是你根本不知道问题出在哪。我见过太多团队数据库跑着跑着突然某条查询从几十毫秒变成几秒开发说SQL没改过运维说服务器资源够用DBA说执行计划看着也没毛病——三方扯皮最后不了了之等下次高峰期再炸一遍。这种场景下Query Store就是那个能帮你“翻旧账”的东西。它不像传统的DMV那样只给你当前时刻的快照而是持续记录查询的执行历史包括执行计划、运行时统计、等待类型等等。你可以理解为以前你只能看到病人现在的体温现在你有了过去一个月的体温曲线。这一篇是Azure SQL DB/DW系列的第4篇专门聊Query Store在实际案例中的应用。第一个案例我选了“缺失索引”——这是最常见、最容易见效、也最容易被误用的优化手段。说它常见是因为几乎每个性能问题排查到最后都会碰到索引缺失的提示说它容易见效加个索引可能直接让查询快十倍说它容易被误用是因为很多人看到“缺失索引”的提示就无脑加结果写性能急剧下降得不偿失。这篇文章适合谁看如果你是在用Azure SQL Database或者Azure Synapse Analytics原SQL DW的开发或运维人员平时需要处理查询性能问题对Query Store只有耳闻但没实际用过那这篇就是写给你的。如果你已经在用Query Store但只停留在“打开它”的阶段这篇文章会告诉你怎么真正把它用起来。我个人的习惯是任何Azure SQL DB的性能排查第一步永远是打开Query Store第二步是看Top Resource Consumers第三步才是根据具体情况深入。缺失索引的发现和验证恰好是这三步的典型串联。2. Query Store到底解决了什么痛点2.1 传统排查手段的局限性在没有Query Store之前我们排查性能问题主要靠这几样东西sys.dm_exec_query_stats、sys.dm_exec_sql_text、sys.dm_exec_query_plan再加上SQL Server Profiler或者Extended Events。这套组合拳不是不能用但有几个硬伤。第一个硬伤是数据易失。DMV里的数据只反映当前缓存中的内容一旦发生内存压力、服务重启、或者计划被逐出缓存历史数据就没了。你上午发现的问题下午想回头查可能什么痕迹都找不到。更别说计划缓存被刷掉之后连当时用的什么执行计划都还原不出来。第二个硬伤是缺少时间维度。DMV给你的是累计值你只能看到“这条查询总共执行了多少次、总共消耗了多少CPU”但你看不到“它在过去一小时内每分钟的CPU消耗趋势”。这就导致你很难判断一个问题是在恶化、在好转、还是稳定存在。第三个硬伤是执行计划的历史对比困难。同一个查询可能因为统计信息更新、参数嗅探、或者数据分布变化在不同时间用了不同的执行计划。传统手段只能看到当前缓存的计划想看历史计划除非你之前手动抓过否则没戏。2.2 Query Store的核心机制Query Store本质上是在数据库内部建了一套“飞行记录仪”。它把查询的编译信息、执行计划、运行时统计持久化到内部表中这些数据不受内存压力影响也不受重启影响当然有保留策略控制。它的工作流程大致是这样的当一条查询被编译时Query Store记录下它的SQL文本和初始执行计划。每次执行时运行时统计执行次数、持续时间、CPU、逻辑读、物理读、内存授予等被聚合到固定的时间窗口默认是60分钟可配置。如果执行计划发生变化新计划也会被记录下来并且和之前的计划做关联。这里有个关键设计Query Store的数据是按时间区间聚合的不是每次执行都存一条。默认情况下它把一小时内同一查询同一计划的执行统计合并成一行。这个设计很聪明既控制了数据量又保留了趋势信息。你可以通过sys.query_store_runtime_stats视图看到每个时间区间的聚合数据。2.3 在Azure SQL DB中的特殊价值Azure SQL Database和本地SQL Server在Query Store的使用上有一些差异这些差异直接影响你的排查策略。首先是默认开启状态。Azure SQL Database从某个时间点开始新建数据库默认开启Query Store而且默认配置是“自动”模式。这意味着你拿到一个新库很可能Query Store已经在跑了不需要你手动开启。但要注意自动模式下的保留策略可能比较激进数据保留时间有限如果你要排查很久以前的问题可能得提前调整配置。其次是没有本地文件系统的概念。本地SQL Server的Query Store数据存在文件组里你可以直接看文件大小。Azure SQL DB里你只能通过sys.database_query_store_options看当前空间使用情况而且空间上限受服务层级影响。Basic和Standard层级的空间限制比较紧Premium和Business Critical层级宽松很多。如果你在低层级上跑Query Store要特别关注空间回收。第三是和Query Performance Insight的集成。Azure门户里有个Query Performance Insight功能底层就是Query Store。你可以直接在门户里看Top CPU、Top Duration、Top Execution Count的查询不用写T-SQL。但门户的功能比较浅真要深入分析还是得自己查Query Store视图。注意Azure SQL Database的Query Store默认操作模式是AUTO这个模式下如果Query Store自己觉得“压力大”可能会自动切换到只读模式停止写入新数据。排查问题时如果发现数据有断档先检查actual_state_desc是不是READ_ONLY。3. 缺失索引案例的完整排查过程3.1 案例背景与初始现象假设我们有一个典型的电商订单库跑在Azure SQL Database的S3层级上。最近业务方反馈“订单查询页面变慢了”具体表现是按用户ID查最近订单列表以前一两秒出结果现在要五六秒高峰期更慢。这个场景太常见了常见到很多人会直接说“加个索引不就完了”。但我的习惯是先看Query Store确认问题到底出在哪条查询上再决定怎么处理。因为“订单查询页面慢”可能对应好几条SQL盲目加索引可能加错地方。第一步确认Query Store状态SELECT desired_state_desc, 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 Store没在正常记录得先处理这个问题。常见原因是空间满了或者被手动切成了只读。3.2 用Query Store定位问题查询确认Query Store正常后下一步是找出“谁在消耗资源”。我一般会按CPU时间排序因为CPU消耗高的查询通常也是影响面最大的SELECT TOP 10 q.query_id, qt.query_sql_text, SUM(rs.count_executions) AS total_executions, SUM(rs.avg_cpu_time * rs.count_executions) / 1000 AS total_cpu_ms, SUM(rs.avg_duration * rs.count_executions) / 1000 AS total_duration_ms, SUM(rs.avg_logical_io_reads * rs.count_executions) AS total_logical_reads 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(HOUR, -24, GETUTCDATE()) GROUP BY q.query_id, qt.query_sql_text ORDER BY total_cpu_ms DESC;这个查询会给你过去24小时内CPU消耗最高的10条查询。注意我用了GETUTCDATE()因为Query Store的时间戳是UTC的如果你用本地时间可能会漏数据。跑完这个查询假设我们发现一条查询特别扎眼它的总CPU时间远高于其他查询而且执行次数很多。看SQL文本大概是这样SELECT OrderId, OrderDate, TotalAmount, Status FROM Orders WHERE CustomerId CustomerId AND OrderDate StartDate ORDER BY OrderDate DESC;这就是典型的“按用户查订单列表”的查询。接下来要看它的执行计划确认是不是因为缺索引导致的全表扫描。3.3 从执行计划中确认缺失索引Query Store里看执行计划有两种方式看图形化计划在SSMS或Azure Data Studio里点开或者直接查plan XML。我一般先用XML快速扫一眼有没有“Missing Index”的提示SELECT p.plan_id, p.query_plan, rs.avg_duration, rs.avg_logical_io_reads, rs.count_executions FROM sys.query_store_plan p JOIN sys.query_store_runtime_stats rs ON p.plan_id rs.plan_id WHERE p.query_id QueryId ORDER BY rs.runtime_stats_interval_id DESC;把query_plan字段点开搜索“MissingIndexes”。如果执行计划里包含缺失索引的提示你会看到类似这样的XML片段MissingIndexes MissingIndexGroup Impact89.23 MissingIndex Database[OrderDB] Schema[dbo] Table[Orders] ColumnGroup UsageEQUALITY Column Name[CustomerId] ColumnId2 / /ColumnGroup ColumnGroup UsageINEQUALITY Column Name[OrderDate] ColumnId3 / /ColumnGroup ColumnGroup UsageINCLUDE Column Name[TotalAmount] ColumnId4 / Column Name[Status] ColumnId5 / /ColumnGroup /MissingIndex /MissingIndexGroup /MissingIndexes这个提示告诉你如果建一个索引以CustomerId为等值条件列OrderDate为不等值条件列然后INCLUDE TotalAmount和Status优化器估计能提升89.23%的性能。但这里有个关键点不要直接照搬这个提示。缺失索引提示只是优化器基于当前查询的“建议”它不考虑其他查询的需求也不考虑写操作的成本。我见过太多人直接按提示建索引结果索引建了一堆写性能掉了一半。3.4 验证索引效果的正确姿势在真正建索引之前我强烈建议做两件事一是用Query Store的历史数据确认这个查询的执行计划是否稳定二是用“假设索引”功能预估效果。先看执行计划稳定性。如果同一个query_id对应多个plan_id说明执行计划发生过变化。你可以这样查SELECT p.plan_id, p.is_forced_plan, p.force_failure_count, rs.avg_duration, rs.avg_logical_io_reads, rs.count_executions, rsi.start_time, rsi.end_time FROM sys.query_store_plan p 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 p.query_id QueryId ORDER BY rsi.start_time DESC;如果发现某个计划突然变差比如逻辑读从几百跳到几万那可能是统计信息过期或者参数嗅探导致的。这种情况下加索引可能有用但更根本的解法可能是更新统计信息或者用OPTION (RECOMPILE)。再看假设索引。SQL Server和Azure SQL DB都支持用WITH (INDEX(...))提示来模拟索引效果但更准确的方式是用Database Engine Tuning Advisor本地或者Query Store的“计划强制”功能Azure。在Azure SQL DB里你可以先建一个测试库把索引建上去跑同样的查询对比。如果不想建测试库至少用SET STATISTICS IO ON和SET STATISTICS TIME ON在加索引前后各跑一次对比逻辑读和CPU时间。我个人的经验是如果一个缺失索引提示的Impact超过80%而且对应的查询是读多写少的场景那加索引基本稳赚。如果Impact在50%以下或者表本身写操作很频繁就要慎重了。4. 索引创建与后续验证的实操细节4.1 索引创建语句的编写要点假设经过验证我们决定加索引。根据缺失索引提示和实际查询模式索引语句大概是这样CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_OrderDate ON dbo.Orders (CustomerId, OrderDate DESC) INCLUDE (TotalAmount, Status) WITH (ONLINE ON, MAXDOP 2);这里有几个细节值得展开说。键列顺序CustomerId在前OrderDate在后。因为查询里CustomerId是等值条件OrderDate是不等值条件。索引的键列顺序应该遵循“等值列在前不等值列在后”的原则这样优化器能最大化利用索引的查找能力。如果反过来OrderDate在前那对于“某个用户的订单”这个查询索引就得先扫所有OrderDate满足条件的行再过滤CustomerId效率差很多。排序方向OrderDate用了DESC。因为查询里有ORDER BY OrderDate DESC如果索引的排序方向和查询一致优化器可以直接用索引的有序性避免排序操作。但要注意如果同一个索引还要服务ORDER BY OrderDate ASC的查询那DESC可能就不合适了。这种情况下可以考虑建两个索引或者接受排序开销。INCLUDE列TotalAmount和Status放在INCLUDE里而不是键列里。因为这两个列只出现在SELECT列表里不参与WHERE过滤和JOIN条件。放在INCLUDE里可以避免索引键过大同时让查询变成覆盖查询Covering Query直接从索引里取数据不用回表。ONLINE ON在Azure SQL Database里ONLINE ON是默认支持的除了Basic层级建索引时不会阻塞写操作。但要注意ONLINE建索引需要更多空间和更长时间如果表特别大得评估影响。MAXDOP 2限制并行度。Azure SQL Database的S3层级有计算资源限制如果建索引时把所有CPU都占了可能影响其他查询。设成2是个保守的选择具体值可以根据服务层级调整。4.2 索引创建后的效果验证索引建完后不能只看“感觉快了”得有数据支撑。我一般会做三件事。第一清空Query Store的旧数据或者等一个新的时间窗口然后跑同样的业务查询对比加索引前后的运行时统计。Query Store会自动记录新计划你可以这样对比SELECT p.plan_id, rs.avg_duration, rs.avg_cpu_time, rs.avg_logical_io_reads, rs.count_executions, rsi.start_time FROM sys.query_store_plan p 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 p.query_id QueryId ORDER BY rsi.start_time DESC;如果新计划的avg_logical_io_reads从几万降到几百avg_duration从几秒降到几十毫秒那说明索引生效了。第二检查写操作有没有受影响。加索引最怕的就是INSERT/UPDATE/DELETE变慢。你可以查一下Orders表相关的写操作在加索引前后的平均持续时间SELECT qt.query_sql_text, rs.avg_duration, rs.count_executions, rsi.start_time 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 qt.query_sql_text LIKE %INSERT%Orders% OR qt.query_sql_text LIKE %UPDATE%Orders% OR qt.query_sql_text LIKE %DELETE%Orders% ORDER BY rsi.start_time DESC;如果写操作的平均持续时间明显上升比如从1毫秒变成5毫秒而读操作的收益又不足以抵消这个代价那可能得考虑调整索引或者干脆删掉。第三观察一段时间内的整体资源趋势。Query Store的sys.query_store_runtime_stats_interval视图可以看每个时间区间的整体统计SELECT rsi.start_time, rsi.end_time, SUM(rs.avg_cpu_time * rs.count_executions) / 1000 AS total_cpu_ms, SUM(rs.avg_logical_io_reads * rs.count_executions) AS total_logical_reads, SUM(rs.count_executions) AS total_executions FROM sys.query_store_runtime_stats rs 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 rsi.start_time, rsi.end_time ORDER BY rsi.start_time DESC;如果加索引后整体CPU和逻辑读都下降了那说明优化是正向的。4.3 索引维护的长期策略加索引不是一劳永逸的事。随着数据量增长和查询模式变化今天的好索引可能变成明天的坏索引。我一般会建议团队建立这样的维护节奏每月跑一次“索引使用情况”检查看看哪些索引几乎没被用过哪些索引的写代价远大于读收益。Query Store本身不直接提供索引使用统计但你可以结合sys.dm_db_index_usage_stats和Query Store的执行计划来推断。每季度做一次“缺失索引”复查。Query Store里积累的缺失索引提示可以作为参考但不要盲目采纳。重点看那些Impact高、出现频率高、且对应查询是核心业务的提示。每次发版后观察一周。新功能上线往往带来新的查询模式Query Store能帮你快速发现新出现的性能问题。提示Azure SQL Database的Query Store有“自动计划修正”功能Automatic Plan Correction可以自动检测计划回归并强制使用好的计划。但这个功能不能替代索引优化它解决的是计划选择问题不是索引缺失问题。5. 常见问题与排查技巧实录5.1 Query Store数据不完整或断档这是最常见的问题之一。表现是你明明知道某个时间段有问题但Query Store里就是没有那段时间的数据。原因通常有这几个空间不足导致自动切换到只读。Azure SQL Database的Query Store空间上限取决于服务层级。Basic层级只有10MB左右S3层级大概100MBPremium层级可以到1GB。如果数据写入速度超过清理速度Query Store会进入READ_ONLY状态。你可以查readonly_reason字段确认SELECT actual_state_desc, readonly_reason, current_storage_size_mb, max_storage_size_mb FROM sys.database_query_store_options;readonly_reason是个位掩码常见值有1表示空间满2表示内部错误4表示手动设置只读8表示达到空间上限。如果是空间问题可以调大max_storage_size_mb如果服务层级允许或者调小INTERVAL_LENGTH_MINUTES让聚合更粗粒度减少数据量。保留策略太激进。Query Store默认的STALE_QUERY_THRESHOLD_DAYS是30天但实际保留时间还受空间清理策略影响。如果空间紧张旧数据会被优先清理。你可以查一下最早的数据是什么时候的SELECT MIN(start_time) AS earliest_data, MAX(end_time) AS latest_data FROM sys.query_store_runtime_stats_interval;如果最早数据只有几天前而你需要更长的历史那就得调整保留策略或者升级服务层级。查询捕获模式设置不当。Query Store有三种捕获模式ALL捕获所有查询、AUTO自动过滤不重要的查询、NONE不捕获。AUTO模式下一些执行次数少、资源消耗低的查询不会被记录。如果你要排查的恰好是这类查询就得临时改成ALL模式。但要注意ALL模式会产生大量数据可能加速空间耗尽。5.2 缺失索引提示不准确或误导缺失索引提示是优化器基于单个查询给出的建议它有几个已知的局限性不考虑现有索引。有时候优化器提示的索引和你已有的某个索引高度重叠只是列顺序或INCLUDE列略有不同。这种情况下与其新建索引不如调整现有索引。你可以用下面的查询看看现有索引的定义SELECT i.name AS index_name, i.type_desc, STRING_AGG(c.name, , ) WITHIN GROUP (ORDER BY ic.key_ordinal) AS key_columns, STRING_AGG(CASE WHEN ic.is_included_column 1 THEN c.name END, , ) AS included_columns FROM sys.indexes i JOIN sys.index_columns ic ON i.object_id ic.object_id AND i.index_id ic.index_id JOIN sys.columns c ON ic.object_id c.object_id AND ic.column_id c.column_id WHERE i.object_id OBJECT_ID(dbo.Orders) GROUP BY i.name, i.type_desc;不考虑写操作成本。优化器只关心读查询的性能不关心索引对INSERT/UPDATE/DELETE的影响。如果表写操作频繁缺失索引提示的Impact再高也得慎重。可能受参数嗅探影响。如果查询用了参数优化器基于某组特定参数值生成的计划可能不适合其他参数值。这种情况下缺失索引提示可能只对特定参数有效。你可以用Query Store的“计划对比”功能看看不同参数值下的计划差异。Impact百分比可能虚高。Impact是基于优化器的成本估算模型算出来的不一定反映真实收益。我见过Impact 95%的提示加了索引后实际只快了20%。所以一定要用实际运行时统计验证。5.3 加索引后查询反而变慢这种情况虽然少见但确实存在。常见原因有索引选择错误。优化器可能选了一个“看起来成本低”但实际效率差的索引。比如新加的索引统计信息还没更新优化器低估了它的成本。解决办法是更新统计信息UPDATE STATISTICS dbo.Orders WITH FULLSCAN;索引碎片或页密度问题。新加的索引如果键列顺序不合理可能导致页分裂频繁反而增加I/O。你可以查一下索引的碎片率SELECT i.name, ps.avg_fragmentation_in_percent, ps.page_count FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(dbo.Orders), NULL, NULL, SAMPLED) ps JOIN sys.indexes i ON ps.object_id i.object_id AND ps.index_id i.index_id;如果碎片率超过30%考虑重建索引。参数嗅探导致计划回归。新索引改变了优化器的选择空间可能让它选了一个对当前参数值不好但对其他参数值好的计划。这种情况下可以用Query Store强制使用之前的计划或者给查询加OPTION (RECOMPILE)。写操作阻塞。如果索引建在写频繁的表上且没有用ONLINE ON建索引期间可能阻塞其他操作。建完后如果写操作变慢可能是索引维护开销导致的。5.4 Query Store相关的问题速查表问题现象可能原因排查方法解决思路Query Store无数据未开启或已切换到只读查sys.database_query_store_options开启或调整空间/保留策略数据断档空间不足或清理策略激进查readonly_reason和最早数据时间调大空间或缩短聚合间隔缺失索引提示不出现捕获模式为AUTO且查询被过滤查query_capture_mode_desc临时改为ALL模式加索引后查询变慢统计信息未更新或计划回归对比加索引前后的执行计划更新统计信息或强制旧计划索引未被使用索引列顺序或INCLUDE列不合理查执行计划中的实际使用索引调整索引定义或删除重建写操作变慢索引维护开销对比加索引前后的写操作持续时间评估是否值得保留索引5.5 几个我踩过的坑第一个坑在Basic层级上开Query Store。Basic层级的空间限制太紧Query Store跑不了几个小时就满了然后自动切只读。如果你非要在Basic层级上用建议把INTERVAL_LENGTH_MINUTES设成60以上STALE_QUERY_THRESHOLD_DAYS设成1或2尽量减少数据量。第二个坑直接照搬缺失索引提示的列顺序。有一次优化器提示的索引是(OrderDate, CustomerId)我直接建了结果查询还是慢。后来发现实际查询里CustomerId是等值条件OrderDate是不等值条件应该把CustomerId放前面。优化器的提示有时候不考虑查询的实际参数化模式得自己判断。第三个坑忽略Query Store的时间戳是UTC。有一次排查问题我用本地时间过滤结果查出来的数据对不上。后来才发现Query Store的start_time和end_time都是UTC必须用GETUTCDATE()而不是GETDATE()。第四个坑在高峰期建索引。虽然用了ONLINE ON但建索引还是消耗了大量CPU和I/O导致其他查询变慢。后来我养成了习惯建索引前先看Query Store里的资源趋势避开业务高峰期。第五个坑加了索引但没更新统计信息。新索引的统计信息是空的优化器可能低估它的收益继续用旧计划。建完索引后手动跑一次UPDATE STATISTICS能让优化器更快用上新索引。6. 从缺失索引延伸到Query Store的更多用法缺失索引只是Query Store的一个应用场景。实际上Query Store能做的事情远不止这些。比如你可以用它来追踪计划回归同一个查询突然变慢对比新旧计划的运行时统计就能定位到是哪个计划变差了。再比如你可以用它来做容量规划看过去一个月的资源消耗趋势预估未来需要升级到什么服务层级。我个人的习惯是每次做性能优化前后都用Query Store留一份“基线数据”。优化前的数据作为对照优化后的数据作为验证。这样过一段时间回头看能清楚知道每次优化到底带来了多少收益。还有一个很实用的技巧用Query Store的“计划强制”功能来稳定关键查询的性能。如果某个核心查询因为参数嗅探导致计划不稳定你可以强制它使用某个已知良好的计划。这个功能在Azure SQL Database里是内置的不需要额外配置。最后再分享一个小经验Query Store的视图很多但常用的就那么几个。sys.query_store_runtime_stats看运行时统计sys.query_store_plan看执行计划sys.query_store_query_text看SQL文本sys.query_store_wait_stats看等待类型。把这四个视图的关系搞清楚大部分排查场景都能覆盖。等待类型的分析我打算放在下一篇里展开讲那又是另一个能挖出很多故事的方向。
阅读完成 · 觉得有帮助？
