That query is killing your server. You know which one. You found the plan with sp_BlitzCache a couple weeks ago and spit out your coffee. But, it belongs to a vendor application and you cannot touch it.
Your SQL Server doesn't care about any of that.
And, you do have an option. SQL Server 2022 and later, you can 'fix' the query without touching it. No code changes. No procedure rewrites. No begging the business rep to contact the vendor. You just inject a hint using Query Store and the next execution performs much better. That's Query Store hints — and if you're not using them, you've left a very powerful DBA tool just sitting on the shelf.
This is Code-Free Performance Tuning.
Query Store: The Flight Data Recorder
If you read my PSP Optimization post, you already know that Query Store is the foundation under all of SQL Server's modern Intelligent Query Processing features. Think of it like the flight data recorder for your database — it captures query text, execution plans, and runtime statistics across every execution and it survives a restart. Unlike DMVs, which vanish on restart, Query Store remembers. That history is what makes hints possible — and you don't have to setup something new to perform diagnostics before you can use it. How cool is that? 😆
Query Store is on by default in SQL Server 2022. v2019 or earlier, turn it on with this:
ALTER DATABASE YourDatabase
SET QUERY_STORE = ON (
OPERATION_MODE = READ_WRITE,
MAX_STORAGE_SIZE_MB = 1024
);
What a Query Store Hint Actually Does
You pick a hint — MAXDOP, RECOMPILE, a join strategy, etc. You attach it to a specific query_id in Query Store. SQL Server intercepts the query at execution time and injects the hint before the optimizer sees it. The application sends the same T-SQL it always has, but the optimizer now processes it differently.
Step 1: Find the Query ID
Everything starts with the query_id. Query Store already assigned one to every query that's touched your database. Run this to find your worst offenders:
-- Top 20 queries by average logical reads in Query Store
SELECT TOP 20
qsqt.query_sql_text,
qsq.query_id,
qsrs.avg_logical_io_reads,
qsrs.avg_duration / 1000.0 AS avg_duration_ms,
qsrs.count_executions,
qsp.plan_id
FROM sys.query_store_query_text qsqt JOIN sys.query_store_query qsq
ON qsqt.query_text_id = qsq.query_text_id JOIN sys.query_store_plan qsp
ON qsq.query_id = qsp.query_id JOIN sys.query_store_runtime_stats qsrs
ON qsp.plan_id = qsrs.plan_id
ORDER BY qsrs.avg_logical_io_reads DESC;
Write down the query_id. That's your target.
Step 2: Inject the Hint
The system procedure is sys.sp_query_store_set_hints. Pass the query_id and whatever hint you'd normally write in an OPTION clause:
-- Query hammering CPU with bad parallelism? Cap it.
EXEC sys.sp_query_store_set_hints
@query_id = 1234,
@query_hints = N'OPTION (MAXDOP 1)';
-- Bad cached plan? Force a recompile every time. Use carefully.
EXEC sys.sp_query_store_set_hints
@query_id = 1234,
@query_hints = N'OPTION (RECOMPILE)';
-- Optimizer keeps choosing nested loops. Make it stop.
EXEC sys.sp_query_store_set_hints
@query_id = 1234,
@query_hints = N'OPTION (HASH JOIN)';
-- Stack them.
EXEC sys.sp_query_store_set_hints
@query_id = 1234,
@query_hints = N'OPTION (MAXDOP 4, HASH JOIN)';
Done. No code change. No restart. No cache flush. The hint fires on the next call.
Verify It Applied
SELECT
query_hint_id,
query_id,
query_hint_text,
last_query_hint_failure_reason_desc,
is_enabled_by_system
FROM sys.query_store_query_hints
WHERE query_id = 1234;
Check last_query_hint_failure_reason_desc. If it's not NO_FAILURE, the hint didn't apply — and that column tells you exactly why. No guessing.
Remove It When You're Done
The vendor shipped a fix and the underlying problem is gone. Pull the hint:
EXEC sys.sp_query_store_clear_hints
@query_id = 1234;
Why This Buries Plan Guides
Plan Guides have been around since SQL Server 2005 and allow you to optimize queries when you cannot change the code - but the implementation is hellish. A Plan Guide requires an exact text match: whitespace, parameter declarations, the sp_executesql wrapper, all of it. One extra space and the guide silently does nothing. You won't know. It just won't work.
Query Store Hints match on query_id. That's it. No text matching. Failures surface in a DMV instead of disappearing into the void.
| Plan Guides | Query Store Hints | |
|---|---|---|
| Matching method | Exact T-SQL text match | query_id — no text matching |
| Silent failures | Yes — whitespace breaks them | No — failure reason surfaced in DMV |
| SSMS visibility | sys.plan_guides only | Integrated into QS interface |
| Minimum version | SQL Server 2005 | SQL Server 2022 |
The Per-Query Compatibility Level Trick
This one is underused and underappreciated. You can use a Query Store hint to force a specific database compatibility level for a SINGLE query while leaving everything else at the current level. That means you could upgrade a database to a newer compatibility level for the new features and keep one query pinned an older CL until you sort out why it always goes sideways.
-- Force compat level 150 for one query while the DB runs at 160
EXEC sys.sp_query_store_set_hints
@query_id = 1234,
@query_hints = N'OPTION (USE HINT (''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150''))';
If you've ever delayed a compatibility level upgrade because some queries went south with the new CL, this is your exit ramp. Pin the problem query and upgrade the database CL. Fix it later when your schedule allows.
SQL Server 2025: The Nuclear Option
Query Store hints were introduced in SQL Server 2022 but were significantly extended in SQL Server 2025. Using ABORT_QUERY_EXECUTION, the query tries to run. SQL Server kills it. The caller gets an error. That's it.
-- Block a query from executing entirely. SQL Server 2025+.
EXEC sys.sp_query_store_set_hints
@query_id = 1234,
@query_hints = N'OPTION (USE HINT (''ABORT_QUERY_EXECUTION''))';
The caller gets:
Msg 8778, Level 16, State 1 Query execution has been aborted because the ABORT_QUERY_EXECUTION hint was specified.
Think about what that means. A report doing a full scan of a 500GB table every time someone hits a button, or a runaway ETL query that drags your server to its knees every Monday at 9AM. You can block it right now, in 30 seconds, without touching the application ...and now you've got time to have another discusion with whomever owns that code.
To unblock:
EXEC sys.sp_query_store_clear_hints @query_id = 1234;
SQL Server 2025: Hints on Secondary Replicas
Before SQL Server 2025, Query Store hints could only target the primary replicas. In SQL Server 2025 we can now use hints with your readable secondary replicas, completely removed from the primary.
-- Find your replica group ID first
SELECT * FROM sys.query_store_replicas;
-- Apply the hint only on that secondary
EXEC sys.sp_query_store_set_hints
@query_id = 1234,
@query_hints = N'OPTION (MAXDOP 2)',
@replica_group_id = 1; -- from sys.query_store_replicas
Maybe you've got all reporting going to your secondary but it's behaving differently than primary. Different statistics or data distribution? Now you can tune your secondary separately without touching the primary.
The Query Hint Recommendation Tool in SSMS v22
SSMS v22 ships with a Query Hint Recommendation tool that tests different hint combinations against your query and tells you which ones actually help. Brent Ozar put it through the wringer and the results were genuinely useful — but it does run the query multiple times and has a performance overhead of 3-5%. Don't point it at production at the wrong time of day.
One catch: it's not installed by default. Open the Visual Studio Installer, select Modify on your SSMS 22 installation, go to the Code Tools workload, and check 'Query Hint Recommendation Tool'. Once installed, you'll find it under Tools → Query Hint Recommendation Tool in SSMS. Highlight a SELECT query, click Start, and it runs it repeatedly with different hint combinations. Definitely worth installing, but remember, it will be executing your query multiple times. You want to be smart about when you run it.
The Bottom Line
Query Store hints are not a workaround. They're a legit, production-grade mechanism for fixing execution plans on code you don't own and cannot modify. Vendor apps, 3rd party pieces, Reporting tools — Find thequery_id. Apply the hint. All done.
If you think your Query Store hints are being ignored or not working, or you need some help identifying what is killing your server, call me. Let's consider a SQL Server Health Check.
More to Read:
SQL Server 2022's Parameter Sensitive Plan Optimization — sqlfingers.com
Query Store Hints — Microsoft Learn
Query Store Hints Best Practices — Microsoft Learn
SSMS v22 Query Hint Recommendation Tool — Brent Ozar
