SQL server 2019 adaptive join fraco desempenho

Question

Eu atualizo o SQL Server de 2016 para 2019, o plano de consulta da minha consulta mudou e usou junção adaptativa, mas infelizmente a duração da consulta aumentou de 1 segundo para 1 minuto, mudei a ordem de junção e o problema foi resolvido

O código T-SQL:

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

e é o plano de consulta: https://www.brentozar.com/pastetheplan/?id=H1cFQxwdP

Após alterar o pedido de adesão:

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

e é o plano de consulta: https://www.brentozar.com/pastetheplan/?id=SJv1GlPdv

Alguém tem uma ideia sobre

1. Por que a junção adaptável causou lentidão na consulta?
2. Como a alteração da ordem de junção altera o plano de execução?

Answer 1

O plano rápido apresenta uma meta de linha . Isso acaba favorecendo as junções de loops aninhados, que entregam 100 linhas ao operador Top com bastante rapidez, atendendo à consulta.

O plano lento também tem um objetivo de linha, mas apenas no operador de junção adaptável. No caso de a junção adaptativa precisar ser executada como uma junção hash, todos os resultados da entrada superior devem ser consumidos (a etapa de "construção" da junção hash). Veja o blog de Forrest McDaniel para uma ótima visualização de como isso funciona: The Three Physical Joins, Visualized

Por que a junção adaptável causou lentidão na consulta?

O adaptativa juntar-se faz de fato executado como um hash, uma vez que excede o limite de 88 linhas (por bastante). Isso faz com que a consulta tenha que ler todas as linhas de dbo.APP, juntando todas as correspondências de dbo.PRS- cerca de 30 GB de leituras, de acordo com o plano de execução.

Como a alteração da ordem de junção altera o plano de execução?

O otimizador tem a capacidade de reordenar as junções para filtrar um conjunto de resultados mais cedo e com mais eficiência, contanto que a consulta ainda produza resultados corretos. Mas isso não acontece muito em face de uma mistura de junções OUTER e INNER. Veja este Q&A para detalhes sobre: Eliminação de junção interna inibida por junção externa anterior

Quando você reescreveu manualmente a ordem de junção, permitiu um plano em que a junção para dbo.EDUBranchveio antes da junção para dbo.Country- que não tem uma junção adaptativa, utiliza o objetivo de linha mencionado acima e resulta muito melhor (como você notou).