Rendimiento deficiente de la combinación adaptable de SQL Server 2019

Question

Actualicé SQL Server de 2016 a 2019, el plan de consulta de mi consulta cambió y usó una combinación adaptativa, pero desafortunadamente la duración de la consulta aumentó a 1 minuto de 1 segundo, cambié el orden de combinación y el problema se resolvió

El 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

y su plan de consulta: https://www.brentozar.com/pastetheplan/?id=H1cFQxwdP

Después de cambiar el orden de unión:

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

y su plan de consulta: https://www.brentozar.com/pastetheplan/?id=SJv1GlPdv

¿Alguien tiene una idea sobre

1. ¿Por qué la combinación adaptable hizo que la consulta se ralentizara?
2. ¿Cómo cambia el plan de ejecución al cambiar el orden de unión?

Answer 1

El plan rápido presenta un objetivo de fila . Esto termina favoreciendo las uniones de bucles anidados, que entregan 100 filas al operador Top con bastante rapidez, satisfaciendo la consulta.

El plan lento también tiene un objetivo de fila, pero en realidad solo en el operador de combinación adaptativa. En el caso de que la combinación adaptativa deba ejecutarse como una combinación hash, se deben consumir todos los resultados de la entrada superior (el paso de "construcción" de la combinación hash). Vea el blog de Forrest McDaniel para una gran visualización de cómo funciona esto: Las tres uniones físicas, visualizadas

¿Por qué la combinación adaptable hizo que la consulta se ralentizara?

La adaptación se unen lo hace , de hecho funcionar como una unión de comprobación aleatoria, ya que excede el umbral de 88 filas (por mucho). Esto lleva a que la consulta tenga que leer todas las filas de dbo.APP, uniendo todas las coincidencias dbo.PRS, alrededor de 30 GB de lecturas, según el plan de ejecución.

¿Cómo cambia el plan de ejecución al cambiar el orden de unión?

El optimizador tiene la capacidad de reordenar combinaciones para filtrar un conjunto de resultados antes y de manera más eficiente, siempre que la consulta siga produciendo resultados correctos. Pero no hace mucho esto frente a una mezcla de uniones OUTER e INNER. Consulte estas preguntas y respuestas para obtener detalles sobre eso: Eliminación de combinación interna inhibida por combinación externa anterior

Cuando reescribió manualmente el orden de unión, permitió un plan donde la unión a dbo.EDUBranchvino antes de la unión a dbo.Country, que no tiene una unión adaptativa, utiliza el objetivo de fila mencionado anteriormente y resulta mucho mejor (como notó).