SQL server 2019 adaptive join poor performance

Question

I upgrade SQL Server from 2016 to 2019, the query plan of my query changed and it used adaptive join, but unfortunately the duration of query increase to 1 minute from 1 second, I changed the join order and the problem was solved

The T-SQL code:

SELECT TOP 100 * FROM dbo.APP App
JOIN dbo.PRS p ON App.PartyId=p.PRSId
LEFT JOIN dbo.Country ON p.NationalityId = dbo.Country.CountryId 
LEFT JOIN dbo.EDUBranch b ON app.EducationBranchId=b.EDUBranchId

and it 's query plan : https://www.brentozar.com/pastetheplan/?id=H1cFQxwdP

After change join order :

SELECT TOP 100 * FROM dbo.APP App
LEFT JOIN dbo.EDUBranch b ON app.EducationBranchId=b.EDUBranchId
JOIN dbo.PRS p ON App.PartyId=p.PRSId
LEFT JOIN dbo.Country ON p.NationalityId = dbo.Country.CountryId

and it 's query plan : https://www.brentozar.com/pastetheplan/?id=SJv1GlPdv

Does anyone have an idea about

1. Why Adaptive join caused the query to slow down?
2. How does changing the join order change the execution plan?

Answer 1

The fast plan features a row goal. This ends up favoring nested loops joins, which deliver 100 rows to the Top operator fairly quickly, satisfying the query.

The slow plan also has a row goal, but really only on the adaptive join operator. In the case the adaptive join needs to run as a hash join, all the results from the upper input must be consumed (the "build" step of the hash join). See Forrest McDaniel's blog for a great visualization of how this works: The Three Physical Joins, Visualized

Why Adaptive join caused the query to slow down?

The adaptive join does in fact run as a hash join, since it exceeds the threshold of 88 rows (by quite a lot). This leads to the query having to read every row from dbo.APP, joining all the matches from dbo.PRS - around 30 GB of reads, according to the execution plan.

How does changing the join order change the execution plan?

The optimizer has the ability to reorder joins in order to filter down a resultset earlier and more efficiently, as long as the query will still produce correct results. But it doesn't do this very much in the face of a mix of OUTER and INNER joins. See this Q&A for details on that: Inner join elimination inhibited by prior outer join

When you manually rewrote the join order, it allowed for a plan where the join to dbo.EDUBranch came before the join to dbo.Country - which doesn't have an adaptive join, utilizes the row goal mentioned above, and turns out much better (as you noticed).