SSMS v22 Query Hint Recommendation Tool: The Invasion of the Query Hints
SQL Server Management Studio 22 Preview 3 is out, and it brings with it a new Query Hint Recommendation tool. Start by highlighting the query you want to test, then click Tools, Query Hint Recommendation Tool. It slides out a new pane on the right hand side:
The maximum tuning time defaults to 300 seconds, but I tacked on a couple zeroes because my slow query already took ~20 seconds to run on its own, and I wanted to give the wizard time to wave his little wand around. The tool actually runs your query repeatedly with different hints, so if you have a 5-minute query, you’ll need to give the tool more time.
Click Start, and it begins running your query with different hints. A couple minutes later, I got:
Neato – it skips hints that it doesn’t expect an improvement on. The winner was the below combination of hints for a supposed 78% improvement:
|
1 2 3 |
OPTION (HASH JOIN, HASH GROUP, USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS'), USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES')) |
To integrate them into your code, highlight your query in the SSMS window, then right-click on the relevant hint, and click Append Hint to Query:
And, uh, it adds it after the semicolon:
<sigh> Okay, whatever, it’s a preview, and it’s worth a little pain because the resulting query really is dramatically faster. I ran the un-hinted and hinted versions of the query five times each to let 2025’s adaptive query planning do its thing, and still the hinted query comes out on top. It’s literally on top in the below screenshot:
The hinted query runs in about 9 seconds (down from 25), despite spilling to disk and doing more CPU work (28 seconds as opposed to 25.) That’s fantastic!
Given the choice between these two options:
- People rewriting queries in ways that make the query less intuitive to read, yet possibly more performant, or
- People slapping a few hints at the end of the query
I would much rather take the latter. The latter can be backed out easily, whereas the former cannot. Yes, we’d all prefer that all of the staff attend my index & query tuning fundamentals courses (especially since it’s just $1995 for unlimited seats), because trained people would notice that the hinted version is really going faster because it’s parallel: it’s able to do the 28 seconds of CPU work in just 9 seconds because it went parallel. The unhinted query did not. So if I simply try adding this hint to the original query:
|
1 |
OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE')) |
Then the query runs in just seven seconds – faster – while retaining its intelligent query plan options like adaptive joins. That’s a better answer – but, like I said, doing the better thing takes time and training. The reality is that a lot of companies out there don’t have the time or money to train their staff. They lean on free tools, and here Microsoft is giving us a free tool that can make big real-world improvements. It’s still up to people to use the tool correctly and appropriately.
I actually love this feature. It empowers more people to do more query experiments, more quickly – without really breaking the original query. You can always remove the hints, either manually or with Query Store, so it’s not that brittle or dangerous. There’s a risk to hints, of course: they have dramatically different results on different hardware and versions. Some people are gonna run this tool on non-production, underpowered servers, and they’re gonna be elated with a particular combination of hints that make their pig fly, but the hints will have terrible effects in production. That’s not the fault of the app’s tool – that’s the fault of the tool running the app.
And hey, at least it beats Copilot.
Github Copilot also arrives in SSMS v22 Preview 3. I don’t want you to think I’m anti-AI: I am very, very pro-AI. I use Anthropic Claude, Google Gemini, and OpenAI’s ChatGPT on a daily basis, plus locally hosted models. LLMs are a tool – and a heavily VC-subsidized one at the moment – and the right tools in the right hands can help get better work done more quickly. Sure, like any tool, LLMs have drawbacks, and you have to be careful not to saw your thumb off.
So I asked Github Copilot for help, and its top performance improvement suggestion was to check for indexes.
Indexes that already exist, as illustrated in the query plan in the screenshot, plus in Object Explorer at left:
<sigh> Whatever. It’s in preview, and as long as venture capitalists keep lighting money on fire to train more models, I hope that it becomes smarter and more efficient, because the idea of it is pretty cool.
19 Comments. Leave new
The military wouldn’t put an untrained soldier in the field with a high tech weapon without training but business does it all the time. On the AI front, I prefer Claude and I think Claude Code is the best code generator.
(Note to readers: comments below this comment quickly started to turn political, so I removed ’em. The comments were amusing and no one should be ashamed of ’em – I laughed out loud – but let’s stick a fork in that.)
I love the fact that SSMS now has an additional tool to help create better queries and facilitate testing to optimize performance, eliminating quite a bit of manual wading through the trenches to get some nice results. While this doesn’t complete remove good ol’ Sherlock Holmes detective work it definitely helps . Once this becomes GA I will definitely add it to my toolbox.
Well that’s cool. I recently built something similar using PowerShell/dbatools that runs through hints and query level trace flags, captures the plan, parses the XML and reports things like runtime, logical reads, plan warnings, memory grants and estimated vs actual reads.
Whenever I’m stuck, I build a template with my query, toss it into my script and let it run. Then I come back horever long later and check on the results.
I’ll have to see how this compares to my janky script ? Unfortunately mine isn’t fancy enough to run multiple hints. I’m curious what heuristic they’re using to figure out when to use 4 hints over just 1. Because that would be a pretty fat cartesian product of hints if I tried to do it myself haha.
Documentation of possible query hints and what they do (even if it could need some more information):
https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver17
[…] SSMS v22 Query Hint Recommendation Tool: The Invasion of the Query Hints (Brent Ozar) […]
Not sure I 100% agree with this statement:
> as long as venture capitalists keep lighting money on fire to train more models, I hope that it becomes smarter and more efficient
At this point, AI is consuming enough energy to power entire countries. To what benefit? To make adding query hints a few seconds faster? I’m not a fan.
If SQL Server can identify the most/least effective hints, why doesn’t it just apply them automatically during execution? It’s like saying “tell me to do it this way” or “suggest to me this approach”. If it knows the best way, why not cut out the middle man and just do it?
Because this tool has to run the query repeatedly, once for each set of hints.
The way math works, that means your queries would take longer to run if this was fone automatically for you at runtime.
Couldnt find the option in SSMS 22 Preview?
From chatGPt
How to install / enable it in SSMS 22 Preview
It is an optional component. During SSMS installation (or modification via Visual Studio Installer), under Individual Components ? Code Tools, there is a “Query Hint Recommendation Tool (Preview)” you can check or uncheck.
To uninstall or disable it, you likewise use the Visual Studio Installer and uncheck it in the Code Tools section.
RK – when you solve your own problem by Googling it or asking ChatGPT, you don’t have to share it here. You can just do what it says there. Cheers!
[…] Brent Ozar tries out a new feature of SQL Server Management Studio: […]
Well,
I can see it being useful, in the right hands. Unfortunately, however, I see a future of ‘discussions’ with Dev Teams who have ‘tested’ this against 20 rows, implemented whatever it suggests – which is really not quite what you need for your 400 k rows in live, and; “We Won’t Change It Because Microsoft Recommended It!!!”.
Still, as long as it’s not suggesting N*L*CK on medical systems …
That does look pretty cool (installing SSMS v22 preview 5 at the moment to kick the tyres a bit).
However, I’m still waiting for the day I can point Claude or Copilot (or whichever AI is the flavour of the month at the time) at an entire environment (DBs and app source code), show it a deadlock graph, and ask it to fix the deadlock…or at least give me some more useful advice/info about the situation.
I’ve been in the SQL game for a long time and have read tons of deadlock graphs. I understand the different lock types (and have Paul Randall’s posts & blogs to fall back on when I’m having a seniors moment). But, seriously, figuring out the underlying cause of, and ultimately resolving, deadlocks can be like looking for a needle in a massive warehouse full of haystacks.
Mike – if you’re solving deadlocks, you don’t need to point it at the entire environment. Just hand it the code on both sides of the deadlock, and it’ll work out the rest. It’s been that way for quite a while. For example, here’s it solving a problem from my Mastering Query Tuning class:
https://chatgpt.com/share/690dd74d-bf88-8007-bb27-8d15c4e03dad
You should give it a shot!
Hmm if I run the Query Hint Recommendation Tool multiple times over the same query – I get different recommendations (or in some cases no recommendation is found) on subsequent runs.
I smell a rat Brent…
It’s non-deterministic since the runtime can change per execution, and it seems to be basing decisions on runtimes.
Thanks, yeah that makes sense.
I was sort of expecting it to be based on query cost….