The query text is unchanged, but somebody says it was fast last week. Query Store plan history lets you check which plans actually ran and when. Start with the evidence before blaming the newest plan.
Check Whether Plan History Exists
Query Store belongs to a database. Run these queries in the database that executes the statement, rather than in master. The feature was introduced in SQL Server 2016. Reading its performance catalog views on SQL Server 2022 and later requires VIEW DATABASE PERFORMANCE STATE. Earlier releases use VIEW DATABASE STATE. Forcing a plan requires ALTER permission on the database.
I check collection status before searching for a regression. A read-only store, a restrictive capture policy, or removed history changes what the evidence can tell you. The options query exposes the current state, capture mode, storage use, and interval length. Do not change those settings just to fill a missing historical gap.
Was this statement being captured before the slowdown began? If no earlier plan survives, say that plainly. Enabling collection today cannot reconstruct last week’s executions. You can still examine the current behavior and collect a comparison going forward. History is useful, but it has no time machine attachment.
SELECT actual_state_desc, desired_state_desc,
query_capture_mode_desc, readonly_reason,
current_storage_size_mb, max_storage_size_mb,
interval_length_minutes
FROM sys.database_query_store_options;Find the Query ID Before Choosing a Plan
A plan belongs to a query_id, and that query belongs to recorded query text and context. Similar-looking SQL can have separate query IDs. Different context settings and statement forms matter. Do not force a plan simply because a text fragment looks familiar.
Use a distinctive fragment from the real statement in the next query. The default search value is only a runnable starting point. It is deliberately broad and is not a recommendation for identifying an application query. It even returns the search query itself, because that text contains the fragment. Match the complete text and containing object before taking the returned query_id into later examples.
The plan count gives you a quick indication that alternatives exist. It does not say which alternative performed best. A query with one recorded plan can still slow down because of blocking, data growth, or changed parameter values. Keep that possibility open while you work through plan history. The query ID is your anchor, not your diagnosis.
DECLARE @TextFragment nvarchar(200) = N'SELECT';
SELECT TOP (50) q.query_id, q.object_id,
q.context_settings_id, qt.query_sql_text,
COUNT(p.plan_id) AS RecordedPlanCount
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt
ON qt.query_text_id = q.query_text_id
LEFT JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
WHERE qt.query_sql_text LIKE N'%' + @TextFragment + N'%'
GROUP BY q.query_id, q.object_id, q.context_settings_id,
qt.query_sql_text
ORDER BY q.query_id DESC;
Build Plan History With Duration per Interval
Replace the demonstration query ID with the ID you just verified. The next query joins the query to its plans, the plans to runtime statistics, and those statistics to collection intervals. runtime_stats joins through plan_id. The parent query joins through query_id. Mixing those identifiers produces the wrong history.
Duration is recorded in microseconds. This query converts the weighted average to milliseconds for readability. It multiplies each average by its execution count before dividing by the total count. That prevents a small statistics fragment from receiving the same weight as a larger fragment.
The current interval can have multiple statistics rows for the same plan and execution type. Grouping them together is necessary. We include only successful executions here. Review aborted and failed executions separately when the complaint includes timeouts or errors. The reported counts and averages come from your database, not from an invented benchmark.
DECLARE @QueryId bigint = 1;
DECLARE @Since datetimeoffset = DATEADD(day, -14, SYSDATETIMEOFFSET());
SELECT q.query_id, p.plan_id,
i.start_time, i.end_time,
SUM(rs.count_executions) AS ExecutionCount,
SUM(rs.avg_duration * CONVERT(float, rs.count_executions))
/ NULLIF(SUM(CONVERT(float, rs.count_executions)), 0)
/ 1000.0 AS WeightedAvgDurationMs,
MIN(rs.first_execution_time) AS FirstExecutionInInterval,
MAX(rs.last_execution_time) AS LastExecutionInInterval
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval AS i
ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE q.query_id = @QueryId
AND i.end_time > @Since
AND rs.execution_type = 0
GROUP BY q.query_id, p.plan_id, i.start_time, i.end_time
ORDER BY i.start_time, p.plan_id;Read Plan History as a Handoff, Not a Timestamp
Read the ordered intervals around the reported change. Look for a new plan appearing while duration rises, then check whether the old plan stops receiving executions. Multiple plans can overlap in the same interval. The newest compile time does not prove that all executions immediately switched plans.
Interval averages are summaries. They do not show the exact start time and parameter values of every execution. The first and last execution columns narrow the period, but they describe execution completion times. A collection interval also can extend beyond the date boundary used by the filter.
I compare execution counts before calling one plan better than another. A plan used for a small request is not automatically the right plan for a large request. Look at comparable business periods and data selections. A matching change in plan and duration is a strong lead. It still needs a check for changed workload, blocking, and different parameter distributions.
Compare What the Plans Actually Do
Retrieve the plans for the verified query ID and open the XML results in SSMS. Start with access paths, join order, estimated rows, and operators that introduce sorts or memory demands. Check whether an index used by the earlier plan still exists. Also inspect engine version and database compatibility level recorded with each plan.
Query Store saves compiled plans. These are not actual execution plans containing the actual row counts from a particular run. Compare estimates with a carefully collected actual plan when that is appropriate for the workload. Avoid running an expensive production statement merely to satisfy curiosity.
Plan history tells you what changed structurally. It does not explain every reason the optimizer chose differently. Statistics changes, parameter sensitivity, schema changes, and altered settings all deserve review. Keep the query text and context beside the plans. Otherwise you risk comparing two similar statements and blaming a change that never occurred within the same query.
DECLARE @QueryId bigint = 1;
SELECT plan_id, query_id, initial_compile_start_time,
last_compile_start_time, last_execution_time,
engine_version, compatibility_level, is_forced_plan,
TRY_CONVERT(xml, query_plan) AS StoredPlanXml
FROM sys.query_store_plan
WHERE query_id = @QueryId
ORDER BY initial_compile_start_time, plan_id;Make a Temporary Force an Explicit Decision
Forcing a known useful plan is a stabilization step after review. It is not the first query to run. Confirm the query ID, the selected plan ID, and the workload evidence. Record why that plan is appropriate and what would make you remove the force.
The guarded example leaves both IDs unset. Running it unchanged performs no force. Supply the reviewed IDs from the same database to make the operation deliberate. Never copy demonstration IDs into production and assume they identify your statement.
A force can fail if the needed plan shape is no longer valid. It also can preserve a poor choice as data and parameters change. Check subsequent execution behavior and forcing status instead of assuming the stored procedure solved the problem. Recompilation attempts to produce the forced shape, but the mechanism does not guarantee identical XML or a successful force under every condition.
DECLARE @QueryId bigint = NULL;
DECLARE @GoodPlanId bigint = NULL;
IF @QueryId IS NOT NULL AND @GoodPlanId IS NOT NULL
BEGIN
IF NOT EXISTS
(SELECT 1 FROM sys.query_store_plan
WHERE query_id = @QueryId AND plan_id = @GoodPlanId)
THROW 50000, 'The selected plan does not belong to this query.', 1;
EXEC sys.sp_query_store_force_plan
@query_id = @QueryId, @plan_id = @GoodPlanId;
END;Verify the Patch and Give It an Exit
Inspect is_forced_plan, force_failure_count, and the last failure reason after the reviewed change. Then return to interval statistics during representative work. Compare the same kinds of requests and watch for another symptom becoming worse. A successful command is not a workload result.
If the problem comes from missing statistics, an unsuitable index, or parameter-sensitive behavior, address that cause separately. Keep the force only while it remains justified. Once the permanent change is tested, remove the force and observe the optimizer’s choice again. The guarded unforce example uses the same deliberate ID selection.
Good plan history makes this discussion concrete. You can name the query, the prior plan, the affected intervals, and the reason for a temporary intervention. Save that evidence with the review. A forgotten force becomes another undocumented dependency. The final goal is stable behavior across the real workload, with a clear explanation for the plan you allow SQL Server to choose.
SELECT query_id, plan_id, is_forced_plan,
force_failure_count, last_force_failure_reason_desc
FROM sys.query_store_plan
WHERE is_forced_plan = 1;
DECLARE @QueryId bigint = NULL;
DECLARE @PlanId bigint = NULL;
IF @QueryId IS NOT NULL AND @PlanId IS NOT NULL
EXEC sys.sp_query_store_unforce_plan
@query_id = @QueryId, @plan_id = @PlanId;Related reading on this blog: Finding Out Who Changed That Row and Finding The Oldest Query Plan From Cache.
Plan forcing is not a root-cause repair, it is a temporary bridge while you fix the cause.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
