Performances médiocres de la jointure adaptative SQL Server 2019

Question

J'ai mis à niveau SQL Server de 2016 à 2019, le plan de requête de ma requête a changé et il a utilisé la jointure adaptative, mais malheureusement la durée de la requête augmente à 1 minute à partir de 1 seconde, j'ai changé l'ordre de jointure et le problème a été résolu

Le code 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

et c'est le plan de requête: https://www.brentozar.com/pastetheplan/?id=H1cFQxwdP

Après la modification de l'ordre de jointure:

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

et c'est le plan de requête: https://www.brentozar.com/pastetheplan/?id=SJv1GlPdv

Quelqu'un a-t-il une idée sur

1. Pourquoi la jointure adaptative a ralenti la requête?
2. Comment la modification de l'ordre de jointure modifie-t-elle le plan d'exécution?

Answer 1

Le plan rapide comporte un objectif de ligne . Cela finit par favoriser les jointures de boucles imbriquées, qui fournissent 100 lignes à l'opérateur Top assez rapidement, satisfaisant ainsi la requête.

Le plan lent a également un objectif de ligne, mais en réalité uniquement sur l'opérateur de jointure adaptative. Dans le cas où la jointure adaptative doit s'exécuter en tant que jointure par hachage, tous les résultats de l'entrée supérieure doivent être consommés (étape de «construction» de la jointure par hachage). Voir le blog de Forrest McDaniel pour une excellente visualisation de son fonctionnement: les trois jointures physiques, visualisées

Pourquoi la jointure adaptative a ralenti la requête?

La jointure adaptative fonctionne en fait comme une jointure de hachage, car elle dépasse le seuil de 88 lignes (de beaucoup). Cela conduit la requête à lire chaque ligne à partir de dbo.APP, en joignant toutes les correspondances à partir de dbo.PRS- environ 30 Go de lectures, selon le plan d'exécution.

Comment la modification de l'ordre de jointure modifie-t-elle le plan d'exécution?

L'optimiseur a la capacité de réorganiser les jointures afin de filtrer un ensemble de résultats plus tôt et plus efficacement, tant que la requête produira toujours des résultats corrects. Mais cela ne fait pas grand-chose face à un mélange de jointures EXTÉRIEURES et INTÉRIEURES. Voir cette Q&A pour plus de détails à ce sujet: Élimination de jointure interne inhibée par une jointure externe précédente

Lorsque vous réécrivez manuellement l'ordre de jointure, cela permettait un plan où la jointure dbo.EDUBranchvenait avant la jointure dbo.Country- qui n'a pas de jointure adaptative, utilise l'objectif de ligne mentionné ci-dessus et s'avère beaucoup mieux (comme vous l'avez remarqué).