Query Store Hints: Fixing SQL You Cannot Edit

Everything so far has assumed you can change the query. Often you cannot. The SQL may be generated by Entity Framework, embedded in a Telerik report definition, produced by a third-party application, or sitting in a deployment pipeline that takes three weeks.

Query Store hints solve exactly this: attach a query hint to a query by its query_id, with no change to the query text and no deployment.

Available in SQL Server 2022 (and 2019 with a recent cumulative update) and in Azure SQL. Requires Query Store to be on.

The workflow

Step 1 — find the query_id. Search Query Store for a distinctive fragment:

SELECT  qsq.query_id,
        qsq.object_id,
        OBJECT_NAME(qsq.object_id) AS containing_object,
        qst.query_sql_text
FROM sys.query_store_query_text AS qst
JOIN sys.query_store_query      AS qsq ON qsq.query_text_id = qst.query_text_id
WHERE qst.query_sql_text LIKE '%FactSalesReport%'   -- placeholder: a table your report hits
ORDER BY qsq.query_id;

Step 2 — apply the hint.

EXEC sys.sp_query_store_set_hints
     @query_id     = 1234,                    -- placeholder
     @query_hints  = N'OPTION(RECOMPILE)';

Step 3 — verify it took effect.

SELECT  qh.query_hint_id,
        qh.query_id,
        qh.query_hint_text,
        qh.last_query_hint_failure_reason_desc,
        qh.query_hint_failure_count,
        qh.source_desc,
        qst.query_sql_text
FROM sys.query_store_query_hints AS qh
JOIN sys.query_store_query      AS qsq ON qsq.query_id = qh.query_id
JOIN sys.query_store_query_text AS qst ON qst.query_text_id = qsq.query_text_id;

query_hint_failure_count > 0 with a reason means the hint could not be applied — check the reason rather than assuming it is working.

Step 4 — measure, using the before/after comparison from Module 1 Lesson 5.

Step 5 — remove it when it is no longer needed.

EXEC sys.sp_query_store_clear_hints @query_id = 1234;

What you can apply

Most query-level hints, including the ones that matter for this course:

-- The workhorse for a report with optional filters
EXEC sys.sp_query_store_set_hints @query_id = 1234, @query_hints = N'OPTION(RECOMPILE)';

-- Cap parallelism for a report that is starving the OLTP workload
EXEC sys.sp_query_store_set_hints @query_id = 1234, @query_hints = N'OPTION(MAXDOP 4)';

-- Optimize for a representative value instead of whatever compiled first
EXEC sys.sp_query_store_set_hints @query_id = 1234, @query_hints = N'OPTION(OPTIMIZE FOR UNKNOWN)';

-- Fall back to the legacy cardinality estimator for one query only
EXEC sys.sp_query_store_set_hints @query_id = 1234,
     @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';

-- Combine
EXEC sys.sp_query_store_set_hints @query_id = 1234, @query_hints = N'OPTION(RECOMPILE, MAXDOP 4)';

Note the doubled single quotes inside USE HINT — the hint text is itself a string literal.

That legacy-CE example is worth highlighting: it lets you keep the whole database at compatibility level 160 and its modern features while giving one problematic query the old estimator. Before Query Store hints, that choice was all-or-nothing at the database level.

Why this replaces plan guides

Plan guides did the same job before 2022. They were difficult in practice: the query text had to match byte-for-byte including whitespace, validation was awkward, they were hard to script through a deployment, and there was no clean way to see whether one was actually being used.

Query Store hints key off the query_id rather than exact text, are listed in one readable view, apply and clear in a single statement, and survive restarts. If you inherit a database with plan guides, migrating them to Query Store hints is a worthwhile cleanup.

-- Any plan guides on this database?
SELECT name, is_disabled, scope_type_desc, hints
FROM sys.plan_guides;

Hint vs forced plan — which to use

Force a plan when a specific plan you have seen is known-good and you want exactly it. Immediate, and the bluntest option.
Apply a hint when you want to influence how the optimizer builds the plan, and still let it adapt as data changes. Generally the better long-term choice.

OPTION(RECOMPILE) as a Query Store hint is particularly good for the report case, because it lets the optimizer build a fresh, appropriate plan for each parameter set — you get the Module 4 catch-all fix applied to a query you cannot edit.

Cautions

A hint is a constraint on the optimizer, and it lasts. Document what you applied, to which query, and why. sys.query_store_query_hints shows the what, not the why.
Query_ids are not stable across environments. The id you found in production does not exist in dev. Script the lookup by query text, not the raw id, if you want this reproducible.
A query whose text changes gets a new query_id, and the hint stays attached to the old one. After any deployment touching that report, verify the hint is still attached to the query being executed.
This is a workaround, not a fix. If the real problem is a missing index or a non-SARGable predicate, the hint may hide it well enough that nobody revisits it. Note the underlying cause in the ticket.

In practice

SQL Server 2022 lets you attach a query hint through Query Store by query_id, so you can apply OPTION(RECOMPILE) to a query whose text you do not control — an EF-generated statement or SQL embedded in a report definition. It replaces plan guides, which needed byte-exact text matching and were painful to validate. Treat it as a targeted stabilizer, document why it is there, and re-check it after any deployment, because changing the query text creates a new query_id and orphans the hint.