20 - Query Store Regressions and Plan Forcing

35 min read

Query Store Regressions and Plan Forcing

Operational flow for Query Store regression triage.


flowchart TD
    A["Slow or unstable query"] --> B{"Query Store<br/>enabled and writable?"}
    B --> Y1([YES])
    B --> N1([NO])
    N1 --> C["Enable Query Store first<br/>or this workflow is blind"]
    Y1 --> D{"More than one plan<br/>for the same query?"}
    D --> Y2([YES])
    D --> N2([NO])
    Y2 --> E["Compare best and worst plans<br/>by duration, reads, and waits"]
    N2 --> F["Root cause is not yet a regression;<br/>inspect plans, stats, indexes, and waits"]
    E --> G{"One plan is clearly safer<br/>and root cause not yet fixed?"}
    G --> Y3([YES])
    G --> N3([NO])
    Y3 --> H["Force the known-good plan<br/>temporarily and review later"]
    N3 --> I["Use Query Store hints<br/>or fix stats, indexes, predicates"]

    classDef yesNode fill:#1f3b2d,stroke:#73d13d,stroke-width:2px,color:#c0caf5,font-weight:bold;
    classDef noNode fill:#4a1f24,stroke:#db4b4b,stroke-width:2px,color:#c0caf5,font-weight:bold;
    class Y1,Y2,Y3 yesNode;
    class N1,N2,N3 noNode;

What Query Store Is

Architecture at a glance

Query Store has three pieces you care about as an operator: the in-memory buffers, the persistent storage in the user database, and the set of catalog views that expose both.

The engine writes new query text, plans, and runtime statistics into an in-memory buffer. A background task flushes the buffer to disk every flush_interval_seconds (default 900 seconds = 15 minutes) and whenever the buffer fills. The on-disk data is stored inside the user database, counted against its data files, and cleaned up by two policies — time-based (stale_query_threshold_days, default 30) and size-based (size_based_cleanup_mode, default AUTO). The catalog views (sys.query_store_*) read from both the in-memory buffer and the persisted storage, so querying them returns the union of what has been flushed and what is still pending.

The version matrix

Some Query Store features require specific SQL Server versions — check the server you are operating against.

Check the server version before using Query Store Hints or the newer capture modes.

SELECT
    @@VERSION AS v,
    SERVERPROPERTY('ProductMajorVersion') AS major,
    SERVERPROPERTY('ProductLevel')        AS level;
| v | major | level |
|---|---|---|
| Microsoft SQL Server 2022 (RTM-CU23) ... 16.0.4236.2 (X64) Developer Edition (64-bit) on Linux | 16 | RTM |

The stoxx lab runs on SQL Server 2022 (major = 16), so every feature in this note is available. The version gates that matter:

FeatureMinimum versionNotes
Query Store (core)SQL 2016 (13)sys.database_query_store_options, plan forcing
Default capture mode becomes AUTOSQL 2019 (15)2016/2017 defaulted to ALL
CUSTOM capture modeSQL 2019 (15)Fine-grained capture policy
OPTION (LABEL = '...')SQL 2022 (16)Labels queries for Query Store identification
Query Store Hints (sp_query_store_set_hints)SQL 2022 (16)Inject hints without editing code
Query Store for readable secondariesSQL 2022 (16)Primary replicas only before that

Query Store Baseline

Check Query Store state and capture mode

The sys.database_query_store_options catalog view is the database-level control surface for Query Store. It holds both the configured target state and the actual runtime state, which may diverge when Query Store has entered read-only mode automatically. Always check both and inspect readonly_reason if they disagree.

Confirm Query Store is writable and capturing waits

The query below pulls the seven columns that matter for a baseline check: the desired vs actual state, any read-only reason, current vs max storage size, the capture mode, and whether per-query wait statistics are being collected.

Query the database-level Query Store configuration and current runtime state.

SELECT
    desired_state_desc,
    actual_state_desc,
    readonly_reason,
    current_storage_size_mb,
    max_storage_size_mb,
    query_capture_mode_desc,
    wait_stats_capture_mode_desc
FROM sys.database_query_store_options;
| desired_state_desc | actual_state_desc | readonly_reason | current_storage_size_mb | max_storage_size_mb | query_capture_mode_desc | wait_stats_capture_mode_desc |
|---|---|---:|---:|---:|---|---|
| `READ_WRITE` | `READ_WRITE` | 0 | 4 | 1000 | `ALL` | `ON` |

Desired and actual both report READ_WRITE, which means Query Store is actively collecting new plans and runtime observations. readonly_reason = 0 confirms there is no active read-only blocker. Storage pressure is nowhere near the ceiling: 4 MB of 1000 MB in use. Capture mode is ALL (every query is captured), and wait statistics capture is ON — the strongest configuration for triage work.

ColumnValueStateMeaningImplication
desired_state_descREAD_WRITEHealthyConfigured for writable data collection.Correct baseline for troubleshooting.
actual_state_descREAD_WRITEHealthyActively usable right now.Regression queries, forcing, and hints all work.
actual_state_descREAD_ONLYProblemNot accepting new writable data.Investigate readonly_reason before trusting the workflow.
actual_state_descERRORCriticalInternal corruption.Run sp_query_store_consistency_check; if that fails, SET QUERY_STORE CLEAR.
readonly_reason0HealthyNo active read-only blocker.Normal operating state.
readonly_reasonNon-zeroProblemSee the enumeration below.Resolve the underlying reason before proceeding.
current_storage_size_mbFar below max_storage_size_mbHealthyPlenty of headroom.No sizing concern.
current_storage_size_mbNear max_storage_size_mbWarningApproaching quota.Increase MAX_STORAGE_SIZE_MB or lower STALE_QUERY_THRESHOLD_DAYS.
query_capture_mode_descALLModeBroad, exhaustive capture.Best for demos and audits; more than production usually needs.
query_capture_mode_descAUTOModeFiltered to significant queries.Production default from SQL 2019 onward.
wait_stats_capture_mode_descONHealthyPer-query waits persisted.Enables wait-based regression analysis.

Decode the readonly_reason bitmap

readonly_reason is a bitmap — multiple conditions can be active at once. Any non-zero value means Query Store is not capturing new data, and knowing which bits are set tells you what to fix.

Emit the bitmap lookup as a literal result set for reference.

SELECT *
FROM (VALUES
    (1, N'Database is in read-only mode', N'Set the database read-write if you own it.'),
    (2, N'Database is in single-user mode', N'Return to multi-user mode.'),
    (4, N'Database is in emergency mode', N'Exit emergency mode; investigate the underlying failure.'),
    (8, N'Database is a secondary replica (AG)', N'Expected on non-primary replicas; use READ_CAPTURE_SECONDARY if you need capture on secondaries (SQL 2022+).'),
    (65536, N'Query Store reached max_storage_size_mb', N'Increase the ceiling or run SET QUERY_STORE CLEAR.'),
    (131072, N'Internal in-memory limit on distinct statements hit', N'Clean old statements; consider upgrading SKU.'),
    (262144, N'Pending in-memory items exceed the internal limit', N'Transient; resolves once the background flush catches up.'),
    (524288, N'Database file size limit reached', N'Grow the database file or increase the disk quota.')
) AS v([Bit value], [Meaning], [Fix]);
| Bit value | Meaning | Fix |
|---:|---|---|
| `1` | Database is in read-only mode | Set the database read-write if you own it. |
| `2` | Database is in single-user mode | Return to multi-user mode. |
| `4` | Database is in emergency mode | Exit emergency mode; investigate the underlying failure. |
| `8` | Database is a secondary replica (AG) | Expected on non-primary replicas; use `READ_CAPTURE_SECONDARY` if you need capture on secondaries (SQL 2022+). |
| `65536` | Query Store reached `max_storage_size_mb` | Increase the ceiling or run `SET QUERY_STORE CLEAR`. |
| `131072` | Internal in-memory limit on distinct statements hit | Clean old statements; consider upgrading SKU. |
| `262144` | Pending in-memory items exceed the internal limit | Transient; resolves once the background flush catches up. |
| `524288` | Database file size limit reached | Grow the database file or increase the disk quota. |

Read-only mode is silent

Query Store does not raise an error when it flips to read-only. The configuration view still reports your desired state. The only signal is the divergence between desired_state_desc and actual_state_desc:

  • Existing plans continue to be observable.
  • New plans are not captured.
  • New runtime statistics are not recorded.
  • You will regress back to the plan cache as your only visibility.

Monitor state continuously, not once

  • Schedule a check that compares desired_state_desc and actual_state_desc every 15 minutes.
  • Alert on any non-zero readonly_reason.
  • Activate SIZE_BASED_CLEANUP_MODE = AUTO so the engine cleans aggressively when approaching the ceiling.
  • Set a STALE_QUERY_THRESHOLD_DAYS that matches your retention needs — 30 days is the default but long-tail systems benefit from 7 days to reduce churn.

Inspect retention and flush configuration

Beyond the seven core columns above, sys.database_query_store_options exposes several sizing and retention knobs that determine how aggressively Query Store trims itself and how often it flushes to disk.

Pull the retention and flush cadence columns so the reader can see what interval length and stale threshold are actually in effect.

SELECT
    interval_length_minutes,
    stale_query_threshold_days,
    max_plans_per_query,
    size_based_cleanup_mode_desc,
    flush_interval_seconds
FROM sys.database_query_store_options;
| interval_length_minutes | stale_query_threshold_days | max_plans_per_query | size_based_cleanup_mode_desc | flush_interval_seconds |
|---:|---:|---:|---|---:|
| 60 | 30 | 200 | `AUTO` | 900 |

The observation interval is 60 minutes, meaning all runtime statistics are aggregated into one-hour buckets. The retention policy keeps data for 30 days before cleanup. Each query may accumulate up to 200 plans before the cap applies. Size-based cleanup runs automatically when storage approaches the ceiling. The background flush task writes in-memory buffers to disk every 900 seconds (15 minutes).

Force an immediate flush

Use this when you need the latest runtime observations to appear in Query Store before you inspect them.

In lab or test scenarios, the runtime statistics you just generated may still be in the in-memory buffer, not yet visible to catalog views. Force the flush with sp_query_store_flush_db.

EXEC sys.sp_query_store_flush_db;
SELECT 'flushed' AS status;
| status |
|---|
| flushed |

sp_query_store_flush_db writes the in-memory Query Store buffer to disk immediately, bypassing the flush_interval_seconds schedule. Use it between “run the query to be analyzed” and “read from sys.query_store_*” in reproducible lab workflows, otherwise you may query too early and see nothing.

Use sp_query_store_flush_db in repro workflows

  • Lab setups — between workload generation and catalog inspection, always flush first.
  • Post-force validation — after sp_query_store_force_plan, flush before verifying is_forced_plan = 1.
  • Automated tests — any test that asserts against Query Store state should flush to ensure deterministic observation.
  • Production — rarely needed; the background flush is usually fast enough for triage work.

Plan Regression Candidates

Compare best and worst plans per query

The sys.query_store_runtime_stats view is the source of truth for runtime observations; it joins to sys.query_store_plan via plan_id and from there to sys.query_store_query via query_id. The query below counts distinct plans per query_id and ratios the worst-plan duration against the best-plan duration to surface the highest-spread candidates.

Find queries whose plans have materially different runtime profiles

Aggregate runtime statistics by query_id and rank the candidates by the ratio of worst to best average duration.

SELECT TOP (10)
    qsq.query_id,
    COUNT(DISTINCT qsp.plan_id) AS plan_count,
    CAST(MIN(qsrs.avg_duration) / 1000.0 AS decimal(18,2)) AS best_avg_ms,
    CAST(MAX(qsrs.avg_duration) / 1000.0 AS decimal(18,2)) AS worst_avg_ms,
    CAST(MAX(qsrs.avg_duration) * 1.0 / NULLIF(MIN(qsrs.avg_duration), 0) AS decimal(18,2)) AS regression_factor,
    LEFT(qsqt.query_sql_text, 160) AS query_text
FROM sys.query_store_runtime_stats AS qsrs
JOIN sys.query_store_plan AS qsp ON qsrs.plan_id = qsp.plan_id
JOIN sys.query_store_query AS qsq ON qsp.query_id = qsq.query_id
JOIN sys.query_store_query_text AS qsqt ON qsq.query_text_id = qsqt.query_text_id
GROUP BY qsq.query_id, qsqt.query_sql_text
HAVING COUNT(DISTINCT qsp.plan_id) > 1
ORDER BY regression_factor DESC, worst_avg_ms DESC;
| query_id | plan_count | best_avg_ms | worst_avg_ms | regression_factor | query_text |
|---:|---:|---:|---:|---:|---|
| 249 | 2 | 0.33 | 3.48 | 10.68 | `DELETE b FROM bronze.stoxxusa50_ohlcv b INNER JOIN ( SELECT symbol, MAX(date) AS max_date FROM bronze.stoxxusa50_ohlcv` |
| 235 | 2 | 0.44 | 3.17 | 7.18 | `DELETE b FROM bronze.stoxxasia50_ohlcv b INNER JOIN ( SELECT symbol, MAX(date) AS max_date FROM bronze.stoxxasia50_ohlcv` |
| 228 | 2 | 0.45 | 3.12 | 6.98 | `DELETE b FROM bronze.eurostoxx50_ohlcv b INNER JOIN ( SELECT symbol, MAX(date) AS max_date FROM bronze.eurostoxx50_ohlcv` |
| 2766 | 2 | 0.21 | 0.76 | 3.56 | `(@_msparam_0 nvarchar(4000),...) SELECT clmns.name AS [Name], clmns.column_id AS [ID` |
| 3007 | 3 | 762.17 | 1813.89 | 2.38 | `ALTER INDEX ALL ON dbo.demo_idxmaint_rowstore REBUILD WITH (ONLINE = ON, MAXDOP = 2)` |
| 1881 | 5 | 7.29 | 13.64 | 1.87 | `UPDATE STATISTICS [silver].[eurostoxx50_ohlcv]` |
| 1879 | 4 | 10.19 | 15.72 | 1.54 | `UPDATE STATISTICS [silver].[stoxxasia50_ohlcv]` |
| 1880 | 4 | 10.21 | 14.92 | 1.46 | `UPDATE STATISTICS [silver].[stoxxusa50_ohlcv]` |
| 2995 | 2 | 1675.26 | 2329.01 | 1.39 | `WITH n AS ( SELECT 1 AS batch_no UNION ALL SELECT 2 UNION ALL ... ) INSERT INTO dbo.demo_idxmaint_rowstore (batch` |
| 3033 | 2 | 97.19 | 122.65 | 1.26 | `UPDATE STATISTICS dbo.demo_idxmaint_rowstore WITH FULLSCAN` |

The strongest candidates are the three DELETE ... MAX(date) bronze cleanup statements at the top, where the worst plan runs 7 to 11 times slower than the best. This is the kind of gap that justifies a closer look — not a command to force a plan blindly, but a strong signal to inspect those queries first. Notice that the bottom half of the list consists of UPDATE STATISTICS and index-maintenance statements; those are maintenance activities with inherently variable cost depending on the amount of data churn they encounter, so they produce “false positive” regression shortlist entries that should be filtered out of operational triage.

ColumnValueStateMeaningImplication
plan_count1N/AQuery has only one tracked plan.Cannot be a regression candidate yet.
plan_count> 1CandidateMultiple plan variants captured.Eligible for regression comparison.
regression_factorNear 1HealthyPlans perform similarly.Plan variability is not the first suspect.
regression_factor> 2CandidateWorst plan at least twice as slow.Strong regression shortlist entry.
regression_factor> 5PriorityLarge spread between best and worst.Investigate first; likely a plan-choice issue.
best_avg_ms vs worst_avg_msLarge absolute spreadCandidateMaterial runtime difference.Check plan shape, statistics, parameter sensitivity, indexing.
query_textMaintenance/adminFilterRegression belongs to maintenance activity.Prioritize by workload criticality, not numeric spread.

Refine the shortlist with domain context

  • Exclude UPDATE STATISTICS, DBCC, ALTER INDEX patterns — their runtime variance is expected and not a plan problem.
  • Exclude auto-parameterized msparam_ queries from SSMS itself — they are tooling overhead, not application workload.
  • Join to sys.query_store_runtime_stats_interval if you want to restrict to a specific time window (e.g., “last 24 hours only”).
  • For production dashboards, add COUNT(*) AS exec_count to weight the shortlist by how often the query actually ran — a 100x regression on a query that runs twice a week is lower priority than a 3x regression on a query that runs every minute.

Controlled Force-Plan Workflow

Build a disposable demo table

The lab table is an intentionally simple copy of silver.eurostoxx50_ohlcv with no supporting index on the predicate columns. The clustered primary key is on a surrogate id, not on symbol or date, so the first execution of the target query has no useful access path and the optimizer falls back to a clustered index scan. This is the necessary starting condition for generating two distinct plans for the same logical query.

Create the demo table from real stoxx data

Use a disposable copy so the later scan-versus-seek contrast comes from indexing, not from synthetic data.

Create a fresh dbo.qs_force_demo table, seed it with every row from silver.eurostoxx50_ohlcv, and verify the row count.

USE stoxx;
IF OBJECT_ID('dbo.qs_force_demo', 'U') IS NOT NULL
    DROP TABLE dbo.qs_force_demo;
 
CREATE TABLE dbo.qs_force_demo
(
    id int IDENTITY(1,1) NOT NULL CONSTRAINT PK_qs_force_demo PRIMARY KEY,
    symbol nvarchar(32) NOT NULL,
    [date] date NOT NULL,
    [close] decimal(19,4) NULL,
    volume bigint NULL
);
 
INSERT INTO dbo.qs_force_demo (symbol, [date], [close], volume)
SELECT symbol, [date], [close], volume
FROM silver.eurostoxx50_ohlcv;
 
SELECT COUNT(*) AS row_count
FROM dbo.qs_force_demo;
| row_count |
|---:|
| 67155 |

The demo table holds the full silver.eurostoxx50_ohlcv rowset (67,155 rows), so the later scan-versus-seek difference is grounded in real table size and real data distribution, not a synthetic fixture. Note that PRIMARY KEY on id creates the clustered index — but that index is on the surrogate key, not on (symbol, date), so a predicate like WHERE symbol = 'X' AND date BETWEEN ... cannot use it for a seek.

Generate two plans for the same query

The classic way to produce two captured plans for the same logical query is to run the query once, change the schema (add an index, drop an index, alter a column type), then run the query again. Query Store sees the schema change as a trigger to recompile and captures the second plan separately. The tag OPTION (LABEL = 'qs_force_demo_count') lets you find both plans later by filtering on the labeled SQL text.

Run the same tagged query before and after adding the index

Execute the same OPTION (LABEL = ...) query before and after creating the support index so Query Store captures two plan variants.

USE stoxx;
IF EXISTS (
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID('dbo.qs_force_demo')
      AND name = 'IX_qs_force_demo_symbol_date'
)
    DROP INDEX IX_qs_force_demo_symbol_date ON dbo.qs_force_demo;
 
SELECT COUNT(*) AS matching_rows
FROM dbo.qs_force_demo
WHERE symbol = 'ASML.AS'
  AND [date] BETWEEN '2025-03-01' AND '2025-04-30'
OPTION (LABEL = 'qs_force_demo_count');
 
CREATE INDEX IX_qs_force_demo_symbol_date
    ON dbo.qs_force_demo(symbol, [date]);
 
SELECT COUNT(*) AS matching_rows
FROM dbo.qs_force_demo
WHERE symbol = 'ASML.AS'
  AND [date] BETWEEN '2025-03-01' AND '2025-04-30'
OPTION (LABEL = 'qs_force_demo_count');
| matching_rows |
|---:|
| 41 |

Both executions return the same 41 rows, which is exactly what a plan-forcing example needs: query semantics unchanged, only the available access path differs. OPTION (LABEL = 'qs_force_demo_count') is a SQL 2022 feature that attaches a literal label to the query text, making it trivially searchable in sys.query_store_query_text afterward — without the label, you would have to filter by the SQL body fragment.

The CASE WHEN qsp.query_plan LIKE '%...%' construction extracts a simplified access_pattern from the stored plan XML for human-readable comparison. This is a pragmatic shortcut — the authoritative form would parse the plan as XML with .query('...') — but for triage work “scan vs seek” is almost always enough to identify the better plan.

Compare the scan and seek plans stored by Query Store

Read both plans for the labeled query and pull their access pattern, average duration, and average logical reads.

SELECT
    qsq.query_id,
    qsp.plan_id,
    CASE
        WHEN qsp.query_plan LIKE '%Clustered Index Scan%' THEN 'Clustered Index Scan'
        WHEN qsp.query_plan LIKE '%Index Seek%' THEN 'Index Seek'
        WHEN qsp.query_plan LIKE '%Table Scan%' THEN 'Table Scan'
        ELSE 'Other'
    END AS access_pattern,
    qsp.is_forced_plan,
    qsp.last_force_failure_reason_desc,
    CAST(AVG(qsrs.avg_duration) / 1000.0 AS decimal(18,3)) AS avg_ms,
    CAST(AVG(qsrs.avg_logical_io_reads) AS decimal(18,2)) AS avg_logical_io_reads,
    LEFT(qsqt.query_sql_text, 180) AS query_text
FROM sys.query_store_runtime_stats AS qsrs
JOIN sys.query_store_plan AS qsp ON qsrs.plan_id = qsp.plan_id
JOIN sys.query_store_query AS qsq ON qsp.query_id = qsq.query_id
JOIN sys.query_store_query_text AS qsqt ON qsq.query_text_id = qsqt.query_text_id
WHERE qsqt.query_sql_text LIKE '%qs_force_demo_count%'
GROUP BY qsq.query_id, qsp.plan_id, qsp.query_plan, qsp.is_forced_plan, qsp.last_force_failure_reason_desc, qsqt.query_sql_text
ORDER BY avg_logical_io_reads DESC;
| query_id | plan_id | access_pattern | is_forced_plan | last_force_failure_reason_desc | avg_ms | avg_logical_io_reads | query_text |
|---:|---:|---|---:|---|---:|---:|---|
| 3161 | 595 | `Clustered Index Scan` | 0 | `NONE` | 2.393 | 411.00 | `SELECT COUNT(*) AS matching_rows FROM dbo.qs_force_demo WHERE symbol = 'ASML.AS' AND [date] BETWEEN '2025-03-01' AND '2025-04-30' OPTION (LABEL = 'qs_force_demo_count')` |
| 3161 | 594 | `Index Seek` | 0 | `NONE` | 0.057 | 4.00 | `SELECT COUNT(*) AS matching_rows FROM dbo.qs_force_demo WHERE symbol = 'ASML.AS' AND [date] BETWEEN '2025-03-01' AND '2025-04-30' OPTION (LABEL = 'qs_force_demo_count')` |

This is a textbook forcing candidate. The same query_id (3161) has two plans: plan_id = 595 is a clustered index scan doing 411 logical reads in 2.393 ms average, and plan_id = 594 is an index seek doing 4 logical reads in 0.057 ms average. Both plans have is_forced_plan = 0 (neither has been forced yet) and last_force_failure_reason_desc = NONE (no prior force attempts have failed). The seek plan is roughly 100 times cheaper and dramatically faster — the kind of gap that justifies temporary forcing while the underlying cause is stabilized.

ColumnValueStateMeaningImplication
query_idSame across rowsHealthyOne logical query has multiple plans.True regression comparison available.
plan_idDifferent across rowsHealthyDistinct plan variants exist.Query Store can compare and force among them.
access_patternClustered Index ScanProblem (in this example)Full base-structure scan.Poor access path for this selective predicate.
access_patternIndex SeekHealthy (in this example)Targeted access path.Lower reads and lower latency.
is_forced_plan0NormalPlan is not currently forced.Observation-only state.
last_force_failure_reason_descNONEHealthyNo force failure recorded.Safe to proceed with forcing if justified.
avg_logical_io_readsVery different between plansCandidateMaterially different data-access cost.Strong sign that plan choice matters.

Force the better plan

Plan forcing is implemented by sp_query_store_force_plan, which takes a query_id and a plan_id and marks that plan as the preferred plan for that query. After forcing, SQL Server will attempt to use the forced plan on every subsequent execution. If it cannot (schema change, parameter bind failure, required index missing), it logs a force failure in sys.query_store_plan.last_force_failure_reason_desc and falls back to recompiling a fresh plan. Always verify the force actually took effect — do not assume the stored procedure’s success indicates the forced plan is active.

Force the low-read plan and verify the force state

Plan forcing is a temporary operational control

  • Forced plans can become stale after schema changes, data-distribution shifts, or index maintenance.
  • Forcing does not fix root causes — it pins the engine to one historical plan.
  • The forced plan may stop being applicable (for example, after dropping the index that made it good) and fall back to a different plan silently if you do not monitor.
  • Review forced plans on a schedule; unforce once the underlying issue is corrected.

Validate before forcing and verify after

  • Force only a plan you have validated on the current workload.
  • Record the reason for the force in a change-management system.
  • Verify is_forced_plan = 1 in sys.query_store_plan after calling the procedure.
  • Monitor force_failure_count and last_force_failure_reason_desc continuously.

Force plan_id = 594 (the low-read seek plan) and then immediately verify the force state by reading back sys.query_store_plan.

DECLARE @query_id bigint = 3161;
DECLARE @plan_id  bigint = 594;
 
EXEC sys.sp_query_store_force_plan
    @query_id = @query_id,
    @plan_id  = @plan_id;
 
SELECT
    plan_id,
    is_forced_plan,
    force_failure_count,
    last_force_failure_reason_desc,
    CAST(AVG(qsrs.avg_duration) / 1000.0 AS decimal(18,3)) AS avg_ms,
    CAST(AVG(qsrs.avg_logical_io_reads) AS decimal(18,2)) AS avg_logical_io_reads
FROM sys.query_store_plan AS qsp
JOIN sys.query_store_runtime_stats AS qsrs ON qsp.plan_id = qsrs.plan_id
WHERE qsp.query_id = @query_id
GROUP BY plan_id, is_forced_plan, force_failure_count, last_force_failure_reason_desc
ORDER BY avg_logical_io_reads DESC;
| plan_id | is_forced_plan | force_failure_count | last_force_failure_reason_desc | avg_ms | avg_logical_io_reads |
|---:|---:|---:|---|---:|---:|
| 595 | 0 | 0 | `NONE` | 2.393 | 411.00 |
| 594 | 1 | 0 | `NONE` | 0.057 | 4.00 |

The force worked cleanly. Plan 594 now has is_forced_plan = 1, force_failure_count = 0, and last_force_failure_reason_desc = NONE. Plan 595 is still in the catalog as a historical record but is not forced — SQL Server will only use it if forcing the preferred plan fails. This is exactly the verification step to perform in production after forcing a plan: do not assume the stored procedure returning without error means the forced plan is active, because forcing can silently degrade to fallback on the first execution.

ColumnValueStateMeaningImplication
is_forced_plan1ForcedThis plan is currently forced.Query Store will try to use it on every execution.
is_forced_plan0NormalPlan is not forced.Normal for alternative or historical plans.
force_failure_count0HealthyNo force failures recorded.Forcing is currently working.
force_failure_count> 0ProblemSQL Server has failed to apply the forced plan.Investigate validity, schema changes, or missing index.
last_force_failure_reason_descNONEHealthyNo last-known failure.Normal operating state.
last_force_failure_reason_descAnything elseProblemQuery Store recorded a force-application problem.The forced plan may not actually be active.

Apply a Query Store Hint

Query Store Hints are a SQL Server 2022+ feature that lets you attach an OPTION(...) clause to a query without editing the source SQL text. The hint is stored in sys.query_store_query_hints and applied by the optimizer on every subsequent execution of that query_id. This is the right tool when you cannot change application code quickly — but it is still operational debt that must be tracked and eventually removed.

Add a Query Store Hint without changing code

Hints are operational debt

  • A hint can outlive the condition that made it useful and become the new problem.
  • Hints are not persistent fixes; they override optimizer decisions statically.
  • Long-lived hints accumulate and make the engine harder to reason about.
  • Every hint should have an owner, a reason, and an expiration date.

Track every active hint explicitly

  • Use Query Store Hints when code cannot be changed quickly — never as a first resort.
  • Remove hints as soon as the root cause is fixed.
  • Audit sys.query_store_query_hints on a schedule to find stale hints.
  • Record the reason, author, and removal-date criteria in a change log.

Unforce the demo plan (so the hint example is isolated), apply a Query Store Hint to the same query_id, inspect sys.query_store_query_hints, then clean up with sp_query_store_clear_hints.

DECLARE @query_id bigint = 3161;
DECLARE @plan_id  bigint = 594;
 
EXEC sys.sp_query_store_unforce_plan
    @query_id = @query_id,
    @plan_id  = @plan_id;
 
EXEC sys.sp_query_store_set_hints
    @query_id    = @query_id,
    @query_hints = N'OPTION(RECOMPILE, MAXDOP 1)';
 
SELECT
    query_hint_id,
    query_id,
    query_hint_text,
    source_desc,
    last_query_hint_failure_reason_desc,
    query_hint_failure_count,
    comment
FROM sys.query_store_query_hints
WHERE query_id = @query_id;
 
EXEC sys.sp_query_store_clear_hints
    @query_id = @query_id;
| query_hint_id | query_id | query_hint_text | source_desc | last_query_hint_failure_reason_desc | query_hint_failure_count | comment |
|---:|---:|---|---|---|---:|---|
| 2 | 3161 | `OPTION(RECOMPILE, MAXDOP 1)` | `User` | `NONE` | 0 | `NULL` |

The hint was created successfully. query_hint_text shows the exact OPTION(...) clause Query Store will apply. source_desc = User indicates a person or explicit process installed the hint. last_query_hint_failure_reason_desc = NONE and query_hint_failure_count = 0 together confirm no application failure has occurred — the hint is syntactically valid and applicable in the query’s context. The comment column is NULL here because sp_query_store_set_hints does not accept a comment argument; the vault convention is to track hint rationale in an external change log or ticket system.

ColumnValueStateMeaningImplication
query_hint_textExplicit OPTION(...)NormalThe exact hint Query Store will try to apply.Always review this text literally; small mistakes matter.
source_descUserNormalA person or explicit process created the hint.Operationally trackable in your change log.
source_descNon-user sourceInvestigateHint came from another subsystem.Verify why it exists before modifying it.
last_query_hint_failure_reason_descNONEHealthyNo known hint-application failure.Hint is applicable in the current context.
last_query_hint_failure_reason_descAnything elseProblemHint failed to apply at least once.Investigate before assuming the hint is helping.
query_hint_failure_count0HealthyNo recorded failures.Healthy hint state.
query_hint_failure_count> 0ProblemHint has failed to apply.Possible syntax mismatch, unsupported hint in context, or invalid option.
commentNULLNormalNo extra annotation stored.Track rationale in an external change log.

Forcing attempts — not guarantees — and unsupported hints

is_forced_plan = 1 guarantees only that Query Store will attempt to apply the plan on every subsequent execution. Schema changes, missing indexes, and parameter incompatibilities can cause the force to silently fall back to recompilation — watch sys.query_store_plan.force_failure_count and last_force_failure_reason_desc for the specific cause. If sp_query_store_force_plan returns without error but is_forced_plan is still 0, the most common explanation is that Query Store has not yet flushed the in-memory state to disk; call sp_query_store_flush_db and re-query. Query Store Hints do not support USE PLAN (replaced by plan forcing itself), OPTIMIZE FOR (@var = val), MAXRECURSION, or any table hints.

Operational Guidance

Intervention matrix

Choose the action that matches the problem, not the action that feels most powerful.

Observe only when Query Store has multiple plans but no clear winner yet. The strength is low, the risk is mostly wasted time on noise, and the exit is to collect more runtime and compare again.

Force a plan when one historical plan is clearly safer and the root cause is not yet fixed. The strength is medium, the risk is that the forced plan becomes stale after schema or data changes, and the exit is to unforce after fixing stats, indexes, or query design.

Use a Query Store Hint when code cannot be changed quickly and you need a targeted mitigation. The strength is medium, the main risk is long-lived technical debt, and the exit is to remove the hint after the permanent fix lands.

Choose the root-cause fix when stats, indexing, predicates, parameterization, or schema are the real issue. The strength is highest, the cost is more effort and testing, and Query Store should stay in the role of validation rather than a crutch.

What to watch after forcing or hinting

Define the healthy and unhealthy states for each intervention signal so on-call can triage quickly.

Treat is_forced_plan as healthy when it stays at 1. If it disappears or force_failure_count rises, re-check last_force_failure_reason_desc and recent schema changes.

Treat runtime spread as healthy when the best and worst plans converge. If the forced or hinted plan still underperforms, reopen root-cause analysis because forcing was not enough.

Treat last_query_hint_failure_reason_desc as healthy when it remains NONE. If it becomes non-NONE or the failure count rises, remove or correct the hint.

Treat plan_count as healthy when it stays in a stable small range, roughly 1 to 5. If it keeps growing unexpectedly, check parameter sensitivity, context settings, and workload churn.

Treat current_storage_size_mb as healthy when it stays well below the maximum. If it climbs toward the ceiling, lower STALE_QUERY_THRESHOLD_DAYS or raise MAX_STORAGE_SIZE_MB.

Query Store as validation, not crutch

  • Force or hint only to buy time for a root-cause fix, never as the permanent solution.
  • Record every force and every hint in a change log with owner, reason, and removal date.
  • Re-evaluate all active forces and hints quarterly.
  • Use sys.query_store_plan and sys.query_store_query_hints as your source of truth — never trust memory or tribal knowledge about what is currently forced or hinted.

SQL Server Query Store Regressions and Plan Forcing References

Graph View

Backlinks