SQL Server 2019 Adaptive Join schlechte Leistung

Question

Ich aktualisiere SQL Server von 2016 auf 2019, der Abfrageplan meiner Abfrage wurde geändert und es wurde ein adaptiver Join verwendet. Leider hat sich die Dauer der Abfrage von 1 Sekunde auf 1 Minute erhöht. Ich habe die Join-Reihenfolge geändert und das Problem wurde behoben

Der 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

und es ist Abfrageplan: https://www.brentozar.com/pastetheplan/?id=H1cFQxwdP

Nach Änderung der Beitrittsreihenfolge:

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

und es ist Abfrageplan: https://www.brentozar.com/pastetheplan/?id=SJv1GlPdv

Hat jemand eine Idee über

1. Warum hat Adaptive Join die Abfrage verlangsamt?
2. Wie ändert das Ändern der Verknüpfungsreihenfolge den Ausführungsplan?

Answer 1

Der schnelle Plan enthält ein Reihenziel . Dies führt dazu, dass verschachtelte Schleifenverknüpfungen bevorzugt werden, die dem Top-Operator relativ schnell 100 Zeilen liefern und die Abfrage erfüllen.

Der langsame Plan hat auch ein Zeilenziel, aber eigentlich nur für den adaptiven Join-Operator. Für den Fall, dass der adaptive Join als Hash-Join ausgeführt werden muss, müssen alle Ergebnisse der oberen Eingabe verwendet werden (der "Build" -Schritt des Hash-Joins). In Forrest McDaniels Blog finden Sie eine großartige Visualisierung, wie dies funktioniert: Die drei physischen Verbindungen, visualisiert

Warum hat Adaptive Join die Abfrage verlangsamt?

Der adaptive Join wird tatsächlich als Hash-Join ausgeführt, da er den Schwellenwert von 88 Zeilen (um einiges) überschreitet. Dies führt dazu, dass die Abfrage jede Zeile lesen muss dbo.APPund alle Übereinstimmungen von dbo.PRS- etwa 30 GB Lesevorgänge gemäß dem Ausführungsplan - zusammenfügt.

Wie ändert das Ändern der Verknüpfungsreihenfolge den Ausführungsplan?

Das Optimierungsprogramm kann Verknüpfungen neu anordnen, um eine Ergebnismenge früher und effizienter herauszufiltern, solange die Abfrage weiterhin korrekte Ergebnisse liefert. Aber angesichts einer Mischung aus OUTER- und INNER-Joins macht es nicht viel. Weitere Informationen hierzu finden Sie in diesen Fragen und Antworten: Eliminierung innerer Verknüpfungen durch vorherige äußere Verknüpfung verhindert

Wenn Sie die Verknüpfungsreihenfolge manuell neu geschrieben haben, konnte ein Plan erstellt werden, bei dem die Verknüpfung dbo.EDUBranchvor der Verknüpfung erfolgte dbo.Country- der keine adaptive Verknüpfung hat, das oben erwähnte Zeilenziel verwendet und (wie Sie bemerkt haben) viel besser ausfällt.