SQL Server特定存储过程Top5高效执行计划分析及强制方案问询

一、验证Query Store查询的正确性与适用性 #

先给一个标准的、针对目标存储过程的Query Store查询示例，你可以对比自己修改后的语句是否符合这个逻辑：

SELECT TOP 5
    qsp.plan_id,
    qsq.query_id,
    qsrs.avg_duration,
    qsrs.avg_logical_io_reads,
    qsrs.avg_cpu_time,
    qsrs.count_executions,
    qsp.is_forced_plan,
    qsp.query_plan_hash
FROM sys.query_store_plan qsp
JOIN sys.query_store_query qsq ON qsp.query_id = qsq.query_id
JOIN sys.query_store_object qso ON qsq.object_id = qso.object_id
JOIN sys.query_store_runtime_stats qsrs ON qsp.plan_id = qsrs.plan_id
WHERE qso.object_name = 'axsp_upd_credit_utilisation2'
  AND qso.object_type = 'PROCEDURE'
ORDER BY qsrs.avg_duration ASC, qsrs.avg_logical_io_reads ASC, qsrs.avg_cpu_time ASC;

正确性判断要点 #

• 关联表必须完整：要通过query_store_object定位到目标存储过程，再关联query_store_query和query_store_plan拿到计划，最后通过query_store_runtime_stats获取实际运行性能数据
• 筛选条件精准：用object_name和object_type='PROCEDURE'确保只针对目标存储过程，避免混入其他对象的计划
• 排序逻辑匹配需求：按平均耗时、逻辑读、CPU时间升序，优先选出资源消耗低的计划

适用性说明 #

这个查询适用于SQL Server 2016及以上版本（Query Store从2016开始支持），前提是目标数据库已经启用Query Store（执行ALTER DATABASE [你的数据库名] SET QUERY_STORE = ON;开启）。

二、核心疑问解答 #

1. 如何修改查询以基于多性能指标获取高效计划 #

不能只看单一指标，要综合耗时、IO、CPU等多维度评估，有两种常用方式：

方式1：多维度排序

直接按多个关键指标依次排序，优先优先级高的指标：

SELECT TOP 5
    qsp.plan_id,
    qsq.query_id,
    qsrs.avg_duration,
    qsrs.avg_logical_io_reads,
    qsrs.avg_cpu_time,
    qsrs.count_executions
FROM sys.query_store_plan qsp
JOIN sys.query_store_query qsq ON qsp.query_id = qsq.query_id
JOIN sys.query_store_object qso ON qsq.object_id = qso.object_id
JOIN sys.query_store_runtime_stats qsrs ON qsp.plan_id = qsrs.plan_id
WHERE qso.object_name = 'axsp_upd_credit_utilisation2'
  AND qso.object_type = 'PROCEDURE'
ORDER BY 
    qsrs.avg_duration ASC,   -- 优先看耗时最短
    qsrs.avg_logical_io_reads ASC,  -- 再看IO消耗
    qsrs.avg_cpu_time ASC;  -- 最后看CPU消耗

方式2：计算综合性能得分

根据业务瓶颈调整各指标权重，生成综合得分排序，比如IO是主要瓶颈就提高IO的权重：

SELECT TOP 5
    qsp.plan_id,
    qsq.query_id,
    qsrs.avg_duration,
    qsrs.avg_logical_io_reads,
    qsrs.avg_cpu_time,
    qsrs.count_executions,
    -- 权重可根据实际情况调整，比如这里IO权重更高
    (qsrs.avg_duration/1000 + qsrs.avg_logical_io_reads*20 + qsrs.avg_cpu_time/1000) AS performance_score
FROM sys.query_store_plan qsp
JOIN sys.query_store_query qsq ON qsp.query_id = qsq.query_id
JOIN sys.query_store_object qso ON qsq.object_id = qso.object_id
JOIN sys.query_store_runtime_stats qsrs ON qsp.plan_id = qsrs.plan_id
WHERE qso.object_name = 'axsp_upd_credit_utilisation2'
  AND qso.object_type = 'PROCEDURE'
ORDER BY performance_score ASC;

2. 添加哪些特定条件或筛选器缩小结果范围 #

• 过滤低执行次数的异常计划：加入AND qsrs.count_executions >= 5（数值可调整），排除偶然执行一两次的异常计划，避免干扰判断
• 排除无效计划：加入AND qsp.is_compiled_plan_valid = 1，只保留编译有效的计划
• 限定时间范围：如果只看最近的运行数据，关联时间区间表筛选：

  AND qsrs.runtime_stats_interval_id IN (
      SELECT TOP 10 runtime_stats_interval_id 
      FROM sys.query_store_runtime_stats_interval 
      ORDER BY end_time DESC
  )
  

• 排除已强制的计划：如果要找新的候选计划，加入AND qsp.is_forced_plan = 0
• 过滤特定参数场景：如果存储过程因参数不同产生不同计划，可关联sys.query_store_query_text查看参数值，针对性筛选

3. 确定最优计划后强制使用的最佳方式 #

推荐用Query Store自带的强制计划功能，两种操作方式：

手动操作（SSMS）

1. 打开SSMS，连接到目标数据库
2. 展开【查询存储】->【查询存储目录视图】->【查询】
3. 找到目标存储过程对应的查询，右键点击最优计划，选择【强制计划】

脚本操作

先从之前的查询中拿到query_id和plan_id，执行：

EXEC sp_query_store_force_plan 
    @query_id = <你的query_id>, 
    @plan_id = <你的plan_id>;

注意事项

• 强制前要确认该计划适配当前数据分布，避免参数嗅探导致性能退化
• 定期监控强制计划的性能，若数据分布变化导致性能下降，取消强制：

  EXEC sp_query_store_unforce_plan @query_id = <query_id>, @plan_id = <plan_id>;
  

• 如果存储过程包含多个查询语句，要确保强制的是对应语句的计划，而非整个存储过程的所有计划

三、SQL Server执行计划分析最佳实践 #

• 重点关注核心指标：逻辑读、CPU时间、执行时间，这些是性能瓶颈的关键，不要只看执行计划的运算符图标
• 对比多计划差异：重点看索引使用、连接类型（嵌套循环/哈希匹配/合并连接）、并行度、谓词推送逻辑，找出高效计划的优化点
• 排查参数嗅探：如果存储过程因参数值不同性能波动，确认最优计划是否适配常用参数场景
• 结合实际执行计划：如果Query Store的计划信息不够详细，直接执行存储过程并查看实际执行计划，对比预估行数和实际行数的差异，判断统计信息是否过期

四、Query Store高效使用技巧 #

• 合理配置捕获策略：根据需求设置捕获模式（比如自动捕获所有查询，或仅捕获开销大的查询），避免存储溢出
• 定期清理旧数据：使用sp_query_store_flush_db手动刷新数据，或设置自动清理策略
• 活用内置报表：用【顶级资源消耗查询】【查询性能趋势】【计划回归】报表快速定位问题，不用自己写复杂查询
• 监控计划回归：当存储过程性能下降时，检查是否有新计划生成，对比新旧计划的性能差异
• 配合扩展事件：如果Query Store信息不足，可搭配扩展事件捕获更详细的查询执行数据

内容的提问来源于stack exchange，提问作者Mayur