patternsqlMinor
SQL server 2019 adaptive join poor performance
Viewed 0 times
adaptivesql2019joinpoorperformanceserver
Problem
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:
and it 's query plan :
https://www.brentozar.com/pastetheplan/?id=H1cFQxwdP
After change join order :
and it 's query plan :
https://www.brentozar.com/pastetheplan/?id=SJv1GlPdv
Does anyone have an idea about
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.EDUBranchIdand 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.CountryIdand it 's query plan :
https://www.brentozar.com/pastetheplan/?id=SJv1GlPdv
Does anyone have an idea about
- Why Adaptive join caused the query to slow down?
- How does changing the join order change the execution plan?
Solution
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
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
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).Context
StackExchange Database Administrators Q#278802, answer score: 8
Revisions (0)
No revisions yet.