Part of the DBA-Tools Project.
“What Changed Today” and “Is the Plan I Forced Still Any Good”
Query Store Top Queries tells you what’s expensive right now. This post covers two narrower, sharper questions that come up once Query Store is actually in use day to day: which queries got worse today compared to their own recent history, and are the plans you deliberately forced still the right call, or quietly failing.
Both scripts compare against a query’s own baseline rather than an absolute threshold, which is what makes them useful for “something changed” investigations rather than routine top-N reviews.
Why Query Store Regressions and Forced Plans Matter
- A query that’s always been slow isn’t a regression, a query that suddenly runs 3x slower than its own 7-day average is, and that distinction needs a baseline comparison, not an absolute CPU or duration threshold
- Forcing a plan is a deliberate intervention, and it can silently stop working:
force_failure_count > 0means Query Store is reverting to natural plans behind your back, on a query you believe is protected - A forced plan that made sense six months ago can be actively holding a query back today, if the optimizer has since found something cheaper, the force is now the problem
- Both conditions are invisible without specifically checking, nothing alerts you when a regression starts or a force silently fails
When to Run These Scripts
- Right after “queries feel slower today” reports, before assuming it’s a broad server-level issue
- Routine health checks on any database with Query Store enabled, both scripts are cheap to run regularly
- Any time you’ve forced a plan, to periodically confirm it’s still both working and still the cheapest option
- Before and after a statistics update, index change, or data volume shift that could plausibly move plan choice
The Scripts
Both run in the context of the target database (-Database <dbname>), and both start with the same honest guard clause: if Query Store isn’t enabled, they tell you exactly how to turn it on instead of returning nothing.
Get-QueryStoreRegressions — What Changed in the Last 24 Hours
/*
Script Name : Get-QueryStoreRegressions
Category : performance
Purpose : Queries that regressed in the last 24 hours vs their 7-day average CPU/duration.
Uses Query Store time-bucketed runtime stats to detect "what changed today"
— queries running >2x slower or using >2x more CPU than their recent baseline.
Run in the context of the target database (-Database <dbname>).
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-query-store-regressions-and-forced-plans/)
Requires : VIEW DATABASE STATE
*/
-- Blog: https://sqldba.blog/dba-scripts-get-query-store-regressions-and-forced-plans/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
DECLARE @regression_factor DECIMAL(5,2) = 2.0;
DECLARE @min_executions INT = 5;
DECLARE @recent_hours INT = 24;
DECLARE @baseline_days INT = 7;
IF ISNULL((SELECT actual_state_desc FROM sys.database_query_store_options), 'OFF')
NOT IN ('READ_WRITE', 'READ_ONLY')
BEGIN
SELECT
DB_NAME() AS current_database,
ISNULL((SELECT actual_state_desc FROM sys.database_query_store_options), 'OFF')
AS query_store_status,
'Enable: ALTER DATABASE [' + DB_NAME() + '] SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE)'
AS action;
END
ELSE
BEGIN
WITH recent_window AS (
SELECT
p.query_id,
SUM(rs.count_executions) AS exec_count,
AVG(rs.avg_cpu_time) / 1000.0 AS avg_cpu_ms,
AVG(rs.avg_duration) / 1000.0 AS avg_duration_ms,
AVG(rs.avg_logical_io_reads) AS avg_logical_reads,
MAX(rs.max_cpu_time) / 1000.0 AS max_cpu_ms
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_runtime_stats_interval AS ri ON ri.runtime_stats_interval_id = rs.runtime_stats_interval_id
JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id
WHERE ri.start_time >= DATEADD(HOUR, -@recent_hours, GETUTCDATE())
AND p.is_forced_plan = 0
GROUP BY p.query_id
HAVING SUM(rs.count_executions) >= @min_executions
),
baseline_window AS (
SELECT
p.query_id,
SUM(rs.count_executions) AS exec_count,
AVG(rs.avg_cpu_time) / 1000.0 AS avg_cpu_ms,
AVG(rs.avg_duration) / 1000.0 AS avg_duration_ms,
AVG(rs.avg_logical_io_reads) AS avg_logical_reads
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_runtime_stats_interval AS ri ON ri.runtime_stats_interval_id = rs.runtime_stats_interval_id
JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id
WHERE ri.start_time >= DATEADD(DAY, -@baseline_days, GETUTCDATE())
AND ri.start_time < DATEADD(HOUR, -@recent_hours, GETUTCDATE())
GROUP BY p.query_id
HAVING SUM(rs.count_executions) >= @min_executions
),
regressed AS (
SELECT
rw.query_id,
OBJECT_NAME(q.object_id) AS object_name,
CAST(rw.avg_cpu_ms AS DECIMAL(10,2)) AS recent_avg_cpu_ms,
CAST(rw.avg_duration_ms AS DECIMAL(10,2)) AS recent_avg_duration_ms,
CAST(rw.avg_logical_reads AS DECIMAL(10,0)) AS recent_avg_logical_reads,
rw.exec_count AS recent_exec_count,
CAST(bw.avg_cpu_ms AS DECIMAL(10,2)) AS baseline_avg_cpu_ms,
CAST(bw.avg_duration_ms AS DECIMAL(10,2)) AS baseline_avg_duration_ms,
CAST(bw.avg_logical_reads AS DECIMAL(10,0)) AS baseline_avg_logical_reads,
bw.exec_count AS baseline_exec_count,
CAST(rw.avg_cpu_ms / NULLIF(bw.avg_cpu_ms, 0) AS DECIMAL(6,2)) AS cpu_regression_factor,
CAST(rw.avg_duration_ms / NULLIF(bw.avg_duration_ms, 0) AS DECIMAL(6,2)) AS duration_regression_factor,
(SELECT COUNT(DISTINCT plan_id) FROM sys.query_store_plan
WHERE query_id = rw.query_id) AS total_plan_count,
CASE
WHEN rw.avg_cpu_ms > bw.avg_cpu_ms * (@regression_factor * 2)
THEN 'CRITICAL — CPU ' +
CAST(CAST(rw.avg_cpu_ms / NULLIF(bw.avg_cpu_ms, 0) AS INT) AS VARCHAR) +
'x baseline; likely plan regression or stats change'
WHEN rw.avg_duration_ms > bw.avg_duration_ms * (@regression_factor * 2)
THEN 'CRITICAL — duration ' +
CAST(CAST(rw.avg_duration_ms / NULLIF(bw.avg_duration_ms, 0) AS INT) AS VARCHAR) +
'x baseline'
WHEN rw.avg_cpu_ms > bw.avg_cpu_ms * @regression_factor
THEN 'WARN — CPU ' +
CAST(CAST(rw.avg_cpu_ms / NULLIF(bw.avg_cpu_ms, 0) AS DECIMAL(4,1)) AS VARCHAR) +
'x baseline'
WHEN rw.avg_duration_ms > bw.avg_duration_ms * @regression_factor
THEN 'WARN — duration ' +
CAST(CAST(rw.avg_duration_ms / NULLIF(bw.avg_duration_ms, 0) AS DECIMAL(4,1)) AS VARCHAR) +
'x baseline'
ELSE 'INFO'
END AS regression_status,
LEFT(qt.query_sql_text, 400) AS query_text
FROM recent_window AS rw
JOIN baseline_window AS bw ON bw.query_id = rw.query_id
JOIN sys.query_store_query AS q ON q.query_id = rw.query_id
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
WHERE rw.avg_cpu_ms > bw.avg_cpu_ms * @regression_factor
OR rw.avg_duration_ms > bw.avg_duration_ms * @regression_factor
)
SELECT *
FROM regressed
ORDER BY
CASE WHEN regression_status LIKE 'CRITICAL%' THEN 1
WHEN regression_status LIKE 'WARN%' THEN 2
ELSE 3 END,
cpu_regression_factor DESC;
END;
The regression factor (2x) and minimum execution count (5) are declared as variables at the top specifically so they’re easy to tune, a query that only ran twice in the recent window isn’t a reliable regression signal yet.
Get-QueryStoreForcedPlans — Is the Force Still Working, and Still Worth It
/*
Script Name : Get-QueryStoreForcedPlans
Category : performance
Purpose : Forced plans in Query Store with failure counts, plan age, forcing reason,
and whether the forced plan is still the cheapest available option.
A force_failure_count > 0 means QS is silently reverting to natural plans —
queries you think are protected are not.
Run in the context of the target database (-Database <dbname>).
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-query-store-regressions-and-forced-plans/)
Requires : VIEW DATABASE STATE
*/
-- Blog: https://sqldba.blog/dba-scripts-get-query-store-regressions-and-forced-plans/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
IF ISNULL((SELECT actual_state_desc FROM sys.database_query_store_options), 'OFF')
NOT IN ('READ_WRITE', 'READ_ONLY')
BEGIN
SELECT
DB_NAME() AS current_database,
ISNULL((SELECT actual_state_desc FROM sys.database_query_store_options), 'OFF')
AS query_store_status,
'Enable: ALTER DATABASE [' + DB_NAME() + '] SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE)'
AS action;
END
ELSE
BEGIN
WITH forced AS (
SELECT
p.query_id,
p.plan_id AS forced_plan_id,
p.force_failure_count,
p.last_force_failure_reason_desc,
p.plan_forcing_type_desc,
p.last_execution_time,
p.initial_compile_start_time AS plan_created,
DATEDIFF(DAY, p.initial_compile_start_time, GETDATE()) AS plan_age_days,
AVG(rs.avg_cpu_time) / 1000.0 AS forced_avg_cpu_ms,
SUM(rs.count_executions) AS forced_exec_count
FROM sys.query_store_plan AS p
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
WHERE p.is_forced_plan = 1
GROUP BY
p.query_id, p.plan_id, p.force_failure_count,
p.last_force_failure_reason_desc, p.plan_forcing_type_desc,
p.last_execution_time, p.initial_compile_start_time
),
best_plan AS (
SELECT
p.query_id,
MIN(rs_agg.avg_cpu_ms) AS best_avg_cpu_ms
FROM sys.query_store_plan AS p
JOIN (
SELECT plan_id, AVG(avg_cpu_time) / 1000.0 AS avg_cpu_ms
FROM sys.query_store_runtime_stats
GROUP BY plan_id
) AS rs_agg ON rs_agg.plan_id = p.plan_id
GROUP BY p.query_id
),
results AS (
SELECT
f.query_id,
f.forced_plan_id,
OBJECT_NAME(q.object_id) AS object_name,
LEFT(qt.query_sql_text, 400) AS query_text,
f.plan_forcing_type_desc,
f.plan_age_days,
f.force_failure_count,
f.last_force_failure_reason_desc,
f.last_execution_time,
CAST(f.forced_avg_cpu_ms AS DECIMAL(10,2)) AS forced_avg_cpu_ms,
CAST(bp.best_avg_cpu_ms AS DECIMAL(10,2)) AS best_available_avg_cpu_ms,
CASE
WHEN bp.best_avg_cpu_ms < f.forced_avg_cpu_ms * 0.8
THEN CAST(CAST(100.0 * (f.forced_avg_cpu_ms - bp.best_avg_cpu_ms)
/ NULLIF(bp.best_avg_cpu_ms, 0) AS INT) AS VARCHAR) +
'% cheaper plan available — consider unforcing and testing'
ELSE 'OK — forced plan remains competitive'
END AS plan_quality,
CASE
WHEN f.force_failure_count > 0
THEN 'CRITICAL — force is FAILING (' + CAST(f.force_failure_count AS VARCHAR) +
' failures, reason: ' + f.last_force_failure_reason_desc +
'); query is running without the forced plan'
WHEN f.plan_age_days > 180
THEN 'WARN — plan forced > 6 months ago; re-evaluate whether force is still needed'
WHEN bp.best_avg_cpu_ms < f.forced_avg_cpu_ms * 0.8
THEN 'WARN — cheaper plan now exists in QS; forced plan may be holding back performance'
ELSE 'OK'
END AS status
FROM forced AS f
JOIN sys.query_store_query AS q ON q.query_id = f.query_id
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
JOIN best_plan AS bp ON bp.query_id = f.query_id
)
SELECT *
FROM results
ORDER BY
CASE WHEN status LIKE 'CRITICAL%' THEN 1
WHEN status LIKE 'WARN%' THEN 2
ELSE 3 END,
force_failure_count DESC,
plan_age_days DESC;
END;
force_failure_count > 0 is the single most important thing this script surfaces: it means Query Store has been silently falling back to a natural plan because it couldn’t apply the forced one, and nothing else in the system tells you that’s happening.
How To Run From The Repo
Clone DBA Tools, initialize and run either script against a database with Query Store enabled:
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# Queries that regressed in the last 24 hours vs their 7-day baseline:
.\run.ps1 Get-QueryStoreRegressions -Database YourDatabase
# Forced plans, failure counts, and whether they're still the best option:
.\run.ps1 Get-QueryStoreForcedPlans -Database YourDatabase
# To run against a remote sql server:
.\run.ps1 Get-QueryStoreRegressions -ServerInstance SQLSERVER01 -Database YourDatabase
These scripts live in the repo at:
sql/performance/query-store/Get-QueryStoreRegressions.sqlsql/performance/query-store/Get-QueryStoreForcedPlans.sql
Example Output
Real output from this lab instance, not staged: both scripts return zero rows against SalesDemo, a database with Query Store enabled and stable, repeatable workload. That’s the honest, correct result, no queries have regressed and no plans have been forced here, exactly what a clean, stable workload should show. On a database with a genuine regression, Get-QueryStoreRegressions returns a row like this:
| object_name | recent_avg_cpu_ms | baseline_avg_cpu_ms | cpu_regression_factor | regression_status |
|---|---|---|---|---|
| usp_GetOrderSummary | 842.50 | 210.30 | 4.01 | CRITICAL — CPU 4x baseline; likely plan regression or stats change |
Understanding the Results
Regressions:
– CRITICAL vs WARN — CRITICAL means more than 4x the baseline (double the WARN threshold), a query at this level is worth investigating before the next business-hours peak, not at the end of the day
– recent_exec_count / baseline_exec_count both required to meet the minimum — a query that barely ran in either window is excluded, avoiding false alarms from low-volume noise
– total_plan_count > 1 — multiple plans exist for this query, worth checking whether a new plan choice (not just data volume or parameter sniffing) explains the regression
Forced Plans:
– force_failure_count > 0 — the single most urgent finding; the plan you think is protecting this query isn’t being used at all right now
– plan_age_days > 180 with status OK — not urgent, but worth a periodic look, forces made a long time ago are easy to forget about entirely
– plan_quality showing a cheaper available plan — the force itself may now be the performance problem, test unforcing before assuming the force is still doing its job
Best Practices
- Run Get-QueryStoreRegressions as a routine daily check, not just when someone reports slowness, catching a regression early is cheaper than diagnosing it after a peak-hours incident
- Treat any forced plan with
force_failure_count > 0as an active incident, the query is running unprotected right now - Re-review forced plans periodically, not just when they’re first created, both the optimizer’s options and the data underneath a query change over time
- Pair a regression finding with Query Performance Deep-Dive when the cause isn’t obvious from Query Store alone
Related Scripts
You may also find these scripts useful:
- Query and Performance Tuning (hub)
- Query Store Top Queries
- Query Store Status
- Query Performance Deep-Dive
Frequently Asked Questions
Why compare against a 7-day baseline instead of a fixed threshold?
A fixed threshold either misses queries that are naturally variable or false-alarms on queries that are consistently a little slow by design. Comparing against the query’s own recent history catches a genuine change in behavior regardless of what “normal” looks like for that specific query.
Does force_failure_count mean the forced plan is invalid?
Not necessarily invalid, but currently inapplicable, usually because of a schema change, a dropped index the plan depended on, or a parameter type mismatch. last_force_failure_reason_desc gives the specific reason, worth checking before assuming the plan itself needs to change.
Summary
“Slower than usual” and “the plan I forced isn’t working” are both invisible without specifically checking Query Store’s own history, no alert fires for either on its own. These two scripts turn both into a routine check: what regressed against its own baseline in the last day, and whether every forced plan is actually still forcing and still worth forcing.
Run both as part of a regular Query Store review, and treat a failing force or a critical regression as worth acting on the same day it’s found, not at the next scheduled review.
Leave a Reply