Catching regressed queries with Query Store

/*
================================================================================
SQL Server Query Store Plan Regression Diagnostics
================================================================================
Description:
    Wrapper for sp_DBA_QueryStoreRegressions. Detects true plan regressions —
    queries with multiple plans where the recent slow plan performs significantly
    worse than the best historical plan for the same query_id.

Output:
    (1) Plan regressions with query text, plan IDs, regression percentage
    (2) Forced plans and force failure counts

Action:
    For regression plans > @RegressionPctThreshold: force the baseline plan:
        EXEC sp_query_store_force_plan @query_id, @baseline_plan_id;
    Monitor forced plans for regression. If regression does not resolve,
    investigate index changes or statistics updates that may have caused the
    plan change. Run after any significant database changes (index maintenance,
    statistics update, large data loads).

Parameters:
    @DatabaseList - comma-separated database names or NULL for all user DBs
    @RegressionPctThreshold - minimum regression % to flag (default 50)
    @RecentHours - lookback for regression data (default 24)
    @LookbackHours - historical window for baseline plans (default 168)

Prerequisites: sp_DBA_QueryStoreRegressions (deploy via 00_Framework/00_Deploy_Framework.ps1)

Criticality: High
Author:        Ravi Sharma
================================================================================
*/

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;

DECLARE @DatabaseList NVARCHAR(MAX) = NULL;
DECLARE @RegressionPctThreshold DECIMAL(10,2) = 50.0;
DECLARE @RecentHours INT = 24;
DECLARE @LookbackHours INT = 168;

IF OBJECT_ID(N'dbo.sp_DBA_QueryStoreRegressions', N'P') IS NULL
BEGIN
    SELECT
        N'Query Store Regression Dependency' AS [Result_Set],
        N'WARNING' AS [Severity],
        N'dbo.sp_DBA_QueryStoreRegressions' AS [Dependency],
        N'Not deployed' AS [Status],
        N'This ad-hoc regression report was skipped because the optional framework procedure is not available.' AS [Message],
        N'Deploy 00_Framework/sp_DBA_QueryStoreRegressions.sql or run 00_Framework/00_Deploy_Framework.ps1, then re-run this script.' AS [How_To_Deploy];
    RETURN;
END;

EXEC dbo.sp_DBA_QueryStoreRegressions
    @DatabaseList = @DatabaseList,
    @RegressionPctThreshold = @RegressionPctThreshold,
    @RecentHours = @RecentHours,
    @LookbackHours = @LookbackHours,
    @MinExecutions = 5,
    @TopPerDatabase = 20,
    @IncludeForcedPlans = 1;

Dummy guide: Query Store regression views beat guesswork after a release.

Filter to queries whose average duration or CPU jumped across the change window, open the plan history, and note whether the shape changed or only estimates drifted.

If the new plan is consistently worse, force the prior plan only after you understand why the optimizer flipped — otherwise you paper over a stats or index problem that will return.

Why regression investigation deserves a method

The central principle is that Query Store helps separate a code regression from a plan change by preserving historical runtime and plan evidence. A single metric, recommendation, or successful command is not enough. The decision has to connect an observed symptom to a plausible cause, a controlled change, and a result that can be verified under the workload that matters.

Evidence to collect

Use evidence from the same time window whenever possible:

• duration, CPU, and reads by interval
• plan ids and first-use time
• execution count and parameter variation
• deployments and statistics events

A practical workflow

1. Define the regression window.
2. Compare plans and runtime distributions.
3. Reproduce the responsible parameter class.
4. Mitigate, validate, and continue monitoring.

Common mistakes

Avoid these shortcuts:

• sorting only by total duration
• comparing unequal time windows
• forcing a plan before checking data and schema changes

Verification and completion

Define completion before making the change. Confirm correctness first, then compare latency, throughput, reads, CPU, waits, storage behavior, or recovery time as appropriate for Catching regressed queries with Query Store. Test negative and boundary cases, not only the path expected to succeed. Watch the system long enough to cover the relevant workload pattern.

Download and run the diagnostic

Before you run it

• Run the script first on a non-production or read-only connection when possible.
• Review the complete SQL file before execution and confirm that the connected instance is the intended target.
• Required access: VIEW DATABASE STATE; the optional DBA framework procedure described in the script.
• Open SQL Server Management Studio or Azure Data Studio, connect to the instance, open the downloaded file, and select Execute. Do not run diagnostic scripts through an application login.
• Save the result grid with the server name, collection time, and incident window. DMV output changes over time and often resets after a SQL Server restart.

What the script does

Calls the repository regression procedure to compare recent plans with historical baselines and returns regression percentage, plan IDs, text, and forced-plan status. The download is intended for diagnosis and does not apply an automatic remediation. Read every statement anyway, especially when adapting the script for automation or a different SQL Server version.

How to read the output

Start with regressions that have enough executions and high business impact. Compare the recent plan ID with the baseline plan ID, then review duration, CPU, reads, parameter class, and the change window before forcing a plan.