Query Hints and Estimated Plans
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…