Plan Regression Detection, Plan Forcing, Query Store Hints, and Operational Governance

Learning outcomes

Suppose ServiceHub’s order-history endpoint was healthy for weeks, then a deployment window shows higher CPU, reads, and duration. “Force the old plan” can be a powerful emergency move, but it is only safe after proving that the old plan is actually better for the current workload and after recording how to reverse the intervention. This lesson treats Query Store as an incident evidence base, not a plan-pinning button.

Lab bootstrap

This lesson is independently runnable. It recreates the same disposable Query Store database if necessary and leaves ServiceHubLab untouched.

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

1. A regression is a comparison, not a slow-query screenshot

A plan regression means performance became materially worse after plan choice changed, for a comparable workload. Query Store gives you multiple plans and time-bucketed execution statistics, but you still must account for parameter mix, row counts, concurrency, deployment timing and data growth. A plan that is slower for customer 1 but much faster for thousands of rare customers might not be a regression at all.

USE ServiceHubQSLab;GOEXEC lab11.GetOrdersByCustomer @customer_id=1;EXEC lab11.GetOrdersByCustomer @customer_id=1;EXEC lab11.GetOrdersByCustomer @customer_id=37;EXEC lab11.GetOrdersByCustomer @customer_id=812;GOSELECT q.query_id,p.plan_id,p.plan_type_desc,p.is_forced_plan,       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_readsFROM 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_idLEFT JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id=p.plan_idWHERE qt.query_sql_text LIKE N'%lab11.WorkOrderFact%customer_id=@customer_id%'GROUP BY q.query_id,p.plan_id,p.plan_type_desc,p.is_forced_planORDER BY q.query_id,p.plan_id;GO

The numbers are local observations only. Do not copy them into a production runbook as thresholds. The useful artifact is the comparison method: same query identity, explicit time/plan, execution counts, and representative parameters.

2. Plan forcing is a governed attempt, not a guarantee of an identical binary plan

sys.sp_query_store_force_plan tells the optimizer to try to reproduce a recorded Query Store plan for the query. SQL Server records whether the plan is forced and can record forcing failures. Current Microsoft guidance explicitly notes that the resulting plan can be the same or similar rather than bit-for-bit identical; performance therefore still needs validation.

In the disposable lab, forcing the existing plan is enough to learn the state transition without manufacturing a fake performance win. If the query has multiple plans, choose only after comparing representative runtime evidence.

USE ServiceHubQSLab;GODECLARE @query_id bigint,@plan_id bigint;SELECT TOP (1) @query_id=q.query_id,@plan_id=p.plan_idFROM 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_idWHERE qt.query_sql_text LIKE N'%lab11.WorkOrderFact%customer_id=@customer_id%'ORDER BY p.last_execution_time DESC,p.plan_id DESC;IF @query_id IS NOT NULLBEGIN  EXEC sys.sp_query_store_force_plan @query_id=@query_id,@plan_id=@plan_id;  SELECT query_id,plan_id,is_forced_plan,force_failure_count,         last_force_failure_reason_desc,is_optimized_plan_forcing_disabled  FROM sys.query_store_plan  WHERE query_id=@query_id;  EXEC lab11.GetOrdersByCustomer @customer_id=1;  EXEC lab11.GetOrdersByCustomer @customer_id=812;  EXEC sys.sp_query_store_unforce_plan @query_id=@query_id,@plan_id=@plan_id;END;GO

3. Query Store hints shape a query externally

Query Store hints are persisted database metadata attached to a Query Store query_id. They are useful when application SQL cannot be edited quickly, but they apply broadly to that query identity and can override statement-level or plan-guide hints. Microsoft therefore recommends them as last-resort interventions, with testing and periodic reevaluation.

USE ServiceHubQSLab;GODECLARE @query_id bigint;SELECT TOP (1) @query_id=q.query_idFROM sys.query_store_query_text AS qtJOIN sys.query_store_query AS q ON q.query_text_id=qt.query_text_idWHERE qt.query_sql_text LIKE N'%lab11.WorkOrderFact%customer_id=@customer_id%'ORDER BY q.query_id DESC;IF @query_id IS NOT NULLBEGIN  EXEC sys.sp_query_store_set_hints    @query_id=@query_id,    @query_hints=N'OPTION(MAXDOP 1)';  SELECT query_id,query_hint_text,last_query_hint_failure_reason_desc,         query_hint_failure_count,source_desc  FROM sys.query_store_query_hints  WHERE query_id=@query_id;  EXEC lab11.GetOrdersByCustomer @customer_id=37;  EXEC sys.sp_query_store_clear_hints @query_id=@query_id;END;GO

sys.sp_query_store_set_hints replaces an existing Query Store hint for that query when you set a new value. Record the previous state before changing it. Clearing removes all Query Store hints for the query ID, so rollback must be deliberate if multiple hint clauses were previously combined.

4. Wrong approach: force the “fastest plan” forever

A common anti-pattern is to sort Query Store by one historical average, force the smallest number, and forget the intervention. That ignores parameter populations, data distribution changes, memory/concurrency, schema evolution, compatibility upgrades, and Intelligent Query Processing features that may produce better choices later. Long-lived forcing can also suppress useful adaptive behavior.

For every intervention, ServiceHub records: incident/change ID; affected query hash/query ID; baseline interval; plan IDs compared; parameter classes tested; hypothesis; chosen intervention; permissions used; owner; expiry date; exact rollback command; and post-change metrics. After large data changes, index/schema changes, a compatibility-level promotion, or major SQL Server servicing, reevaluate forced plans and Query Store hints.

5. SQL Server 2025 topology and automatic-tuning boundaries

SQL Server 2025 adds Query Store capabilities for readable Availability Group secondary replicas. When that feature is enabled, hints and plan forcing can be scoped with replica-group information, and new catalog views expose forcing locations. That is topology-dependent and not part of this single-instance mandatory lab. Do not assume a hint applied on the primary automatically has the same meaning on every readable secondary unless Query Store for secondaries is deliberately configured.

Automatic plan correction/automatic tuning also has platform and edition support boundaries. Inspect sys.database_automatic_tuning_options before discussing it as active; a desired setting can be NOT_SUPPORTED, Query Store can be off/read-only, or policy may intentionally keep it disabled. The mandatory lesson uses manual evidence/rollback so every learner can reproduce the governance pattern locally.

Authoritative references

• Tune performance with Query Store — regression comparison and plan forcing workflow
• sp_query_store_force_plan — forcing, failure and permission semantics
• Query Store hints — persisted external hints and secondary-replica behavior
• Query Store hints best practices — governance and reevaluation guidance
• Optimized plan forcing — optimization replay and forcing behavior
• SQL Server 2025 build versions — current servicing baseline