9

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?
0
7

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).

2
  • 1
    Thanks for your answer. For some reason I can not rewrite join order. Is there a way to use sql 2019 at the same time but fix the adaptive join problem? Do you think this is a Microsoft issue?
    – Ferfere
    Oct 31 '20 at 7:28
  • @Ferfere No problem! So are you not allowed to change the query text at all? Some options to try are enable TF 4199 or changing the database compatibility level to 130 (the 2016 compat level), or using the legacy cardinality estimator. Nov 2 '20 at 14:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.