Adaptive Joins and Join Hints

SQL Server 2017
At a recent all-day seminar on query performance tuning I was asked a question that I didn't know the answer to: "How do join hints affect adaptive joins?" I don't know. Let's find out together. Adaptive Joins Here's a query that we can run against AdventureWorks: [crayon-5c6202bc3623c145477516/] Without a columnstore index in SQL Server 2017, the execution plan looks like this: Let's introduce a columnstore index: [crayon-5c6202bc36250433954199/] Now, if we run the same query, the execution plan changes to use an adaptive join like this: You can read more on adaptive joins here if this is new behavior. Now, what if we decide that we just want to see a hash join all the time? Adding a Join Hint I'm not a giant fan of any of the query hints.…
Read More