A vendor query regresses, but its SQL text is outside your control. Query store hints let you test a targeted plan instruction without editing the application or every session setting.
Find the Exact Query ID
Query Store must be enabled and capture the statement before you can attach a hint. Search query text, then inspect query_id, plans, and runtime history. A query can have several query IDs due to context or text differences, so do not apply a hint to the first similar string you find. I record the vendor release and failing plan as part of the investigation. The statement text can contain literals, so protect it when sharing evidence. With the default AUTO capture mode, a cheap query that ran once is not captured yet. In my test it appeared only after repeated executions.
SELECT TOP (20) q.query_id, q.query_hash, qt.query_sql_text
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
WHERE qt.query_sql_text LIKE N'%OrderSummary%'
ORDER BY q.query_id DESC;Choose One Hypothesis
RECOMPILE can help a parameter-sensitive query but raises compilation cost. MAXDOP can limit parallelism for a query that uses too many workers. A query optimizer compatibility hint can test older optimizer behavior after an upgrade. None is automatically the right choice. I compare the current plan, actual rows, CPU, duration, reads, and parameter mix first. A hint can fix one case and slow another. Keep the test to one query_id and one measured issue.
Try the hint in a restored copy or controlled window. Query Store hints are applied at compile time, so verify a new execution after setting one. Do not infer success from the procedure returning without error.
Apply and Inspect Query Store Hints
sys.sp_query_store_set_hints accepts the query_id and an OPTION clause. The sample uses 39 as a placeholder; replace it with the query_id from your own discovery query. An ID that does not exist in Query Store stops the procedure with error 12402. Query sys.query_store_query_hints to confirm the text and any application failure reason. I check the resulting plan and runtime history as well. A hint recorded in metadata can fail to apply, and the failure columns are there for a reason.
EXEC sys.sp_query_store_set_hints
@query_id = 39,
@query_hints = N'OPTION (RECOMPILE)';
SELECT query_id, query_hint_text,
last_query_hint_failure_reason_desc, query_hint_failure_count
FROM sys.query_store_query_hints
WHERE query_id = 39;Test Other Targeted Options
If RECOMPILE is too costly or the problem is a parallel plan, test MAXDOP with a value approved for the server. For an optimizer regression after compatibility change, a USE HINT for the earlier query optimizer compatibility level can be a narrow experiment. The exact level must be one the engine supports. I do not stack several hints until the query runs faster and then claim to know which one worked. Change one variable and record the plan and metrics.
Ask whether statistics, indexes, or vendor code can provide a durable fix. A hint is useful while that work proceeds. It should not hide a schema problem indefinitely.
Clear the Hint Cleanly
When the vendor ships a fix or a better database change is ready, use sys.sp_query_store_clear_hints for that query_id, again replacing the placeholder 39, then confirm the row disappears from sys.query_store_query_hints and observe the new plan. I schedule removal as part of the real fix deployment; otherwise old hints can keep shaping new vendor SQL in unexpected ways. Keep a rollback procedure ready in case the new plan regresses.
EXEC sys.sp_query_store_clear_hints @query_id = 39;
SELECT query_id, query_hint_text
FROM sys.query_store_query_hints
WHERE query_id = 39;Watch for Query Store Hints That Fail to Apply
A row in the hints catalog view says a hint was configured. It does not guarantee the optimizer used it. Review last_query_hint_failure_reason_desc and query_hint_failure_count after running the query. Inspect the actual plan and runtime result. A hint can be invalid for the statement, conflict with another hint, or stop being useful after a schema or compatibility change. I capture the failure reason before replacing the hint so the investigation has a record of what happened.
Each hint attaches to a query_id, not a broad text pattern. If the vendor changes the statement, a new query_id can appear and the old hint can remain attached to code that no longer runs. Check the vendor upgrade in a test environment and reconcile the hint inventory afterward.
Choose the Hint With a Measured Tradeoff
RECOMPILE can avoid one parameter-sensitive plan being reused for very different values, but it makes each execution pay compilation cost. MAXDOP can reduce worker use on a runaway parallel query while increasing elapsed time for some calls. An older optimizer compatibility hint can isolate a regression, but it can also keep an old plan behavior long after the rest of the database moves forward. I test the actual parameter mix, including rare and common values, before picking one.
What is the exit condition? A vendor fix, an index change, or corrected statistics should trigger a fresh comparison without the hint. I write that condition into the change record. If the query is business critical, keep a rollback command and monitor Query Store after clearing the hint. A targeted hint is useful precisely because it can be applied and removed without editing vendor code; that advantage is lost when nobody remembers it exists.
Keep a simple rollback script in the change record: clear the hint, rerun the query, and compare the plan and user latency. If the hint fixed a severe regression, clearing it without a tested alternative is unsafe. I schedule removal only after the vendor fix or database change passes the same representative parameter tests. The exit plan is as important as the initial hint. An ownerless hint becomes a time capsule nobody requested.
Review Query Store Hints After Changes
Recheck hints after upgrades, compatibility changes, and major data shifts. Query Store retention can clean old queries, and a rewritten vendor statement can receive a new query_id. I maintain a short inventory of active hints with owner, reason, query_id, expected metric, and removal condition. A hint without an owner becomes invisible configuration. The next DBA deserves to know why one query behaves differently.
The measure of success is lower user-facing cost for the query's real parameter mix, with no hidden regression. A recorded hint row is just the starting point of that proof.
Related reading on this blog: Forcing a Plan in Query Store and Checking That It Held and Auditing Query Hints Left in Production Code.
A query hint is not a permanent repair, it is a targeted control with an exit plan.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
