SQL Server 2019 Adaptive join scarse prestazioni

Question

Ho aggiornato SQL Server dal 2016 al 2019, il piano di query della mia query è cambiato e utilizzava il join adattivo, ma sfortunatamente la durata della query è aumentata a 1 minuto da 1 secondo, ho cambiato l'ordine di join e il problema è stato risolto

Il codice 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

ed è il piano di query: https://www.brentozar.com/pastetheplan/?id=H1cFQxwdP

Dopo la modifica dell'ordine di iscrizione:

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

ed è il piano di query: https://www.brentozar.com/pastetheplan/?id=SJv1GlPdv

Qualcuno ha un'idea in merito

1. Perché il join adattivo ha causato il rallentamento della query?
2. In che modo la modifica dell'ordine di join cambia il piano di esecuzione?

Answer 1

Il piano veloce presenta un obiettivo di fila . Questo finisce per favorire i join di loop nidificati, che consegnano abbastanza rapidamente 100 righe all'operatore Top, soddisfacendo la query.

Il piano lento ha anche un obiettivo di riga, ma in realtà solo sull'operatore di join adattivo. Nel caso in cui l'adaptive join debba essere eseguito come hash join, tutti i risultati dell'input superiore devono essere consumati (il passaggio "build" dell'hash join). Vedi il blog di Forrest McDaniel per una grande visualizzazione di come funziona: The Three Physical Joins, Visualized

Perché il join adattivo ha causato il rallentamento della query?

L'adaptive join fa infatti funzionare come un hash join, dato che supera la soglia di 88 righe (da un bel po '). Ciò porta la query a dover leggere ogni riga da dbo.APP, unendo tutte le corrispondenze da dbo.PRS- circa 30 GB di letture, secondo il piano di esecuzione.

In che modo la modifica dell'ordine di join cambia il piano di esecuzione?

L'ottimizzatore ha la capacità di riordinare i join per filtrare un gruppo di risultati prima e in modo più efficiente, a condizione che la query produca ancora risultati corretti. Ma non lo fa molto di fronte a un mix di join ESTERNO e INTERNO. Vedi questa domanda e risposta per i dettagli in merito: Eliminazione del join interno inibita da un join esterno precedente

Quando si riscrive manualmente l'ordine di join, è consentito un piano in cui il join dbo.EDUBranchviene prima del join dbo.Country, che non dispone di un join adattivo, utilizza l'obiettivo di riga sopra menzionato e risulta molto migliore (come hai notato).