Mistake: force the join operator that looks fastest today. A physical join operator is the execution algorithm used to implement a logical join, and the optimizer usually chooses among alternatives for different data conditions. Query hints should therefore be a last resort. section 10.5
The 19-second Nested Loops query
A join between Marketing.marketing.Impressions and MarketingArchive.dbo.ImpressionsArchive runs for 19 seconds with Nested Loops. Forcing Hash Join may help this execution, but it hard-codes a choice that can become poor as data, statistics, or workloads change.
Prefer investigation and maintainable fixes before forcing an operator:
- Check whether statistics are current. section 10.5
- Use plan freezing, Query Store hints, USE PLAN, HASH MATCH, or join hints only exceptionally. section 10.5
