如何在SQL Server 2016查询存储中识别回归查询？

SQL Server 2017引入的sys.dm_db_tuning_recommendations视图在2016版本中不可用，但可以通过查询存储的核心DMV组合查询，实现识别执行计划回归、对比最优与问题计划的需求。以下是针对拥有多执行计划的回归查询的分析脚本，包含丰富的运行统计与对比信息：

核心分析脚本 #

WITH QueryPlanStats AS (
    SELECT
        qsq.query_id,
        qsqt.query_text_id,
        qsqt.query_sql_text,
        qsp.plan_id,
        qsp.is_forced_plan,
        qsp.plan_type_desc,
        qsp.parameterization_type_desc,
        qsrs.execution_type_desc,
        -- 核心运行统计指标
        qsrs.count_executions,
        qsrs.avg_cpu_time,
        qsrs.total_cpu_time,
        qsrs.avg_logical_io_reads,
        qsrs.total_logical_io_reads,
        qsrs.avg_duration,
        qsrs.total_duration,
        -- 计划生命周期信息
        qsp.last_compile_time,
        qsp.last_execution_time,
        -- 计算当前查询的最优基准（以平均CPU耗时最低为标准，可按需替换为读/持续时间）
        MIN(qsrs.avg_cpu_time) OVER (PARTITION BY qsq.query_id) AS baseline_avg_cpu,
        MIN(qsrs.avg_logical_io_reads) OVER (PARTITION BY qsq.query_id) AS baseline_avg_reads
    FROM
        sys.query_store_query qsq
    INNER JOIN
        sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
    INNER JOIN
        sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
    INNER JOIN
        sys.query_store_runtime_stats qsrs ON qsp.plan_id = qsrs.plan_id
    WHERE
        qsrs.count_executions >= 10 -- 过滤执行次数过少的噪音计划
        AND qsq.is_internal_query = 0 -- 排除系统内部自动生成的查询
),
MultiPlanQueries AS (
    -- 筛选出拥有2个及以上执行计划的查询
    SELECT
        query_id,
        COUNT(DISTINCT plan_id) AS total_plan_count
    FROM
        QueryPlanStats
    GROUP BY
        query_id
    HAVING
        COUNT(DISTINCT plan_id) >= 2
)
SELECT
    qps.query_id,
    qps.query_sql_text,
    qps.plan_id,
    qps.is_forced_plan,
    qps.plan_type_desc,
    qps.parameterization_type_desc,
    qps.execution_type_desc,
    qps.count_executions,
    -- 格式化数值提升可读性
    FORMAT(qps.avg_cpu_time / 1000, 'N2') AS avg_cpu_time_ms,
    FORMAT(qps.total_cpu_time / 1000, 'N2') AS total_cpu_time_ms,
    FORMAT(qps.avg_logical_io_reads, 'N0') AS avg_logical_reads,
    FORMAT(qps.total_logical_io_reads, 'N0') AS total_logical_reads,
    FORMAT(qps.avg_duration / 1000, 'N2') AS avg_duration_ms,
    FORMAT(qps.total_duration / 1000, 'N2') AS total_duration_ms,
    qps.last_compile_time,
    qps.last_execution_time,
    -- 计算相对于最优计划的资源消耗涨幅，突出回归计划
    FORMAT((qps.avg_cpu_time / qps.baseline_avg_cpu) * 100 - 100, 'N2') AS cpu_over_baseline_pct,
    FORMAT((qps.avg_logical_io_reads / NULLIF(qps.baseline_avg_reads, 0)) * 100 - 100, 'N2') AS reads_over_baseline_pct,
    -- 生成强制最优计划的脚本（仅针对资源消耗高于基准的计划）
    CASE
        WHEN qps.avg_cpu_time > qps.baseline_avg_cpu THEN
            'EXEC sp_query_store_force_plan @query_id = ' + CAST(qps.query_id AS VARCHAR(20)) + ', @plan_id = ' + CAST((SELECT TOP 1 plan_id FROM QueryPlanStats WHERE query_id = qps.query_id AND avg_cpu_time = baseline_avg_cpu) AS VARCHAR(20)) + ';'
        ELSE NULL
    END AS force_baseline_plan_script
FROM
    QueryPlanStats qps
INNER JOIN
    MultiPlanQueries mpq ON qps.query_id = mpq.query_id
ORDER BY
    qps.query_id,
    cpu_over_baseline_pct DESC -- 按CPU涨幅从高到低排序，优先排查最严重的回归

脚本说明与使用技巧 #

• 基准计划定义：脚本默认以平均CPU耗时最低作为最优计划的判断标准，若更关注IO或执行时长，可将MIN(qsrs.avg_cpu_time)替换为MIN(qsrs.avg_logical_io_reads)或MIN(qsrs.avg_duration)
• 过滤条件调整：可根据业务场景修改count_executions >=10的阈值，过滤掉测试类或低频次的查询
• 回归判断：重点关注cpu_over_baseline_pct或reads_over_baseline_pct为正数且数值较大的记录，这类计划的资源消耗远高于同查询的最优计划，大概率是执行计划回归或参数嗅探导致的问题
• 快速修复：对于确认的问题计划，可直接执行生成的force_baseline_plan_script脚本，强制查询使用最优计划

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