I’m working on an update to my Query Performance Tuning book for SQL Server 2025 and I found myself wondering, will a query hint be immediately apparent in an execution plan without runtime metrics (AKA, Estimated Plan)? My assumption was a resounding yes, but you have to check.
Query Hint in Estimated Plan
I’ve got a simple query that I wanted to test this with:
SELECT p.Name,
p.Class
FROM Production.Product AS p
WHERE p.Color = 'Red'
AND p.DaysToManufacture > 15
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));This one is going to very subtle in it’s behavior. The execution plan, estimated and actual, with or without the query hint, is going to be shaped like this:
The devil as they say, is in the details. Let’s look at the properties of the first operator:
It’s right there at the top. No, you won’t see the query hint. You’ll see the results of the query hint. Look at the value for the CardinalityEstimationModelVersion. It’s 70. That’s the old one.
Conclusion
OK, so I had a doubt. I was momentarily concerned that it wouldn’t work the way expected. Maybe too much time in PostgreSQL. I don’t know, but I decided to validate this behavior and figured I’d share the results in a blog post.
Short conclusion, of course the query hint is going to be exposed somewhere in the execution plan, even a plan without runtime metrics that wasn’t retrieved from cache or the Query Store (meaning, an Estimated Plan). This is because Query Hints are poorly named. They’re not hints. They’re commandments. “Thou shalt use the legacy cardinality estimation engine!” in big fiery letters on a stone tablet. The optimizer must comply, even when just looking at an Estimated Plan.