Loading...
Loading...
Use when the user asks for top or regressed queries in Query Store: what got slower since a deploy versus a baseline, or which earlier plan to force. When Query Store is off, use `top-queries-noqds`.
npx skill4agent add microsoft/microsoft-sql query-store-investigationquery-store-volatile-historical-slow-queries-comparisonmicrosoft-sql-adminmssql_list_database_connectionsmssql_connect_databasemssql_execute_querysqlcmdsql-tools-mcp-setupmssql_execute_queryplan_idqueryStorePlanIdSummarizePlanGetPlanWarningsPlanBottleneckAnalysis| Parameter | Default | Description |
|---|---|---|
| TopN | 25 | Maximum number of rows to return per query |
| HistDaysBack | 1 | Days back for the historical baseline window |
| CurrHoursBack | 1 | Hours back for the current (recent) window |
| AvgCpuTime | 1000 | Minimum avg CPU time (µs) to include a query |
| MinExecCount | 3 | Minimum execution count to include a query |
| RegressionFactor | 2 | Only return queries where current avg CPU > best historical avg CPU × this multiplier |
datetimeoffsetdatetimeGETDATE()sp_query_store_force_plansp_query_store_unforce_plansp_query_store_remove_plansp_query_store_set_hintssp_query_store_clear_hintssp_query_store_remove_querysp_query_store_force_planNO_INDEXForceBestHistPlanCmdSELECT 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);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);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);RemoveCurrentPlanCmdForceBestHistPlanCmdNote on window overlap: the historical window spans the fullday(s) and intentionally overlaps with the current window. This provides a robust full-day CPU baseline that includes recent hours.{{HistDaysBack}}
-- 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;Confirmation required. These commands change Query Store state and the plans the query uses. Never run,sp_query_store_force_plan, orsp_query_store_unforce_plan(or any similar Query Store procedure) without the user's explicit confirmation for that specific command. Before asking, state the impact (whichsp_query_store_remove_plan/query_idis 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.plan_id
RemoveCurrentPlanCmdsp_query_store_remove_planEXEC sp_query_store_remove_plan @plan_id = <CurrentPlanId>RegressionFactorBadPlanCpuPctOfTotalplan_id⚠️ This is destructive. The plan cannot be recovered after removal.
ForceBestHistPlanCmdsp_query_store_force_planEXEC sp_query_store_force_plan @query_id = <query_id>, @plan_id = <BestHistPlanId>BestHistPlanIdsp_query_store_force_planlast_force_failure_reason_descNO_INDEXDECLARE @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_countlast_force_failure_reason_descxmlObjectEXEC sp_query_store_unforce_plan @query_id = <query_id>, @plan_id = <BestHistPlanId>ForceBestHistPlanCmdBadPlanCpuPctOfTotalRemoveCurrentPlanCmdplan_idquery_idquery_plan_hashis_forced_planis_parallel_planlast_compile_start_timelast_execution_timecount_compilesquery_idquery_text_idobject_idquery_hashinitial_compile_start_timelast_execution_timecount_compilesruntime_stats_interval_idstart_timeend_time