Users start waiting longer after a release that passed its smoke test. Query Store can identify regressed queries by comparing captured workload windows around the deployment. Weight the averages by execution count, keep the plans beside them, and distinguish a changed plan from changed query text.
Choose Windows That Represent the Workload
A before-and-after comparison needs more than the deployment time. It needs representative observation windows on both sides. Match business activity as closely as possible. Comparing an overnight batch with a quiet daytime period produces a convincing table with an unconvincing conclusion.
I confirm Query Store was capturing before the release first. Missing historical evidence cannot be reconstructed by enabling capture afterward. Check its actual state, retention, storage limits, and capture mode. The necessary query and runtime intervals must still exist.
The example uses explicit datetimeoffset boundaries with UTC offsets. Replace the synthetic dates with your reviewed deployment and observation boundaries. Excluding an interval that straddles the release avoids mixing both versions into one side. That also leaves a small gap in the comparison, which should be stated in the review. A precise-looking timestamp does not make a blended interval precise.
Keep the comparison windows stable when looking for regressed queries, and record any deployment or workload change between them.
Rank Regressed Queries by Weighted Duration and CPU
Query Store runtime averages belong to a plan and interval, with an execution count. Average those averages without their counts and a lightly used interval can outweigh a busy one. Multiply each average by count_executions, sum the products, and divide by total executions.
The query below combines regular executions only. Query Store can contain multiple rows for the active interval, so aggregating the rows is important. Duration and CPU values use microseconds. The output retains that unit rather than inventing a measured conversion result for your server.
The comparison joins by query_id. That identifies the same captured query across the windows, but only while its identity remains the same. I keep execution counts beside the averages because a dramatic increase based on a very small sample deserves a different level of confidence. The delta ranks candidates for investigation, not confirmed causes of every reported delay.
DECLARE @Start datetimeoffset='2026-09-24T08:00:00+00:00';
DECLARE @Release datetimeoffset='2026-09-24T10:00:00+00:00';
DECLARE @End datetimeoffset='2026-09-24T12:00:00+00:00';
WITH Metrics AS
(
SELECT p.query_id,
CASE WHEN i.end_time<=@Release THEN 'Before' ELSE 'After' END AS WindowName,
SUM(CONVERT(float,r.count_executions)*r.avg_duration)
/NULLIF(SUM(r.count_executions),0) AS DurationUS,
SUM(CONVERT(float,r.count_executions)*r.avg_cpu_time)
/NULLIF(SUM(r.count_executions),0) AS CPUUS,
SUM(r.count_executions) AS Executions
FROM sys.query_store_runtime_stats AS r
JOIN sys.query_store_plan AS p ON p.plan_id=r.plan_id
JOIN sys.query_store_runtime_stats_interval AS i
ON i.runtime_stats_interval_id=r.runtime_stats_interval_id
WHERE r.execution_type=0 AND i.start_time>=@Start AND i.end_time<=@End
AND (i.end_time<=@Release OR i.start_time>=@Release)
GROUP BY p.query_id,CASE WHEN i.end_time<=@Release THEN 'Before' ELSE 'After' END
)
SELECT b.query_id,q.object_id,b.Executions AS BeforeExecutions,a.Executions AS AfterExecutions,
b.DurationUS AS BeforeDurationUS,a.DurationUS AS AfterDurationUS,
b.CPUUS AS BeforeCPUUS,a.CPUUS AS AfterCPUUS,
a.DurationUS-b.DurationUS AS DurationIncreaseUS,qt.query_sql_text
FROM Metrics AS b
JOIN Metrics AS a ON a.query_id=b.query_id AND a.WindowName='After'
JOIN sys.query_store_query AS q ON q.query_id=b.query_id
JOIN sys.query_store_query_text AS qt ON qt.query_text_id=q.query_text_id
WHERE b.WindowName='Before'
ORDER BY DurationIncreaseUS DESC;Place the Plans Beside the Candidate
Did the slower query use a different plan after the deployment? Inspect which plans executed in each observation window before attributing the change to a new plan. A plan's creation time alone does not prove it supplied all the runtime work in the window.
The next query lists plan identifiers and execution counts by interval for a selected query. Replace the demonstration identifier with a candidate from the first result. Open query_plan as XML in SSMS to compare operators, estimates, indexes, and relevant warnings.
Check whether the slower average came from a different mix of parameter values or executions under the same plan. That distinction changes the next step. A plan regression and a workload shift can coexist, so preserve both explanations until the evidence separates them. Do not force a plan simply because its picture looks simpler than the newer plan.
DECLARE @QueryID bigint=1;
SELECT p.plan_id,i.start_time,i.end_time,r.count_executions,
r.avg_duration,r.avg_cpu_time,TRY_CONVERT(xml,p.query_plan) AS QueryPlan
FROM sys.query_store_plan AS p
JOIN sys.query_store_runtime_stats AS r ON r.plan_id=p.plan_id
JOIN sys.query_store_runtime_stats_interval AS i
ON i.runtime_stats_interval_id=r.runtime_stats_interval_id
WHERE p.query_id=@QueryID AND r.execution_type=0
ORDER BY i.start_time,p.plan_id;
Match Changed Procedures Without Inventing a Pair
A deployment that changes statement text can produce a new query_id. The before-and-after inner join then excludes that new query because there is no matching old identity. That absence is a limitation of the comparison, not proof that the changed code performed well.
Use object_id to group statements belonging to a stored procedure when that relationship exists. The next query lists captured statements for one procedure so you can inspect the old and new texts. A procedure can contain several statements, so object_id alone does not supply a one-to-one statement mapping.
Review the deployment's actual text changes and match the logical operations deliberately. Dynamic SQL and ad hoc statements can have object_id equal to zero and need another approach. Preserve unmatched new statements in a separate review list. The SQL is evidence for the mapping, while the code change explains which business operation each statement represents.
SELECT q.query_id,q.object_id,q.last_execution_time,qt.query_sql_text
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt ON qt.query_text_id=q.query_text_id
WHERE q.object_id=OBJECT_ID(N'dbo.ReleaseProcedure')
ORDER BY q.last_execution_time DESC,q.query_id;Investigate Resource Changes Alongside Duration
A longer duration with stable CPU can point toward waits or changed concurrency. Increased CPU or reads points toward a different kind of execution cost. Query Store wait statistics, actual plans, and targeted request sampling provide the follow-up. Avoid turning a duration delta into an automatic index recommendation.
Ask whether the affected query runs on the user-visible path. A large increase in a rarely used administrative statement deserves different priority from a smaller increase in a frequently called request. Include execution frequency and business impact when ranking the candidates.
For an identified plan regression, Query Store plan forcing can provide a targeted response when a suitable earlier plan remains usable. Check forcing failures and validate the restored behavior. A plan force is a decision to monitor, not a permanent certificate that the query is repaired. Data changes can make that decision stale.
Recheck Regressed Queries After the Fix
Repeat the comparison using another representative window after the chosen fix. Keep the same query identities, metric definitions, and execution-type filters. If the text changed again, explicitly rebuild the logical mapping instead of quietly comparing unrelated identities.
Record the capture gaps and excluded boundary intervals. Those limits explain what the result can support. Also retain candidates that improved, because the deployment's overall behavior includes both improvements and regressions. The final decision should not depend on the largest alarming row alone.
Finding regressed queries is the start of a focused investigation. Confirm the query identity, identify the active plans, examine resource behavior, and retest the response. Query Store provides durable evidence across the release. Careful window selection and weighted arithmetic make that evidence useful enough to act on.
Related reading on this blog: Comparing a Good and a Bad Plan for One Query in Query Store and Forcing a Plan in Query Store and Checking That It Held.
A deployment timestamp is not a diagnosis, it is a boundary for comparing captured work.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
