query-store-investigation

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Query Store Performance Investigation

Query Store 性能调查

Use this skill when the user asks about query performance analysis, top resource-consuming queries, or recently regressed queries and Query Store is enabled.
当用户询问查询性能分析、资源消耗最高的查询或近期性能回归查询,并且 Query Store 已启用 时,使用此 skill。

Scope

范围

  • Covers top resource-consuming queries (duration, CPU, logical IO), regressed queries found by comparing a recent runtime window with a historical baseline, and the best historical plan versus the current plan.
  • For volatile or intermittent queries, one query_id's interval-by-interval drill-down, per-query waits, aborted executions, forced-plan failures, or parameter sensitivity, use
    query-store-volatile-historical-slow-queries-comparison
    .
  • 涵盖资源消耗最高的查询(耗时、CPU、逻辑 IO)、通过将近期运行时窗口与历史基线进行比较而发现的性能回归查询,以及最佳历史计划与当前计划的对比。
  • 对于波动性或间歇性查询、针对单个 query_id 逐时间间隔下钻、按查询统计的等待事件、中止的执行、强制计划失败或参数敏感性,请使用
    query-store-volatile-historical-slow-queries-comparison
    。

Required tools

必需工具

  • A SQL query tool connected to the target server and database. Prefer, in order: the SQL Tools MCP server that ships with the
    microsoft-sql-admin
    plugin (
    mssql_list_database_connections
    ,
    mssql_connect_database
    , then
    mssql_execute_query
    ); any other SQL tool the agent host provides for running T-SQL and reading result sets;
    sqlcmd
    from a terminal. If the SQL Tools MCP server is not connected or lists no connections, follow the
    sql-tools-mcp-setup
    skill, which asks before installing or configuring anything. If no SQL tool can be made available, give the user the T-SQL to run and ask for the results.
With the SQL Tools MCP server, run these queries through
mssql_execute_query
. To inspect a plan, pass its
plan_id
as
queryStorePlanId
to
SummarizePlan
,
GetPlanWarnings
or
PlanBottleneckAnalysis
rather than returning showplan XML. One plan-tool call per plan; do not re-run the workload to compare plans.
  • 连接到目标服务器和数据库的 SQL 查询工具。首选顺序为:随
    microsoft-sql-admin
    插件提供的 SQL Tools MCP server(
    mssql_list_database_connections
    、
    mssql_connect_database
    ,然后是
    mssql_execute_query
    );Agent 宿主提供的、用于运行 T-SQL 和读取结果集的任何其他 SQL 工具; 终端中的
    sqlcmd
    。如果 SQL Tools MCP server 未连接或未列出任何连接,请遵循
    sql-tools-mcp-setup
    skill,该 skill 会在安装或配置任何内容前先行询问。如果无法提供任何 SQL 工具,请向用户提供可运行的 T-SQL,并要求其返回结果。
使用 SQL Tools MCP server 时,通过
mssql_execute_query
运行这些查询。要检查某个计划,请将其
plan_id
作为
queryStorePlanId
传给
SummarizePlan
、
GetPlanWarnings
或
PlanBottleneckAnalysis
,而不是返回 showplan XML。每个计划只能调用一次计划工具;不要为了比较计划而重新运行工作负载。

Parameters

参数

ParameterDefaultDescription
TopN25Maximum number of rows to return per query
HistDaysBack1Days back for the historical baseline window
CurrHoursBack1Hours back for the current (recent) window
AvgCpuTime1000Minimum avg CPU time (µs) to include a query
MinExecCount3Minimum execution count to include a query
RegressionFactor2Only return queries where current avg CPU > best historical avg CPU × this multiplier
参数默认值说明
TopN25每个查询返回的最大行数
HistDaysBack1历史基线窗口回溯的天数
CurrHoursBack1当前(近期)窗口回溯的小时数
AvgCpuTime1000纳入查询所需的最小平均 CPU 时间(µs)
MinExecCount3纳入查询所需的最小执行次数
RegressionFactor2仅返回当前平均 CPU > 最佳历史平均 CPU × 此乘数的查询

Best Practices

最佳实践

  1. By default, only get the first 100 chars of query text.
  2. Don't return showplan XML unless specifically asked — plans can be very large.
  3. Preserve
    datetimeoffset
    boundaries when filtering Query Store timestamps. Casting them to
    datetime
    , or comparing with local
    GETDATE()
    , shifts windows on non-UTC SQL Server hosts.
  4. Confirmation gate for state-changing commands.
    sp_query_store_force_plan
    ,
    sp_query_store_unforce_plan
    ,
    sp_query_store_remove_plan
    ,
    sp_query_store_set_hints
    ,
    sp_query_store_clear_hints
    , and
    sp_query_store_remove_query
    change Query Store state and query behavior. Present them as recommendations only. Never run any of them without the user's explicit confirmation for that specific command, and explain its impact and how to roll it back first. The investigation queries in this skill are read-only.
  5. A historical plan can be forced only while every index it uses still exists. If the plan names an index that was dropped, renamed or disabled since it was compiled (often by the release that caused the regression),
    sp_query_store_force_plan
    succeeds but the plan is never applied (
    NO_INDEX
    , 8712). Before you recommend forcing a plan, run the read-only check under
    ForceBestHistPlanCmd
    below; when it returns rows, forcing is not a fix.
  1. 默认只获取查询文本的前 100 个字符。
  2. 除非用户明确要求,否则不要返回 showplan XML——计划可能非常大。
  3. 筛选 Query Store 时间戳时,应保留
    datetimeoffset
    边界。将其转换为
    datetime
    ,或与本地
    GETDATE()
    比较,都会在非 UTC 的 SQL Server 主机上使时间窗口发生偏移。
  4. 状态变更命令的确认门禁。
    sp_query_store_force_plan
    、
    sp_query_store_unforce_plan
    、
    sp_query_store_remove_plan
    、
    sp_query_store_set_hints
    、
    sp_query_store_clear_hints
    和
    sp_query_store_remove_query
    会更改 Query Store 状态和查询行为。只能将它们作为建议呈现。未经用户针对该特定命令的明确确认,绝不执行其中任何一条,并应先说明其影响以及回滚方式。本 skill 中的调查查询均为只读查询。
  5. 只有在其使用的每个索引仍然存在时,才能强制使用历史计划。 如果计划引用了自编译后已被删除、重命名或禁用的索引(通常发生在导致性能回归的版本发布中),
    sp_query_store_force_plan
    会执行成功,但该计划始终不会被应用(
    NO_INDEX
    ,8712)。在建议强制使用计划之前,请运行下文
    ForceBestHistPlanCmd
    下的只读检查;如果检查返回任何行,则强制使用该计划并不能解决问题。

Example Queries

示例查询

Top Resource-Consuming Queries (Last Hour)

资源消耗最高的查询(过去一小时)

sql
SELECT TOP 10
    ([rs].[avg_duration]) [AvgDuration],
    SUM([rs].[count_executions]) [TotalExecutions],
    [qsq].[query_id],
    LEFT([qst].[query_sql_text], 100) AS query_text_snippet,
    CASE
        WHEN [qsq].[object_id] = 0 THEN N'Ad-hoc'
        ELSE OBJECT_NAME([qsq].[object_id])
    END AS [ObjectName]
FROM [sys].[query_store_query] [qsq]
JOIN [sys].[query_store_query_text] [qst] ON [qsq].[query_text_id] = [qst].[query_text_id]
JOIN [sys].[query_store_plan] [qsp] ON [qsq].[query_id] = [qsp].[query_id]
JOIN [sys].[query_store_runtime_stats] [rs] ON [qsp].[plan_id] = [rs].[plan_id]
WHERE [rs].[last_execution_time] > DATEADD(HOUR, -1, GETUTCDATE())
    AND [rs].[execution_type] = 0
GROUP BY [rs].[avg_duration], [qsq].[query_id], [qst].[query_sql_text], [qsq].[object_id]
ORDER BY ([rs].[avg_duration]) DESC
OPTION (MAXDOP 1);
sql
SELECT TOP 10
    ([rs].[avg_duration]) [AvgDuration],
    SUM([rs].[count_executions]) [TotalExecutions],
    [qsq].[query_id],
    LEFT([qst].[query_sql_text], 100) AS query_text_snippet,
    CASE
        WHEN [qsq].[object_id] = 0 THEN N'Ad-hoc'
        ELSE OBJECT_NAME([qsq].[object_id])
    END AS [ObjectName]
FROM [sys].[query_store_query] [qsq]
JOIN [sys].[query_store_query_text] [qst] ON [qsq].[query_text_id] = [qst].[query_text_id]
JOIN [sys].[query_store_plan] [qsp] ON [qsq].[query_id] = [qsp].[query_id]
JOIN [sys].[query_store_runtime_stats] [rs] ON [qsp].[plan_id] = [rs].[plan_id]
WHERE [rs].[last_execution_time] > DATEADD(HOUR, -1, GETUTCDATE())
    AND [rs].[execution_type] = 0
GROUP BY [rs].[avg_duration], [qsq].[query_id], [qst].[query_sql_text], [qsq].[object_id]
ORDER BY ([rs].[avg_duration]) DESC
OPTION (MAXDOP 1);

Top Queries by Logical IO (Last Hour)

按逻辑 IO 统计的资源消耗最高查询(过去一小时)

sql
SELECT TOP 10
    ([rs].[avg_logical_io_reads]) [AvgLogicalIO],
    SUM([rs].[count_executions]) [TotalExecutions],
    [qsq].[query_id],
    LEFT([qst].[query_sql_text], 100) AS query_text_snippet,
    CASE
        WHEN [qsq].[object_id] = 0 THEN N'Ad-hoc'
        ELSE OBJECT_NAME([qsq].[object_id])
    END AS [ObjectName]
FROM [sys].[query_store_query] [qsq]
JOIN [sys].[query_store_query_text] [qst] ON [qsq].[query_text_id] = [qst].[query_text_id]
JOIN [sys].[query_store_plan] [qsp] ON [qsq].[query_id] = [qsp].[query_id]
JOIN [sys].[query_store_runtime_stats] [rs] ON [qsp].[plan_id] = [rs].[plan_id]
WHERE [rs].[last_execution_time] > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY [rs].[avg_logical_io_reads], [qsq].[query_id], [qst].[query_sql_text], [qsq].[object_id]
ORDER BY ([rs].[avg_logical_io_reads]) DESC
OPTION (MAXDOP 1);
sql
SELECT TOP 10
    ([rs].[avg_logical_io_reads]) [AvgLogicalIO],
    SUM([rs].[count_executions]) [TotalExecutions],
    [qsq].[query_id],
    LEFT([qst].[query_sql_text], 100) AS query_text_snippet,
    CASE
        WHEN [qsq].[object_id] = 0 THEN N'Ad-hoc'
        ELSE OBJECT_NAME([qsq].[object_id])
    END AS [ObjectName]
FROM [sys].[query_store_query] [qsq]
JOIN [sys].[query_store_query_text] [qst] ON [qsq].[query_text_id] = [qst].[query_text_id]
JOIN [sys].[query_store_plan] [qsp] ON [qsq].[query_id] = [qsp].[query_id]
JOIN [sys].[query_store_runtime_stats] [rs] ON [qsp].[plan_id] = [rs].[plan_id]
WHERE [rs].[last_execution_time] > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY [rs].[avg_logical_io_reads], [qsq].[query_id], [qst].[query_sql_text], [qsq].[object_id]
ORDER BY ([rs].[avg_logical_io_reads]) DESC
OPTION (MAXDOP 1);

Recently Regressed Queries (Last Hour vs. Prior 24 Hours)

近期性能回归查询(过去一小时与之前 24 小时对比)

sql
WITH Last24Hours AS (
    SELECT
        qsq.query_id,
        LEFT(qst.query_sql_text, 100) AS query_text_snippet,
        CASE WHEN qsq.object_id = 0 THEN N'Ad-hoc' ELSE OBJECT_NAME(qsq.object_id) END AS object_name,
        AVG(rs.avg_duration) AS avg_duration,
        AVG(rs.avg_logical_io_reads) AS avg_logical_io_reads,
        SUM(rs.count_executions) AS total_executions
    FROM sys.query_store_query qsq
    JOIN sys.query_store_query_text qst ON qsq.query_text_id = qst.query_text_id
    JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
    JOIN sys.query_store_runtime_stats rs ON qsp.plan_id = rs.plan_id
    WHERE rs.last_execution_time BETWEEN DATEADD(hour, -25, SYSDATETIMEOFFSET()) AND DATEADD(hour, -1, SYSDATETIMEOFFSET())
    GROUP BY qsq.query_id, qst.query_sql_text, qsq.object_id
),
LastHour AS (
    SELECT
        qsq.query_id,
        LEFT(qst.query_sql_text, 100) AS query_text_snippet,
        CASE WHEN qsq.object_id = 0 THEN N'Ad-hoc' ELSE OBJECT_NAME(qsq.object_id) END AS object_name,
        AVG(rs.avg_duration) AS avg_duration,
        AVG(rs.avg_logical_io_reads) AS avg_logical_io_reads,
        SUM(rs.count_executions) AS total_executions
    FROM sys.query_store_query qsq
    JOIN sys.query_store_query_text qst ON qsq.query_text_id = qst.query_text_id
    JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
    JOIN sys.query_store_runtime_stats rs ON qsp.plan_id = rs.plan_id
    WHERE rs.last_execution_time > DATEADD(hour, -1, SYSDATETIMEOFFSET())
    GROUP BY qsq.query_id, qst.query_sql_text, qsq.object_id
)
SELECT TOP ({{TopN}})
    l24.query_id, l24.query_text_snippet, l24.object_name,
    l24.avg_duration AS last_24h_avg_duration,
    lh.avg_duration AS last_hour_avg_duration,
    l24.avg_logical_io_reads AS last_24h_avg_io,
    lh.avg_logical_io_reads AS last_hour_avg_io,
    l24.total_executions AS last_24h_executions,
    lh.total_executions AS last_hour_executions,
    CASE WHEN l24.avg_duration = 0 THEN 0
         ELSE ((lh.avg_duration - l24.avg_duration) / l24.avg_duration) * 100
    END AS duration_change_pct,
    CASE WHEN l24.avg_logical_io_reads = 0 THEN 0
         ELSE ((lh.avg_logical_io_reads - l24.avg_logical_io_reads) / l24.avg_logical_io_reads) * 100
    END AS io_change_pct
FROM Last24Hours l24
JOIN LastHour lh ON l24.query_id = lh.query_id
WHERE CASE WHEN l24.avg_duration = 0 THEN 0
           ELSE ((lh.avg_duration - l24.avg_duration) / l24.avg_duration) * 100 END > 10
   OR CASE WHEN l24.avg_logical_io_reads = 0 THEN 0
           ELSE ((lh.avg_logical_io_reads - l24.avg_logical_io_reads) / l24.avg_logical_io_reads) * 100 END > 10
ORDER BY duration_change_pct DESC, io_change_pct DESC
OPTION (MAXDOP 1);
sql
WITH Last24Hours AS (
    SELECT
        qsq.query_id,
        LEFT(qst.query_sql_text, 100) AS query_text_snippet,
        CASE WHEN qsq.object_id = 0 THEN N'Ad-hoc' ELSE OBJECT_NAME(qsq.object_id) END AS object_name,
        AVG(rs.avg_duration) AS avg_duration,
        AVG(rs.avg_logical_io_reads) AS avg_logical_io_reads,
        SUM(rs.count_executions) AS total_executions
    FROM sys.query_store_query qsq
    JOIN sys.query_store_query_text qst ON qsq.query_text_id = qst.query_text_id
    JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
    JOIN sys.query_store_runtime_stats rs ON qsp.plan_id = rs.plan_id
    WHERE rs.last_execution_time BETWEEN DATEADD(hour, -25, SYSDATETIMEOFFSET()) AND DATEADD(hour, -1, SYSDATETIMEOFFSET())
    GROUP BY qsq.query_id, qst.query_sql_text, qsq.object_id
),
LastHour AS (
    SELECT
        qsq.query_id,
        LEFT(qst.query_sql_text, 100) AS query_text_snippet,
        CASE WHEN qsq.object_id = 0 THEN N'Ad-hoc' ELSE OBJECT_NAME(qsq.object_id) END AS object_name,
        AVG(rs.avg_duration) AS avg_duration,
        AVG(rs.avg_logical_io_reads) AS avg_logical_io_reads,
        SUM(rs.count_executions) AS total_executions
    FROM sys.query_store_query qsq
    JOIN sys.query_store_query_text qst ON qsq.query_text_id = qst.query_text_id
    JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
    JOIN sys.query_store_runtime_stats rs ON qsp.plan_id = rs.plan_id
    WHERE rs.last_execution_time > DATEADD(hour, -1, SYSDATETIMEOFFSET())
    GROUP BY qsq.query_id, qst.query_sql_text, qsq.object_id
)
SELECT TOP ({{TopN}})
    l24.query_id, l24.query_text_snippet, l24.object_name,
    l24.avg_duration AS last_24h_avg_duration,
    lh.avg_duration AS last_hour_avg_duration,
    l24.avg_logical_io_reads AS last_24h_avg_io,
    lh.avg_logical_io_reads AS last_hour_avg_io,
    l24.total_executions AS last_24h_executions,
    lh.total_executions AS last_hour_executions,
    CASE WHEN l24.avg_duration = 0 THEN 0
         ELSE ((lh.avg_duration - l24.avg_duration) / l24.avg_duration) * 100
    END AS duration_change_pct,
    CASE WHEN l24.avg_logical_io_reads = 0 THEN 0
         ELSE ((lh.avg_logical_io_reads - l24.avg_logical_io_reads) / l24.avg_logical_io_reads) * 100
    END AS io_change_pct
FROM Last24Hours l24
JOIN LastHour lh ON l24.query_id = lh.query_id
WHERE CASE WHEN l24.avg_duration = 0 THEN 0
           ELSE ((lh.avg_duration - l24.avg_duration) / l24.avg_duration) * 100 END > 10
   OR CASE WHEN l24.avg_logical_io_reads = 0 THEN 0
           ELSE ((lh.avg_logical_io_reads - l24.avg_logical_io_reads) / l24.avg_logical_io_reads) * 100 END > 10
ORDER BY duration_change_pct DESC, io_change_pct DESC
OPTION (MAXDOP 1);

Regressed Queries with Best Plan Recommendation

带最佳计划建议的性能回归查询

Identifies queries whose CPU has regressed in a recent window compared to a historical baseline, and surfaces the best historical plan to force as a remediation option. Returns
RemoveCurrentPlanCmd
and
ForceBestHistPlanCmd
columns as generated command text for the user to review; the query itself does not change Query Store (it only uses session temp tables) and never runs them.
Note on window overlap: the historical window spans the full
{{HistDaysBack}}
day(s) and intentionally overlaps with the current window. This provides a robust full-day CPU baseline that includes recent hours.
sql
-- Time windows derived from parameters
DECLARE @StartTime     DATETIMEOFFSET = DATEADD(HOUR, -{{CurrHoursBack}}, SYSDATETIMEOFFSET());
DECLARE @EndTime       DATETIMEOFFSET = SYSDATETIMEOFFSET();
DECLARE @HistStartTime DATETIMEOFFSET = DATEADD(DAY,  -{{HistDaysBack}},  SYSDATETIMEOFFSET());
DECLARE @HistEndTime   DATETIMEOFFSET = DATEADD(MILLISECOND, -3, DATEADD(DAY, {{HistDaysBack}}, @HistStartTime));

DROP TABLE IF EXISTS #CurrentStats, #HistStats, #HistPlans,
    #BestHistPlan30Days, #CurrentPlans, #TotalCpuCurrentInterval;

-- Current stats: aggregated CPU over the recent window
SELECT TOP ({{TopN}})
    p.query_id,
    SUM(rs.count_executions * rs.avg_cpu_time) / SUM(rs.count_executions) AS AvgCpuTime,
    SUM(rs.count_executions)                                               AS ExecCount,
    AVG(rs.count_executions * rs.avg_cpu_time)                            AS AvgTotallExTime,
    COUNT(DISTINCT rs.runtime_stats_interval_id)                          AS QtyIntervals
INTO #CurrentStats
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
JOIN sys.query_store_plan p ON rs.plan_id = p.plan_id
WHERE rsi.start_time BETWEEN @StartTime AND @EndTime
  AND rs.count_executions > {{MinExecCount}}
  AND rs.avg_cpu_time     > {{AvgCpuTime}}
GROUP BY p.query_id
OPTION (MAXDOP 1);

-- Total CPU across all plans in current window (used for % of total calculation)
SELECT SUM(rs.avg_cpu_time * rs.count_executions) AS TotalCpuTimeCurrentWindow
INTO #TotalCpuCurrentInterval
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 BETWEEN @StartTime AND @EndTime
OPTION (MAXDOP 1);

-- Most recent plan used per query in the current window
SELECT
    p.query_id,
    p.plan_id AS CurrentPlanId,
    AVG(rs.count_executions * rs.avg_cpu_time) AS PlanAvgTotallExTime,
    ROW_NUMBER() OVER (PARTITION BY p.query_id ORDER BY MAX(rs.last_execution_time) DESC) AS rn
INTO #CurrentPlans
FROM sys.query_store_plan p
JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.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 BETWEEN @StartTime AND @EndTime
GROUP BY p.query_id, p.plan_id
OPTION (MAXDOP 1);

-- Historical stats over the baseline window
SELECT
    p.query_id,
    SUM(rs.avg_cpu_time * rs.count_executions) / SUM(rs.count_executions) AS HistAvgCpuTime,
    SUM(rs.count_executions)                                               AS ExecCount,
    AVG(rs.count_executions * rs.avg_cpu_time)                            AS HistAvgTotallExTime,
    COUNT(DISTINCT rs.runtime_stats_interval_id)                          AS HistQtyIntervals
INTO #HistStats
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
JOIN sys.query_store_plan p ON rs.plan_id = p.plan_id
WHERE rsi.start_time BETWEEN @HistStartTime AND @HistEndTime
  AND rs.count_executions > {{MinExecCount}}
  AND rs.avg_cpu_time     > {{AvgCpuTime}}
GROUP BY p.query_id
OPTION (MAXDOP 1);

-- Best historical plan per query over the last 30 days (lowest avg CPU)
SELECT t.query_id, t.plan_id AS BestHistPlanId, t.PlanAvgCpuTime
INTO #BestHistPlan30Days
FROM (
    SELECT
        p.query_id,
        rs.plan_id,
        SUM(rs.avg_cpu_time * rs.count_executions) / SUM(rs.count_executions) AS PlanAvgCpuTime,
        ROW_NUMBER() OVER (
            PARTITION BY p.query_id
            ORDER BY SUM(rs.avg_cpu_time * rs.count_executions) / SUM(rs.count_executions) ASC
        ) AS rn
    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
    JOIN sys.query_store_plan p ON rs.plan_id = p.plan_id
    WHERE rsi.start_time > DATEADD(DAY, -30, SYSDATETIMEOFFSET())
    GROUP BY p.query_id, rs.plan_id
) t
JOIN (
    SELECT query_id FROM #CurrentStats
    UNION
    SELECT query_id FROM #HistStats
) qId ON t.query_id = qId.query_id
WHERE t.rn = 1
OPTION (MAXDOP 1, RECOMPILE);

-- Final output: regressed queries with plan remediation commands
SELECT TOP ({{TopN}})
    cs.query_id,
    cp.CurrentPlanId,
    CAST(
        (cp.PlanAvgTotallExTime * 100.0)
        / (SELECT TotalCpuTimeCurrentWindow FROM #TotalCpuCurrentInterval)
        AS DECIMAL(10, 2)
    )                                                                  AS BadPlanCpuPctOfTotal,
    cs.ExecCount                                                       AS CurrentExecCount,
    hs.ExecCount                                                       AS HistExecCount,
    cs.ExecCount / cs.QtyIntervals                                     AS CurrentExecPerInterval,
    hs.ExecCount / hs.HistQtyIntervals                                 AS HistExecPerInterval,
    CAST(cs.AvgTotallExTime  / 1000 AS BIGINT)                        AS CurrentAvgTotalCpuMS,
    CAST(hs.HistAvgTotallExTime / 1000 AS BIGINT)                     AS HistAvgTotalCpuMS,
    CAST(cs.AvgCpuTime       / 1000 AS BIGINT)                        AS CurrentAvgCpuMS,
    CAST(bh.PlanAvgCpuTime   / 1000 AS BIGINT)                        AS HistAvgCpuMS,
    CAST(cs.AvgCpuTime * 1.0 / bh.PlanAvgCpuTime AS DECIMAL(10, 2))  AS RegressionFactor,
    q.query_hash,
    qt.query_sql_text                                                  AS QuerySQLText,
    'EXEC sp_query_store_remove_plan @plan_id = '
        + CAST(cp.CurrentPlanId AS NVARCHAR(100))                     AS RemoveCurrentPlanCmd,
    'EXEC sp_query_store_force_plan @query_id = '
        + CAST(cs.query_id AS NVARCHAR(100))
        + ', @plan_id = '
        + CAST(bh.BestHistPlanId AS NVARCHAR(100))                    AS ForceBestHistPlanCmd,
    bh.BestHistPlanId
FROM #CurrentStats cs
JOIN #HistStats hs          ON cs.query_id = hs.query_id
JOIN #BestHistPlan30Days bh ON cs.query_id = bh.query_id
JOIN #CurrentPlans cp       ON cs.query_id = cp.query_id AND cp.rn = 1
JOIN sys.query_store_query q ON cs.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE cs.AvgCpuTime > bh.PlanAvgCpuTime * {{RegressionFactor}}
ORDER BY BadPlanCpuPctOfTotal DESC
OPTION (MAXDOP 1);

-- Cleanup
DROP TABLE IF EXISTS #CurrentStats, #HistStats, #HistPlans,
    #BestHistPlan30Days, #CurrentPlans, #TotalCpuCurrentInterval;
找出相较于历史基线,CPU 在近期窗口中出现性能回归的查询,并给出可作为修复选项而强制使用的最佳历史计划。返回
RemoveCurrentPlanCmd
和
ForceBestHistPlanCmd
列,其中包含供用户审核的生成命令文本;查询本身不会更改 Query Store(仅使用会话临时表),也绝不会执行这些命令。
关于窗口重叠的说明: 历史窗口覆盖完整的
{{HistDaysBack}}
天,并且有意与当前窗口重叠。这样可以建立稳健的完整每日 CPU 基线,其中包含最近几个小时的执行情况。
sql
-- Time windows derived from parameters
DECLARE @StartTime     DATETIMEOFFSET = DATEADD(HOUR, -{{CurrHoursBack}}, SYSDATETIMEOFFSET());
DECLARE @EndTime       DATETIMEOFFSET = SYSDATETIMEOFFSET();
DECLARE @HistStartTime DATETIMEOFFSET = DATEADD(DAY,  -{{HistDaysBack}},  SYSDATETIMEOFFSET());
DECLARE @HistEndTime   DATETIMEOFFSET = DATEADD(MILLISECOND, -3, DATEADD(DAY, {{HistDaysBack}}, @HistStartTime));

DROP TABLE IF EXISTS #CurrentStats, #HistStats, #HistPlans,
    #BestHistPlan30Days, #CurrentPlans, #TotalCpuCurrentInterval;

-- Current stats: aggregated CPU over the recent window
SELECT TOP ({{TopN}})
    p.query_id,
    SUM(rs.count_executions * rs.avg_cpu_time) / SUM(rs.count_executions) AS AvgCpuTime,
    SUM(rs.count_executions)                                               AS ExecCount,
    AVG(rs.count_executions * rs.avg_cpu_time)                            AS AvgTotallExTime,
    COUNT(DISTINCT rs.runtime_stats_interval_id)                          AS QtyIntervals
INTO #CurrentStats
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
JOIN sys.query_store_plan p ON rs.plan_id = p.plan_id
WHERE rsi.start_time BETWEEN @StartTime AND @EndTime
  AND rs.count_executions > {{MinExecCount}}
  AND rs.avg_cpu_time     > {{AvgCpuTime}}
GROUP BY p.query_id
OPTION (MAXDOP 1);

-- Total CPU across all plans in current window (used for % of total calculation)
SELECT SUM(rs.avg_cpu_time * rs.count_executions) AS TotalCpuTimeCurrentWindow
INTO #TotalCpuCurrentInterval
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 BETWEEN @StartTime AND @EndTime
OPTION (MAXDOP 1);

-- Most recent plan used per query in the current window
SELECT
    p.query_id,
    p.plan_id AS CurrentPlanId,
    AVG(rs.count_executions * rs.avg_cpu_time) AS PlanAvgTotallExTime,
    ROW_NUMBER() OVER (PARTITION BY p.query_id ORDER BY MAX(rs.last_execution_time) DESC) AS rn
INTO #CurrentPlans
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 rsi.start_time BETWEEN @StartTime AND @EndTime
GROUP BY p.query_id, p.plan_id
OPTION (MAXDOP 1);

-- Historical stats over the baseline window
SELECT
    p.query_id,
    SUM(rs.avg_cpu_time * rs.count_executions) / SUM(rs.count_executions) AS HistAvgCpuTime,
    SUM(rs.count_executions)                                               AS ExecCount,
    AVG(rs.count_executions * rs.avg_cpu_time)                            AS HistAvgTotallExTime,
    COUNT(DISTINCT rs.runtime_stats_interval_id)                          AS HistQtyIntervals
INTO #HistStats
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
JOIN sys.query_store_plan p ON rs.plan_id = p.plan_id
WHERE rsi.start_time BETWEEN @HistStartTime AND @HistEndTime
  AND rs.count_executions > {{MinExecCount}}
  AND rs.avg_cpu_time     > {{AvgCpuTime}}
GROUP BY p.query_id
OPTION (MAXDOP 1);

-- Best historical plan per query over the last 30 days (lowest avg CPU)
SELECT t.query_id, t.plan_id AS BestHistPlanId, t.PlanAvgCpuTime
INTO #BestHistPlan30Days
FROM (
    SELECT
        p.query_id,
        rs.plan_id,
        SUM(rs.avg_cpu_time * rs.count_executions) / SUM(rs.count_executions) AS PlanAvgCpuTime,
        ROW_NUMBER() OVER (
            PARTITION BY p.query_id
            ORDER BY SUM(rs.avg_cpu_time * rs.count_executions) / SUM(rs.count_executions) ASC
        ) AS rn
    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
    JOIN sys.query_store_plan p ON rs.plan_id = p.plan_id
    WHERE rsi.start_time > DATEADD(DAY, -30, SYSDATETIMEOFFSET())
    GROUP BY p.query_id, rs.plan_id
) t
JOIN (
    SELECT query_id FROM #CurrentStats
    UNION
    SELECT query_id FROM #HistStats
) qId ON t.query_id = qId.query_id
WHERE t.rn = 1
OPTION (MAXDOP 1, RECOMPILE);

-- Final output: regressed queries with plan remediation commands
SELECT TOP ({{TopN}})
    cs.query_id,
    cp.CurrentPlanId,
    CAST(
        (cp.PlanAvgTotallExTime * 100.0)
        / (SELECT TotalCpuTimeCurrentWindow FROM #TotalCpuCurrentInterval)
        AS DECIMAL(10, 2)
    )                                                                  AS BadPlanCpuPctOfTotal,
    cs.ExecCount                                                       AS CurrentExecCount,
    hs.ExecCount                                                       AS HistExecCount,
    cs.ExecCount / cs.QtyIntervals                                     AS CurrentExecPerInterval,
    hs.ExecCount / hs.HistQtyIntervals                                 AS HistExecPerInterval,
    CAST(cs.AvgTotallExTime  / 1000 AS BIGINT)                        AS CurrentAvgTotalCpuMS,
    CAST(hs.HistAvgTotallExTime / 1000 AS BIGINT)                     AS HistAvgTotalCpuMS,
    CAST(cs.AvgCpuTime       / 1000 AS BIGINT)                        AS CurrentAvgCpuMS,
    CAST(bh.PlanAvgCpuTime   / 1000 AS BIGINT)                        AS HistAvgCpuMS,
    CAST(cs.AvgCpuTime * 1.0 / bh.PlanAvgCpuTime AS DECIMAL(10, 2))  AS RegressionFactor,
    q.query_hash,
    qt.query_sql_text                                                  AS QuerySQLText,
    'EXEC sp_query_store_remove_plan @plan_id = '
        + CAST(cp.CurrentPlanId AS NVARCHAR(100))                     AS RemoveCurrentPlanCmd,
    'EXEC sp_query_store_force_plan @query_id = '
        + CAST(cs.query_id AS NVARCHAR(100))
        + ', @plan_id = '
        + CAST(bh.BestHistPlanId AS NVARCHAR(100))                    AS ForceBestHistPlanCmd,
    bh.BestHistPlanId
FROM #CurrentStats cs
JOIN #HistStats hs          ON cs.query_id = hs.query_id
JOIN #BestHistPlan30Days bh ON cs.query_id = bh.query_id
JOIN #CurrentPlans cp       ON cs.query_id = cp.query_id AND cp.rn = 1
JOIN sys.query_store_query q ON cs.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE cs.AvgCpuTime > bh.PlanAvgCpuTime * {{RegressionFactor}}
ORDER BY BadPlanCpuPctOfTotal DESC
OPTION (MAXDOP 1);

-- Cleanup
DROP TABLE IF EXISTS #CurrentStats, #HistStats, #HistPlans,
    #BestHistPlan30Days, #CurrentPlans, #TotalCpuCurrentInterval;

Plan Remediation Commands

查询计划修复命令

The final output of the Regressed Queries query includes two generated remediation commands. Present them to the user as recommendations only and explain what each does before anything is executed.
Confirmation required. These commands change Query Store state and the plans the query uses. Never run
sp_query_store_force_plan
,
sp_query_store_unforce_plan
, or
sp_query_store_remove_plan
(or any similar Query Store procedure) without the user's explicit confirmation for that specific command. Before asking, state the impact (which
query_id
/
plan_id
is affected, what changes for the workload) and the rollback (unforce a forced plan; a removed plan cannot be restored). A general request to "fix" or "investigate" is not confirmation.
性能回归查询的最终输出包含两条生成的修复命令。只能将其作为建议呈现给用户,并在执行任何命令之前说明每条命令的作用。
需要确认。 这些命令会更改 Query Store 状态以及查询所使用的计划。未经用户针对该特定命令的明确确认,绝不运行
sp_query_store_force_plan
、
sp_query_store_unforce_plan
或
sp_query_store_remove_plan
(或任何类似的 Query Store 存储过程)。在请求确认之前,应说明影响(受影响的
query_id
/
plan_id
、工作负载会发生哪些变化)以及回滚方式(可以取消强制使用已强制计划;已删除的计划无法恢复)。笼统地要求“修复”或“调查”并不构成确认。

RemoveCurrentPlanCmd
—
sp_query_store_remove_plan

RemoveCurrentPlanCmd
—
sp_query_store_remove_plan

sql
EXEC sp_query_store_remove_plan @plan_id = <CurrentPlanId>
What it does: Permanently deletes the bad plan from Query Store. SQL Server will recompile the query on next execution and pick a new plan from scratch.
When to use:
  • The current plan is clearly bad (high
    RegressionFactor
    , high
    BadPlanCpuPctOfTotal
    ) but you don't have a known-good historical plan to force
  • You want to give the optimizer a clean slate without locking in a specific plan
  • Use as a last resort — once removed, the plan history for that
    plan_id
    is gone
⚠️ This is destructive. The plan cannot be recovered after removal.

sql
EXEC sp_query_store_remove_plan @plan_id = <CurrentPlanId>
作用: 从 Query Store 中永久删除不良计划。SQL Server 将在查询下次执行时重新编译该查询,并从头选择新计划。
适用场景:
  • 当前计划明显不佳(
    RegressionFactor
    高、
    BadPlanCpuPctOfTotal
    高),但没有可强制使用的已知良好历史计划
  • 希望在不锁定特定计划的情况下,为优化器提供干净的起点
  • 作为最后手段——计划一旦删除,该
    plan_id
    的计划历史就会丢失
⚠️ 此操作具有破坏性。删除后无法恢复该计划。

ForceBestHistPlanCmd
—
sp_query_store_force_plan

ForceBestHistPlanCmd
—
sp_query_store_force_plan

sql
EXEC sp_query_store_force_plan @query_id = <query_id>, @plan_id = <BestHistPlanId>
What it does: Tells Query Store to always use the specified historical plan for this query, bypassing the optimizer's plan selection. The forced plan persists until explicitly unforced.
When to use:
  • You have a verified good historical plan (
    BestHistPlanId
    ) that was performing well before the regression
  • The regression is caused by a bad plan choice (parameter sniffing, stale statistics, cardinality estimation error) rather than a schema or data volume change
  • As a fast mitigation during an active incident — stops the bleeding immediately while you investigate root cause
Check first that the plan can still be forced. Forcing works only while every index the plan uses still exists under the same name and is enabled. If one was dropped, renamed or disabled after the plan was compiled,
sp_query_store_force_plan
still succeeds, but every compile then fails to apply the plan (
last_force_failure_reason_desc
=
NO_INDEX
, 8712) and the query keeps the optimizer's own plan. This read-only query, run in the query's database, lists the indexes the plan names that are now missing or disabled:
sql
DECLARE @plan_id bigint = <BestHistPlanId>;
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
plan_objects AS (
    SELECT DISTINCT
           o.value('@Database', 'nvarchar(258)') AS database_name,
           o.value('@Schema',   'nvarchar(258)') AS schema_name,
           o.value('@Table',    'nvarchar(258)') AS table_name,
           o.value('@Index',    'nvarchar(258)') AS index_name
    FROM sys.query_store_plan AS p
    CROSS APPLY (SELECT TRY_CONVERT(xml, p.query_plan) AS plan_xml) AS x
    CROSS APPLY x.plan_xml.nodes('//Object[@Index]') AS n(o)
    WHERE p.plan_id = @plan_id
)
SELECT po.schema_name, po.table_name, po.index_name,
       CASE WHEN i.index_id IS NULL THEN 'missing' ELSE 'disabled' END AS problem
FROM plan_objects AS po
LEFT JOIN sys.indexes AS i
       ON i.object_id = OBJECT_ID(po.schema_name + N'.' + po.table_name)
      AND QUOTENAME(i.name) = po.index_name
WHERE po.database_name = QUOTENAME(DB_NAME())
  AND (i.index_id IS NULL OR i.is_disabled = 1);
Any row means forcing this plan will not take effect: do not present it as a fix on its own. The missing index is often the cause of the regression; propose recreating or re-enabling it (its definition is in the release script or source control). No rows means every index the plan names in this database is there; forcing can still fail for other reasons, so after the user forces it and the query recompiles, read
force_failure_count
and
last_force_failure_reason_desc
for the plan. Indexes in other databases are not checked, and a plan nested deeper than the
xml
type allows (128 levels) returns no rows; read its
Object
elements from the plan text instead.
To unforce a plan later:
sql
EXEC sp_query_store_unforce_plan @query_id = <query_id>, @plan_id = <BestHistPlanId>
Recommended order of operations (each command only after the user explicitly confirms it):
  1. Recommend
    ForceBestHistPlanCmd
    first, if the plan passes the check above — immediate relief, reversible
  2. Monitor CPU improvement via
    BadPlanCpuPctOfTotal
  3. If forcing doesn't help (e.g. data volume changed), recommend
    RemoveCurrentPlanCmd
    to trigger a fresh recompile
  4. Investigate root cause (missing index, stale stats, implicit conversion) to prevent recurrence

sql
EXEC sp_query_store_force_plan @query_id = <query_id>, @plan_id = <BestHistPlanId>
作用: 指示 Query Store 始终为该查询使用指定的历史计划,绕过优化器的计划选择。强制计划会持续生效,直到被显式取消强制。
适用场景:
  • 已验证的历史计划(
    BestHistPlanId
    )在性能回归发生前表现良好
  • 性能回归由不良计划选择(parameter sniffing、stale statistics、cardinality estimation error)引起,而不是由 schema 或数据量变化引起
  • 作为活跃事件期间的 快速缓解措施——在调查根因期间可以立即止损
首先检查该计划是否仍可被强制使用。 只有在计划使用的每个索引仍以相同名称存在且处于启用状态时,强制计划才会生效。如果计划编译后某个索引已被删除、重命名或禁用,
sp_query_store_force_plan
仍会执行成功,但之后每次编译都无法应用该计划(
last_force_failure_reason_desc
=
NO_INDEX
,8712),查询将继续使用优化器自行选择的计划。以下只读查询应在目标查询所在的数据库中运行,它会列出计划所引用但目前缺失或已禁用的索引:
sql
DECLARE @plan_id bigint = <BestHistPlanId>;
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
plan_objects AS (
    SELECT DISTINCT
           o.value('@Database', 'nvarchar(258)') AS database_name,
           o.value('@Schema',   'nvarchar(258)') AS schema_name,
           o.value('@Table',    'nvarchar(258)') AS table_name,
           o.value('@Index',    'nvarchar(258)') AS index_name
    FROM sys.query_store_plan AS p
    CROSS APPLY (SELECT TRY_CONVERT(xml, p.query_plan) AS plan_xml) AS x
    CROSS APPLY x.plan_xml.nodes('//Object[@Index]') AS n(o)
    WHERE p.plan_id = @plan_id
)
SELECT po.schema_name, po.table_name, po.index_name,
       CASE WHEN i.index_id IS NULL THEN 'missing' ELSE 'disabled' END AS problem
FROM plan_objects AS po
LEFT JOIN sys.indexes AS i
       ON i.object_id = OBJECT_ID(po.schema_name + N'.' + po.table_name)
      AND QUOTENAME(i.name) = po.index_name
WHERE po.database_name = QUOTENAME(DB_NAME())
  AND (i.index_id IS NULL OR i.is_disabled = 1);
只要返回任何一行,就表示强制使用该计划不会生效:不要将其单独作为修复方案。缺失的索引通常是性能回归的原因;建议重新创建或重新启用该索引(其定义位于发布脚本或源代码管理中)。如果没有返回任何行,则表示该计划在此数据库中引用的每个索引都存在;强制计划仍可能因其他原因失败,因此用户强制使用该计划并让查询重新编译后,应读取该计划的
force_failure_count
和
last_force_failure_reason_desc
。其他数据库中的索引不在检查范围内;如果计划嵌套层级超过
xml
类型允许的深度(128 层),查询将不会返回任何行;此时应改为从计划文本中读取其
Object
元素。
稍后取消强制使用计划:
sql
EXEC sp_query_store_unforce_plan @query_id = <query_id>, @plan_id = <BestHistPlanId>
推荐操作顺序(每条命令都必须在用户明确确认后执行):
  1. 如果计划通过上述检查,建议先执行
    ForceBestHistPlanCmd
    ——可立即缓解问题且可逆
  2. 通过
    BadPlanCpuPctOfTotal
    监控 CPU 改善情况
  3. 如果强制计划未奏效(例如数据量发生变化),建议执行
    RemoveCurrentPlanCmd
    以触发全新重新编译
  4. 调查根因(missing index、stale statistics、implicit conversion),以防止问题再次发生

Query Store Schema Reference

Query Store Schema 参考

sys.query_store_plan key columns

sys.query_store_plan 关键列

  • plan_id
    ,
    query_id
    ,
    query_plan_hash
    ,
    is_forced_plan
    ,
    is_parallel_plan
  • last_compile_start_time
    ,
    last_execution_time
    ,
    count_compiles
  • plan_id
    、
    query_id
    、
    query_plan_hash
    、
    is_forced_plan
    、
    is_parallel_plan
  • last_compile_start_time
    、
    last_execution_time
    、
    count_compiles

sys.query_store_query key columns

sys.query_store_query 关键列

  • query_id
    ,
    query_text_id
    ,
    object_id
    ,
    query_hash
  • initial_compile_start_time
    ,
    last_execution_time
    ,
    count_compiles
  • query_id
    、
    query_text_id
    、
    object_id
    、
    query_hash
  • initial_compile_start_time
    、
    last_execution_time
    、
    count_compiles

sys.query_store_runtime_stats_interval key columns

sys.query_store_runtime_stats_interval 关键列

  • runtime_stats_interval_id
    ,
    start_time
    ,
    end_time
  • runtime_stats_interval_id
    、
    start_time
    、
    end_time

Correlating incident captures

关联事件捕获信息

Use the shared incident evidence rules: execution-weighted averages, per-query time windows, positive-session blocking graphs, sleeping heads and distinct downstream counts. Keep diagnostic candidates separate from authorized actions.
使用共享的事件证据规则:执行次数加权平均值、按查询划分的时间窗口、positive-session blocking graphs、sleeping heads 以及不同的下游计数。将诊断候选项与已授权操作明确区分。