Solución:
Para que quede claro, el optimizador ya sabe que es una combinación de varios a varios. Si fuerza combinaciones de combinación y observa un plan estimado, puede ver una propiedad para el operador de combinación que le indica si la combinación podría ser de varios a muchos. El problema que debe resolver aquí es aumentar las estimaciones de cardinalidad, presumiblemente para obtener un plan de consulta más eficiente para la parte de la consulta que omitió.
Lo primero que intentaría es poner los resultados de la combinación de Object3 y Object5 en una tabla temporal. Para el plan que publicó, es solo una columna en 51393 filas, por lo que apenas debería ocupar espacio en tempdb. Puede recopilar estadísticas completas en la tabla temporal y eso por sí solo podría ser suficiente para obtener una estimación de cardinalidad final suficientemente precisa. Recopilando estadísticas completas sobre Object1 también puede ayudar. Las estimaciones de cardinalidad a menudo empeoran a medida que avanza desde un plan de derecha a izquierda.
Si eso no funciona, puede probar el ENABLE_QUERY_OPTIMIZER_HOTFIXES sugerencia de consulta si aún no lo tiene habilitado en la base de datos o en el nivel del servidor. Microsoft bloquea las correcciones de rendimiento que afectan al plan para SQL Server 2016 detrás de esa configuración. Algunos de ellos se relacionan con estimaciones de cardinalidad, por lo que quizás tenga suerte y una de las correcciones le ayudará con su consulta. También puede intentar utilizar el estimador de cardinalidad heredado con un FORCE_LEGACY_CARDINALITY_ESTIMATION sugerencia de consulta. Ciertos conjuntos de datos pueden obtener mejores estimaciones con el CE heredado.
Como último recurso, puede aumentar manualmente la estimación de cardinalidad según el factor que desee utilizando el método de Adam Machanic. MANY() función. Hablo de eso en otra respuesta, pero parece que el enlace está muerto. Si está interesado, puedo intentar desenterrar algo.
Las estadísticas de SQL Server solo contienen un histograma para la columna principal del objeto de estadísticas. Por lo tanto, puede crear estadísticas filtradas que proporcionen un histograma de valores para Key2, pero solo entre filas con Key1 = 1. La creación de estas estadísticas filtradas en cada tabla corrige las estimaciones y conduce al comportamiento esperado para la consulta de prueba: cada nueva unión no afecta la estimación de cardinalidad final (confirmada tanto en SQL 2016 SP1 como en SQL 2017).
-- Note: Add "WITH FULLSCAN" to each if you want a perfect 20,000 row estimate
CREATE STATISTICS st_#Table1 ON #Table1 (Key2) WHERE Key1 = 1
CREATE STATISTICS st_#Table2 ON #Table2 (Key2) WHERE Key1 = 1
CREATE STATISTICS st_#Table3 ON #Table3 (Key2) WHERE Key1 = 1
Sin estas estadísticas filtradas, SQL Server adoptará un enfoque más heurístico para estimar la cardinalidad de su combinación. El siguiente documento técnico contiene buenas descripciones de alto nivel de algunas de las heurísticas que usa SQL Server: Optimización de sus planes de consultas con el Estimador de cardinalidad de SQL Server 2014.
Por ejemplo, agregando el USE HINT('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS') sugerencia a su consulta cambiará la heurística de contención de unión para asumir alguna correlación (en lugar de independencia) entre los Key1 predicado y el Key2 unir predicado, que puede ser beneficioso para su consulta. Para la consulta de prueba final, esta sugerencia aumenta la estimación de cardinalidad de 1,175 para 7,551, pero todavía es un poco tímido con respecto a la correcta 20,000 estimación de fila producida con las estadísticas filtradas.
Otro enfoque que hemos usado en situaciones similares es extraer el subconjunto relevante de los datos en tablas #temp. Especialmente ahora que las versiones más nuevas de SQL Server ya no escriben con entusiasmo tablas #temp en el disco, hemos tenido buenos resultados con este enfoque. Su descripción de su combinación de muchos a muchos implica que cada tabla #temp individual en su caso sería relativamente pequeña (o al menos más pequeña que el conjunto de resultados final), por lo que valdría la pena probar este enfoque.
DROP TABLE IF EXISTS #Table1_extract, #Table2_extract, #Table3_extract, #c
-- Extract only the subset of rows that match the filter predicate
-- (Or better yet, extract only the subset of columns you need!)
SELECT * INTO #Table1_extract FROM #Table1 WHERE Key1 = 1
SELECT * INTO #Table2_extract FROM #Table2 WHERE Key1 = 1
SELECT * INTO #Table3_extract FROM #Table3 WHERE Key1 = 1
-- Now perform the join on those extracts, removing the filter predicate
SELECT col = 1
INTO #c
FROM #Table1_extract t1
JOIN #Table2_extract t2
ON t1.Key2 = t2.Key2
JOIN #Table3_extract t3
ON t1.Key2 = t3.Key2