Build a Repeatable Workflow for Detecting, Explaining, and Correcting Plan Regressions

Learning outcomes

The final lesson turns Chapters 10–11 into an incident workflow. A plan regression is not resolved when someone finds a “bad-looking operator” or forces a plan; it is resolved when the team can state what changed, why the regression occurred, which intervention was selected, whether representative workload improved, and exactly how the intervention will be monitored and reversed.

Lab bootstrap

USE master;GOIF DB_ID(N'ServiceHubQSLab') IS NULLBEGIN  CREATE DATABASE ServiceHubQSLab;END;GOALTER DATABASE ServiceHubQSLab SET COMPATIBILITY_LEVEL = 170;ALTER DATABASE ServiceHubQSLab SET QUERY_STORE = ON(  OPERATION_MODE = READ_WRITE,  QUERY_CAPTURE_MODE = AUTO,  WAIT_STATS_CAPTURE_MODE = ON,  INTERVAL_LENGTH_MINUTES = 5,  MAX_STORAGE_SIZE_MB = 100,  SIZE_BASED_CLEANUP_MODE = AUTO,  CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 7));GOUSE ServiceHubQSLab;GOIF SCHEMA_ID(N'lab11') IS NULL EXEC(N'CREATE SCHEMA lab11 AUTHORIZATION dbo;');GOIF OBJECT_ID(N'lab11.WorkOrderFact',N'U') IS NULLBEGIN  CREATE TABLE lab11.WorkOrderFact  (    work_order_id bigint IDENTITY(1,1) NOT NULL,    customer_id int NOT NULL,    region_code char(3) NOT NULL,    status varchar(12) NOT NULL,    priority tinyint NOT NULL,    opened_at datetime2(0) NOT NULL,    amount decimal(12,2) NOT NULL,    notes varchar(200) NULL,    CONSTRAINT PK_lab11_WorkOrderFact PRIMARY KEY CLUSTERED(work_order_id)  );  ;WITH n AS  (    SELECT TOP (40000)      ROW_NUMBER() OVER (ORDER BY a.object_id,b.object_id) AS n    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b  )  INSERT lab11.WorkOrderFact(customer_id,region_code,status,priority,opened_at,amount,notes)  SELECT CASE WHEN n <= 24000 THEN 1 ELSE 2 + n % 3999 END,         CASE WHEN n <= 27000 THEN 'N01' WHEN n <= 35000 THEN 'W02' ELSE 'E03' END,         CASE WHEN n % 23 = 0 THEN 'ESCALATED' WHEN n % 5 = 0 THEN 'CLOSED' ELSE 'OPEN' END,         CASE WHEN n <= 27000 THEN 1 ELSE 5 END,         DATEADD(minute,n,'2026-01-01T00:00:00'),         CAST(20 + (n % 8000) / 10.0 AS decimal(12,2)),         CASE WHEN n % 41=0 THEN REPLICATE('x',180) END  FROM n;  CREATE INDEX IX_lab11_Customer    ON lab11.WorkOrderFact(customer_id)    INCLUDE(region_code,status,opened_at,amount);  CREATE INDEX IX_lab11_RegionStatus    ON lab11.WorkOrderFact(region_code,status,opened_at)    INCLUDE(customer_id,priority,amount);END;GOCREATE OR ALTER PROCEDURE lab11.GetOrdersByCustomer  @customer_id intASBEGIN  SET NOCOUNT ON;  SELECT work_order_id,region_code,status,opened_at,amount  FROM lab11.WorkOrderFact  WHERE customer_id=@customer_id  ORDER BY opened_at,work_order_id;END;GO

Generate several executions before beginning the incident workflow. The point is not to manufacture a dramatic regression; the point is to practice the evidence path on a reproducible database.

USE ServiceHubQSLab;GOEXEC lab11.GetOrdersByCustomer 1;EXEC lab11.GetOrdersByCustomer 1;EXEC lab11.GetOrdersByCustomer 17;EXEC lab11.GetOrdersByCustomer 812;EXEC lab11.GetOrdersByCustomer 2401;EXEC lab11.GetOrdersByCustomer 1;GO

1. Step 0: freeze the incident context before tuning

Record the engine build, compatibility level, edition, Query Store state, recovery/topology context, deployment timestamp, application release, relevant configuration, and the user-visible symptom. Without this context, a later plan comparison can confuse a compatibility promotion, hardware change, data growth, index deployment, or workload shift with an optimizer regression.

USE ServiceHubQSLab;GOSELECT SERVERPROPERTY('ProductVersion') AS product_version,       SERVERPROPERTY('ProductUpdateLevel') AS product_update_level,       SERVERPROPERTY('Edition') AS edition,       SERVERPROPERTY('EngineEdition') AS engine_edition;SELECT name,compatibility_level,recovery_model_desc,is_query_store_onFROM sys.databases WHERE database_id=DB_ID();SELECT actual_state_desc,desired_state_desc,readonly_reason,       query_capture_mode_desc,current_storage_size_mb,max_storage_size_mbFROM sys.database_query_store_options;SELECT name,value FROM sys.database_scoped_configurationsWHERE name IN ('PARAMETER_SENSITIVE_PLAN_OPTIMIZATION','CE_FEEDBACK','DOP_FEEDBACK',               'OPTIONAL_PARAMETER_OPTIMIZATION','MEMORY_GRANT_FEEDBACK_PERSISTENCE');GO

2. Identify candidates with time, execution count, and resource change

A top-resource query is not necessarily regressed; it may simply be the busiest correct query. A regression workflow compares a baseline window with an incident window, or compares multiple plans for the same query under comparable workload. Query Store’s persisted intervals make this possible even after cache churn.

USE ServiceHubQSLab;GOSELECT TOP (20)       q.query_id,p.plan_id,p.plan_type_desc,p.is_forced_plan,       MIN(rsi.start_time) AS first_interval,       MAX(rsi.end_time) AS last_interval,       SUM(rs.count_executions) AS executions,       CAST(SUM(rs.avg_duration*rs.count_executions)            /NULLIF(SUM(rs.count_executions),0) AS decimal(18,2)) AS weighted_avg_duration_us,       CAST(SUM(rs.avg_cpu_time*rs.count_executions)            /NULLIF(SUM(rs.count_executions),0) AS decimal(18,2)) AS weighted_avg_cpu_us,       CAST(SUM(rs.avg_logical_io_reads*rs.count_executions)            /NULLIF(SUM(rs.count_executions),0) AS decimal(18,2)) AS weighted_avg_reads,       LEFT(qt.query_sql_text,300) AS query_textFROM sys.query_store_query_text AS qtJOIN sys.query_store_query AS q ON q.query_text_id=qt.query_text_idJOIN sys.query_store_plan AS p ON p.query_id=q.query_idJOIN sys.query_store_runtime_stats AS rs ON rs.plan_id=p.plan_idJOIN sys.query_store_runtime_stats_interval AS rsi  ON rsi.runtime_stats_interval_id=rs.runtime_stats_interval_idGROUP BY q.query_id,p.plan_id,p.plan_type_desc,p.is_forced_plan,qt.query_sql_textORDER BY weighted_avg_duration_us DESC;GO

In a real incident, filter to explicit UTC/local time boundaries that match the release and symptom. Preserve the raw rows or export them before cleanup/retention removes them.

3. Explain before correcting: plan, estimate, waits, stats, parameters, indexes

For a candidate query, compare plan IDs and Showplan XML, but do not stop at operator icons. Ask: did estimated rows diverge from actual rows? Did parameter classes change? Are the statistics relevant to the predicate current and representative? Did an index appear/disappear? Did a memory grant spill? Did waits shift from CPU to I/O or blocking? Is a forced plan or Query Store hint already active? Did PSP/OPPO/feedback create multiple variants?

USE ServiceHubQSLab;GODECLARE @query_id bigint =(  SELECT TOP (1) q.query_id  FROM sys.query_store_query_text AS qt  JOIN sys.query_store_query AS q ON q.query_text_id=qt.query_text_id  WHERE qt.query_sql_text LIKE N'%lab11.WorkOrderFact%customer_id=@customer_id%'  ORDER BY q.query_id DESC);SELECT plan_id,plan_type_desc,is_forced_plan,force_failure_count,       last_force_failure_reason_desc,last_compile_start_time,last_execution_time,       CONVERT(xml,query_plan) AS query_planFROM sys.query_store_plan WHERE query_id=@query_id ORDER BY plan_id;SELECT query_id,query_hint_text,source_desc,last_query_hint_failure_reason_descFROM sys.query_store_query_hints WHERE query_id=@query_id;SELECT plan_id,feature_desc,state_desc,feedback_data,last_updated_timeFROM sys.query_store_plan_feedback ORDER BY last_updated_time DESC;SELECT s.name AS stats_name,sp.last_updated,sp.rows,sp.rows_sampled,sp.modification_counterFROM sys.stats AS sCROSS APPLY sys.dm_db_stats_properties(s.object_id,s.stats_id) AS spWHERE s.object_id=OBJECT_ID(N'lab11.WorkOrderFact')ORDER BY s.name;GO

Query Store plans are compile/execution artifacts, not proof of a causal root cause. Correlate the evidence with the deployment/data timeline and reproduce under representative parameters if possible.

4. Choose the least invasive correction and state its rollback first

Before applying a reversible intervention, write the rollback command. For a forced plan it is sp_query_store_unforce_plan; for a Query Store hint it is sp_query_store_clear_hints; for a scoped configuration change it is the exact prior value. If the rollback cannot be stated, the change is not ready for an incident window.

USE ServiceHubQSLab;GOSELECT query_id,plan_id,is_forced_plan,force_failure_count,       last_force_failure_reason_descFROM sys.query_store_planWHERE is_forced_plan=1 OR force_failure_count>0;SELECT query_id,query_hint_text,source_desc,       last_query_hint_failure_reason_desc,query_hint_failure_countFROM sys.query_store_query_hintsORDER BY query_id;GO

5. Validate like a production change, not a demo

Validation needs representative parameter classes, realistic concurrency, a defined cache/warmup policy, and comparable metrics. State dataset size, MAXDOP, compatibility, Query Store state, and major environmental limits. Do not claim “50% faster” from one local run. At minimum compare execution count, elapsed time distribution, CPU, logical/physical reads, memory grants/spills, waits, row counts, errors/timeouts, and any effect on neighboring workload.

For ServiceHub, an acceptable emergency correction might be: p95 latency returns to the pre-release band for both high-volume and rare customers; logical reads do not regress materially for either class; compilation CPU remains acceptable; no new spill/blocking pattern appears; and the intervention has a review date after the next statistics/data-distribution cycle. These are example categories—not numeric production targets.

6. Cleanup and bridge to tempdb/workspace performance

Chapter 12 moves from plan choice into tempdb, memory grants, spills, sorts, hashes and hidden workspace consumption. Those topics are a natural continuation because a plan regression can manifest as a spill or workspace-pressure incident even when query semantics are unchanged.

The following cleanup is intentionally destructive but affects only the disposable database created by this chapter. Verify the database name before executing it.

USE master;GOIF DB_ID(N'ServiceHubQSLab') IS NOT NULLBEGIN  ALTER DATABASE ServiceHubQSLab SET SINGLE_USER WITH ROLLBACK IMMEDIATE;  DROP DATABASE ServiceHubQSLab;END;GOSELECT DB_ID(N'ServiceHubQSLab') AS should_be_null;GO

Authoritative references

• Tune performance with Query Store — regressed-query and forcing workflows
• Monitoring performance with Query Store — runtime/plan/wait analysis
• Query Store hints best practices — short-term intervention and reevaluation discipline
• Parameter Sensitive Plan optimization — parameter-sensitive plan evidence
• Intelligent Query Processing — adaptive-feature prerequisites and feedback
• SQL Server 2025 build versions — CU/build baseline
• SSMS 22 release notes — tooling baseline