Joe Obbish walks us through a scenario with adaptive joins in SQL Server 2017:
The estimated costs for the two queries are very close to each other: 74.6842 and 74.6839 optimizer units. However, we saw earlier that the tipping point for an adaptive join on this query can vary between 22680 and 80388.3 rows. This inconsistency means that we can find a query that performs worse with adaptive joins enabled.
Click through to see the queries Joe is using. Based on this, I’d guess that this is probably a knife-edge problem: most of the time, adaptive join processing is better, but if you hit the wrong query, it’s worse.