“What Changed Today” and “Is the Plan I Forced Still Any Good”
A query that was fine yesterday is slow today, or a plan you forced has stopped protecting it. The first script compares each query’s last 24 hours in Query Store with its own previous 6 days; the second lists every forced plan with its failure count and the reason forcing failed. For what is expensive right now, whatever its history, start with Query Store Top Queries.
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
- Neither raises an error. A failed force returns nothing to the application, the query just runs on another plan, and the only push signal is the
query_store_plan_forcing_failedExtended Event in the MS Docs Query Store best practices
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>). With Query Store OFF they return 1 row with the database name and the ALTER DATABASE that turns it on. READ_ONLY passes that check: the scripts run on whatever was captured before the switch and do not mention it, so when a busy database returns nothing, run Query Store Status first.
Get-QueryStoreRegressions: What Changed in the Last 24 Hours
- Tested on: SQL Server 2025 (RTM CU8) 17.0.4075.5, Windows lab instance
- Last verified: 2026-10-05 (both scripts run, the Example Output is a real run on the lab instance)
- Permissions: VIEW DATABASE PERFORMANCE STATE in the target database on SQL Server 2022 and later, or VIEW DATABASE STATE; without either the script stops with Msg 262
- Safety: read-only, impact low
/*
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
*/
-- 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
),
-- Materialise results so ORDER BY can reference aliases
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. @recent_hours sets the window: the last 24 hours are compared with the 6 days before them, by the UTC start time of each Query Store interval.
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
*/
-- 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. The application gets no error, so this column is where it shows.
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
Get-QueryStoreRegressions
A lab procedure ran 300 times for a customer with 10 orders on an index seek. It was then recompiled for a customer with 300,000 orders and ran 21 times on the scan plan that compile left behind, 20 of them for the small customer. Run with @recent_hours = 1 so the earlier runs fall in the baseline; one row, shown one column per line:
CPU per run went from 0.08 ms to 190.74 ms and reads from 33 to 9,936 for a customer who still had 10 orders: a plan change, not more data. At 2511x the factor is already a quarter of the way to the 9999.99 its column can hold.
Get-QueryStoreForcedPlans
A plan using an index seek was forced with sp_query_store_force_plan, then the index it seeks on was dropped. One row, shown one column per line:
The status is CRITICAL while plan_quality still reads OK, because they are separate checks. NO_INDEX means an index the forced plan uses no longer exists. The 2 failures are 2 recompiles: the query ran 6 times on its scan plan meanwhile, and last_execution_time on the forced plan stopped moving.
Understanding the Results
Get-QueryStoreRegressions
regression_status@regression_factor; WARN is more than 2x. CPU is tested before duration, and on CRITICAL rows the multiple in the text is cut to a whole number, so 4.9x reads as 4x.Act when it says CRITICAL. Open the query in the Regressed Queries view in SSMS (MS Docs) and compare its plans before the next busy period.cpu_regression_factorduration_regression_factorrecent_exec_countbaseline_exec_countquery_id, MS Docs), or slow for longer than the baseline: Query Store Top Queries has no baseline to meet.total_plan_countsp_query_store_force_plan, then check it holds with the second script.object_nameGet-QueryStoreForcedPlans
statusforce_failure_countlast_force_failure_reason_descsys.query_store_plan (MS Docs). Fixing the cause does not clear it: with the index re-created, the forced shape ran again under a new plan_id and this row still read CRITICAL, NO_INDEX.Act when status is CRITICAL. The query is running on whatever plan the optimizer picks. Put back what the reason names, then unforce and force the plan again so the count starts from 0.plan_qualityforced_avg_cpu_msbest_available_avg_cpu_msplan_age_dayslast_execution_timeBest 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
MS Docs: sys.query_store_runtime_stats · sys.query_store_plan · sys.database_query_store_options · Query Store best practices
Related Scripts
You may also find these scripts useful:
- The Query Is Fast in SSMS but Slow From the Application, forcing the plan is the fix that measured 852 reads for both values when ARITHABORT itself could not be changed
- Query and Performance Tuning (hub)
- Query Store Top Queries
- Query Store Status
- Query Performance Deep-Dive
- DBA Scripts: The Complete Guide, the map across every script on this site
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. The baseline is the 6 days before the last 24 hours, so a regression older than a day is already part of what it is compared with.
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. Dropping the index a forced seek plan used gave NO_INDEX; re-creating it with the same name let the forced shape run again, but the count stayed at 2 until the plan was forced again.
Why does Get-QueryStoreRegressions return nothing when users say it is slow?
The query has to have run at least 5 times in the last 24 hours and 5 times in the 6 days before. A new query, a procedure that was dropped and re-created, or a regression older than the baseline has nothing to compare with. A forced plan is left out of the recent window. On SQL Server 2022 and later, parameter-sensitive plan optimization can split one statement into variants with their own query_id, each with fewer executions. And if Query Store has gone READ_ONLY, nothing run since is captured: on the lab, 20 runs after the switch left the execution count at 321, and the script returned the same row as before without mentioning it.
Why does Get-QueryStoreRegressions fail with Msg 8115?
cpu_regression_factor and duration_regression_factor are DECIMAL(6,2), which holds up to 9999.99. A query that went from 0.01 ms to 100 ms is 10,000x, and converting 10000.0 to DECIMAL(6,2) raises Msg 8115, Arithmetic overflow error converting numeric to data type numeric, which stops the whole result. Change both casts to DECIMAL(12,2).
What permission do these scripts need?
VIEW DATABASE PERFORMANCE STATE in the target database on SQL Server 2022 and later, which VIEW DATABASE STATE includes. A login with neither gets Msg 262, VIEW DATABASE PERFORMANCE STATE permission denied in database, not an empty result. With it, the rows came back but object_name was NULL, because OBJECT_NAME only names objects the login can see.
A forced plan I know about is missing from Get-QueryStoreForcedPlans. Why?
The script joins each forced plan to its runtime stats, so a forced plan with none left drops out. A force that keeps failing never adds new stats to its own plan, and Query Store removes old runtime stats after stale_query_threshold_days, 30 by default. On the lab, clearing the stats of a failing forced plan took the result from 1 CRITICAL row to 0 rows. List every forced plan directly with SELECT query_id, plan_id, force_failure_count, last_force_failure_reason_desc FROM sys.query_store_plan WHERE is_forced_plan = 1;
Summary
“Slower than usual” and “the plan I forced isn’t working” are both invisible without specifically checking Query Store’s own history, and neither raises an error. 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