Plan Guides/Hints, USE HINT, Query Store Hints, and Responsible Intervention

Learning outcomes

The optimizer normally deserves the freedom to adapt as data, statistics, hardware, and SQL Server evolve. Hints deliberately narrow that freedom. Sometimes this is necessary—for example, an urgent regression in vendor SQL you cannot edit—but every hint creates a future obligation. ServiceHub therefore treats hints as governed change records with evidence, scope, owner, expiry, rollback, and post-change verification.

Lab bootstrap for an independently runnable lesson

The first hint examples use the same disposable skewed table. The Query Store-hint experiment later uses a separate disposable database so it cannot silently change ServiceHubLab's Query Store configuration.

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab10') IS NULL EXEC(N'CREATE SCHEMA lab10 AUTHORIZATION dbo;');IF OBJECT_ID(N'lab10.WorkOrderFact',N'U') IS NULLBEGIN  CREATE TABLE lab10.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,    closed_at datetime2(0) NULL,    amount decimal(12,2) NOT NULL,    notes varchar(200) NULL,    CONSTRAINT PK_lab10_WorkOrderFact PRIMARY KEY CLUSTERED(work_order_id)  );  ;WITH n AS  (    SELECT TOP (30000)           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 lab10.WorkOrderFact(customer_id,region_code,status,priority,opened_at,closed_at,amount,notes)  SELECT CASE WHEN n <= 18000 THEN 1 ELSE 2 + n % 1999 END,         CASE WHEN n <= 21000 THEN 'N01' WHEN n <= 27000 THEN 'W02' ELSE 'E03' END,         CASE WHEN n % 20 = 0 THEN 'ESCALATED' WHEN n % 5 = 0 THEN 'CLOSED' ELSE 'OPEN' END,         CASE WHEN n <= 21000 THEN 1 ELSE 5 END,         DATEADD(minute,n,'2026-01-01T00:00:00'),         CASE WHEN n % 5=0 THEN DATEADD(minute,n+90,'2026-01-01T00:00:00') END,         CAST(20 + (n % 5000) / 10.0 AS decimal(12,2)),         CASE WHEN n % 37=0 THEN REPLICATE('x',160) END  FROM n;  CREATE INDEX IX_lab10_RegionStatusOpened    ON lab10.WorkOrderFact(region_code,status,opened_at)    INCLUDE(customer_id,priority,amount);  CREATE INDEX IX_lab10_Customer    ON lab10.WorkOrderFact(customer_id)    INCLUDE(region_code,status,opened_at,amount);END;GO

1. Choose the intervention surface deliberately

A table hint changes behavior for one table reference; a join hint constrains join strategy/order implications; a query hint in the OPTION clause affects the statement. USE HINT exposes named optimizer behaviors without requiring legacy trace flags for many scenarios. A plan guide attaches hints or a plan to matching SQL text you cannot edit. Query Store hints, supported in SQL Server 2022+, attach supported hints to a Query Store query_id without changing application text.

These are not equivalent. Query Store hints persist across restarts/failovers and, per current Microsoft documentation, can override hard-coded statement-level hints and plan-guide hints. Unsupported or impossible hint combinations can fail to apply, with failure metadata recorded in sys.query_store_query_hints.

USE ServiceHubLab;GOSELECT actual_state_desc,desired_state_desc,query_capture_mode_desc,       current_storage_size_mb,max_storage_size_mbFROM sys.database_query_store_options;GOSELECT query_id,query_hint_text,last_query_hint_failure_reason_desc,       query_hint_failure_count,source_descFROM sys.query_store_query_hintsORDER BY query_id;GO

This read-only inventory is the right first step. Do not overwrite an existing Query Store hint because a lab says “set one.” The mandatory change experiment below uses a disposable database.

2. In-code hints are visible but still carry debt

An in-code query hint can be appropriate when the query owner controls the statement and evidence supports the change. The source code then documents the intervention close to the query, which can be easier to discover than an external plan guide. But it still needs a reason and an exit condition.

DECLARE @customer_id int=1;SELECT work_order_id,region_code,status,opened_at,amountFROM lab10.WorkOrderFactWHERE customer_id=@customer_id;GODECLARE @customer_id int=1;SELECT work_order_id,region_code,status,opened_at,amountFROM lab10.WorkOrderFactWHERE customer_id=@customer_idOPTION (OPTIMIZE FOR UNKNOWN);GODECLARE @customer_id int=1;SELECT work_order_id,region_code,status,opened_at,amountFROM lab10.WorkOrderFactWHERE customer_id=@customer_idOPTION (USE HINT('DISABLE_PARAMETER_SNIFFING'));GO

Do not decide from one execution. Compare representative parameter populations, compilation CPU, logical reads, elapsed time, waits, and plan shape. If the schema/statistics/query can be corrected safely, prefer that durable fix.

3. Plan guides solve “cannot edit SQL,” but exact matching is operationally brittle

A plan guide can target ad hoc SQL, parameterized templates, or statements inside modules. It is useful when application/vendor text cannot be changed, but matching rules and exact text/parameter definitions make it harder to maintain. A small application release can stop the guide from matching. This is one reason current guidance generally makes Query Store hints easier for supported hint types.

-- Execute as dbo only in a disposable environment.DECLARE @stmt nvarchar(max)=N'SELECT work_order_id,region_code,status,opened_at,amountFROM lab10.WorkOrderFactWHERE customer_id=@customer_id';EXEC sys.sp_create_plan_guide  @name=N'PG_lab10_customer',  @stmt=@stmt,  @type=N'SQL',  @module_or_batch=NULL,  @params=N'@customer_id int',  @hints=N'OPTION (OPTIMIZE FOR UNKNOWN)';GOSELECT name,is_disabled,scope_type_desc,hintsFROM sys.plan_guidesWHERE name=N'PG_lab10_customer';GOEXEC sys.sp_control_plan_guide N'DROP',N'PG_lab10_customer';GO

If exact text or parameters do not match your client-generated SQL, the guide may not affect it. That is an expected failure mode to diagnose, not a reason to keep creating more guides.

4. Query Store hints: external, persisted, reversible

To avoid mutating ServiceHubLab's database-level Query Store state, this experiment creates ServiceHubHintLab, enables Query Store, runs a uniquely tagged query, resolves its query_id, applies MAXDOP 1 as an easily observable hint, verifies metadata, clears the hint, and drops the database. Query Store hints require ALTER permission on the database. The database creation/drop requires suitable instance permission; if your account lacks it, read the script and use an administrator-created disposable database.

USE master;GOIF DB_ID(N'ServiceHubHintLab') IS NOT NULLBEGIN  ALTER DATABASE ServiceHubHintLab SET SINGLE_USER WITH ROLLBACK IMMEDIATE;  DROP DATABASE ServiceHubHintLab;END;GOCREATE DATABASE ServiceHubHintLab;ALTER DATABASE ServiceHubHintLab SET COMPATIBILITY_LEVEL = 170;ALTER DATABASE ServiceHubHintLab SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = ALL);GOUSE ServiceHubHintLab;CREATE TABLE dbo.Probe(  probe_id int IDENTITY PRIMARY KEY,  category int NOT NULL,  payload char(100) NOT NULL DEFAULT REPLICATE('x',100));;WITH n AS( SELECT TOP (5000) ROW_NUMBER() OVER (ORDER BY a.object_id,b.object_id) AS n FROM sys.all_objects a CROSS JOIN sys.all_objects b)INSERT dbo.Probe(category)SELECT n%10 FROM n;GOSELECT COUNT_BIG(*) AS chapter10_hint_probeFROM dbo.ProbeWHERE category=7; -- chapter10_hint_probeGO

DECLARE @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'%chapter10_hint_probe%'ORDER BY q.last_execution_time DESC;SELECT @query_id AS query_id;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_descFROM sys.query_store_query_hintsWHERE query_id=@query_id;EXEC sys.sp_query_store_clear_hints @query_id=@query_id;GOUSE master;ALTER DATABASE ServiceHubHintLab SET SINGLE_USER WITH ROLLBACK IMMEDIATE;DROP DATABASE ServiceHubHintLab;GO

In production you would not create/drop a database. You would identify a real Query Store query_id, preserve baseline runtime/plan evidence, stage the hint, monitor acceptance criteria, and clear it if the result is wrong or its reason expires.

5. SQL Server 2025 adds an operational Query Store hint worth treating with extra care

ABORT_QUERY_EXECUTION is available in SQL Server 2025 as a Query Store hint to block future executions of a known problematic query. It is an operational circuit breaker, not a tuning hint. Existing in-flight execution is not stopped merely because the hint was added, and future attempts fail with an error. This can be valuable during incidents but needs explicit ownership, audit trail, and unblock procedure.

6. Wrong approach: hint the symptom forever

A forced join type or legacy CE hint can hide stale statistics, a missing predicate, poor data modeling, parameter skew, or an index mistake. Hints also age: data volume changes, a SQL Server upgrade changes optimizer capabilities, hardware changes, or a formerly safe maximum degree of parallelism becomes harmful. Every intervention should therefore record the measured regression, query identifier/hash, current plan and parameters, exact hint, owner, deployment date, expiry/review date, rollback command, and metrics that define success.

USE ServiceHubLab;GODROP PROCEDURE IF EXISTS lab10.GetWorkByCustomer;-- Dropping the table also drops its indexes and statistics objects.DROP TABLE IF EXISTS lab10.WorkOrderFact;IF SCHEMA_ID(N'lab10') IS NOT NULLAND NOT EXISTS (SELECT 1 FROM sys.objects WHERE schema_id=SCHEMA_ID(N'lab10'))  EXEC(N'DROP SCHEMA lab10;');GO

Dropping the disposable table removes its indexes and statistics automatically. The chapter's persistent ServiceHub ops schema remains unchanged.

Authoritative references

• Hints (Transact-SQL) — last-resort guidance
• Query hints — OPTION/USE HINT semantics
• Plan guides — external hint/plan attachment
• Query Store hints — persistence, precedence, supported hints and lifecycle
• Query Store hints best practices — SQL Server 2025 ABORT_QUERY_EXECUTION and governance
• SQL Server 2025 build versions — CU/build servicing baseline
• SSMS 22 release notes — current client-tool baseline