Considere o seguinte plano de consulta no SQL Server 2014:
No plano de consulta, uma junção automática ar.fId = ar.fIdproduz uma estimativa de 1 linha. No entanto, esta é uma estimativa logicamente inconsistente: artem 20,608linhas e apenas um valor distinto de fId(refletido com precisão nas estatísticas). Portanto, essa junção produz o produto cruzado completo de linhas ( ~424MMlinhas), fazendo com que a consulta seja executada por várias horas.
Estou tendo dificuldade em entender por que o SQL Server apresentaria uma estimativa que pode ser facilmente comprovada como inconsistente com as estatísticas. Alguma ideia?
Investigação inicial e detalhes adicionais
Com base na resposta de Paul aqui , parece que as heurísticas SQL 2012 e SQL 2014 para estimar a cardinalidade da junção devem lidar facilmente com uma situação em que dois histogramas idênticos precisam ser comparados.
Comecei com a saída do sinalizador de rastreamento 2363, mas não consegui entender isso facilmente. O trecho a seguir significa que o SQL Server está comparando histogramas para fIde bIdpara estimar a seletividade de uma junção que usa apenas fId? Se assim for, isso obviamente não seria correto. Ou estou interpretando mal a saída do sinalizador de rastreamento?
Plan for computation:
CSelCalcExpressionComparedToExpression( QCOL: [ar].fId x_cmpEq QCOL: [ar].fId )
Loaded histogram for column QCOL: [ar].bId from stats with id 3
Loaded histogram for column QCOL: [ar].fId from stats with id 1
Selectivity: 0
Observe que criei várias soluções alternativas, que são incluídas no script de reprodução completo e reduzem essa consulta para milissegundos. Esta pergunta é focada em entender o comportamento, como evitá-lo em consultas futuras e determinar se é um bug que deve ser registrado na Microsoft.
Aqui está um script de reprodução completo , aqui está a saída completa do sinalizador de rastreamento 2363 e aqui estão as definições de consulta e tabela, caso você queira examiná-las rapidamente sem abrir o script completo:
WITH cte AS (
SELECT ar.fId,
ar.bId,
MIN(CONVERT(INT, ar.isT)) AS isT,
MAX(CONVERT(INT, tcr.isS)) AS isS
FROM #SQL2014MinMaxAggregateCardinalityBug_ar ar
LEFT OUTER JOIN #SQL2014MinMaxAggregateCardinalityBug_tcr tcr
ON tcr.rId = 508
AND tcr.fId = ar.fId
AND tcr.bId = ar.bId
GROUP BY ar.fId, ar.bId
)
SELECT s.fId, s.bId, s.isS, t.isS
FROM cte s
JOIN cte t
ON t.fId = s.fId
AND t.isT = 1
CREATE TABLE #SQL2014MinMaxAggregateCardinalityBug_ar (
fId INT NOT NULL,
bId INT NOT NULL,
isT BIT NOT NULL
PRIMARY KEY (fId, bId)
)
CREATE TABLE #SQL2014MinMaxAggregateCardinalityBug_tcr (
rId INT NOT NULL,
fId INT NOT NULL,
bId INT NOT NULL,
isS BIT NOT NULL
PRIMARY KEY (rId, fId, bId, isS)
)
Consistência
Não há garantia geral de consistência. As estimativas podem ser calculadas em subárvores diferentes (mas logicamente equivalentes) em momentos diferentes, usando métodos estatísticos diferentes.
Não há nada de errado com a lógica que diz que unir essas duas subárvores idênticas deve produzir um produto cruzado, mas também não há nada para dizer que a escolha do raciocínio é mais sólida do que qualquer outra.
Estimativa inicial
No seu caso específico, a estimativa de cardinalidade inicial para a junção não é realizada em duas subárvores idênticas . A forma da árvore naquele momento é:
LogOp_Join LogOp_GbAgg LogOp_LeftOuterJoin LogOp_Get TBL: ar LogOp_Select LogOp_Get TBL: tcr ScaOp_Comp x_cmpEq ScaOp_Identifier [tcr].rId Valor ScaOp_Const=508 ScaOp_Logical x_lopAnd ScaOp_Comp x_cmpEq ScaOp_Identifier [ar].fId ScaOp_Identifier [tcr].fId ScaOp_Comp x_cmpEq ScaOp_Identifier [ar].bId ScaOp_Identifier [tcr].bId AncOp_PrjList AncOp_PrjEl Expr1003 ScaOp_AggFunc stopMax ScaOp_Convert int ScaOp_Identifier [tcr].isS LogOp_Select LogOp_GbAgg LogOp_LeftOuterJoin LogOp_Get TBL: ar LogOp_Select LogOp_Get TBL: tcr ScaOp_Comp x_cmpEq ScaOp_Identifier [tcr].rId Valor ScaOp_Const=508 ScaOp_Logical x_lopAnd ScaOp_Comp x_cmpEq ScaOp_Identifier [ar].fId ScaOp_Identifier [tcr].fId ScaOp_Comp x_cmpEq ScaOp_Identifier [ar].bId ScaOp_Identifier [tcr].bId AncOp_PrjList AncOp_PrjEl Expr1006 ScaOp_AggFunc stopMin ScaOp_Convert int ScaOp_Identifier [ar].isT AncOp_PrjEl Expr1007 ScaOp_AggFunc stopMax ScaOp_Convert int ScaOp_Identifier [tcr].isS ScaOp_Comp x_cmpEq ScaOp_Identifier Expr1006 Valor ScaOp_Const=1 ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [ar].fId ScaOp_Identifier QCOL: [ar].fIdA primeira entrada de junção teve uma agregação não projetada simplificada e a segunda entrada de junção tem o predicado
t.isT = 1colocado abaixo dela, ondet.isTéMIN(CONVERT(INT, ar.isT)). Apesar disso, o cálculo de seletividade para oisTpredicado pode ser usadoCSelCalcColumnInIntervalem um histograma:CSelCalcColumnInInterval Coluna: COL: Expr1006 Histograma carregado para a coluna QCOL: [ar].isT de estatísticas com id 3 Seletividade: 4.85248e-005 Coleta de estatísticas gerada: CStCollFilter(ID=11, CARD=1) CStCollGroupBy(ID=10, CARD=20608) CStCollOuterJoin(ID=9, CARD=20608 x_jtLeftOuter) CStCollBaseTable(ID=3, CARD=20608 TBL: ar) CStCollFilter(ID=8, CARD=1) CStCollBaseTable(ID=4, CARD=28 TBL: tcr)A expectativa (correta) é que 20.608 linhas sejam reduzidas para 1 linha por esse predicado.
estimativa de junção
A questão agora é como as 20.608 linhas da outra entrada de junção corresponderão a esta linha:
LogOp_Join CStCollGroupBy(ID=7, CARD=20608) CStCollOuterJoin(ID=6, CARD=20608 x_jtLeftOuter) ... CStCollFilter(ID=11, CARD=1) CStCollGroupBy(ID=10, CARD=20608) ... ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [ar].fId ScaOp_Identifier QCOL: [ar].fIdExistem várias maneiras diferentes de estimar a junção em geral. Poderíamos, por exemplo:
Dependendo do estimador de cardinalidade em uso e de algumas heurísticas, qualquer um deles (ou uma variação) pode ser usado. Consulte o white paper da Microsoft Otimizando seus planos de consulta com o estimador de cardinalidade do SQL Server 2014 para obter mais informações.
Incomodar?
Agora, conforme observado na pergunta, neste caso, a junção de coluna única 'simples' (on
fId) usa aCSelCalcExpressionComparedToExpressioncalculadora:Este cálculo avalia que unir as 20.608 linhas com a 1 linha filtrada terá uma seletividade zero: nenhuma linha corresponderá (relatada como uma linha nos planos finais). isso é errado? Sim, provavelmente há um bug no novo CE aqui. Pode-se argumentar que 1 linha corresponderá a todas as linhas ou a nenhuma; portanto, o resultado pode ser razoável, mas há motivos para acreditar no contrário.
Os detalhes são bastante complicados, mas a expectativa de que a estimativa seja baseada em
fIdhistogramas não filtrados, modificados pela seletividade do filtro, dando20608 * 20608 * 4.85248e-005 = 20608linhas é bastante razoável.Seguir este cálculo significaria usar a calculadora
CSelCalcSimpleJoinWithDistinctCountsem vez deCSelCalcExpressionComparedToExpression. Não há nenhuma maneira documentada de fazer isso, mas se você estiver curioso, poderá habilitar o sinalizador de rastreamento não documentado 9479:Observe que a junção final produz 20.608 linhas de duas entradas de linha única, mas isso não deve ser uma surpresa. É o mesmo plano produzido pela CE original sob TF 9481.
Mencionei que os detalhes são complicados (e demorados para investigar), mas até onde posso dizer, a causa raiz do problema está relacionada ao predicado
rId = 508, com seletividade zero. Essa estimativa de zero é aumentada para uma linha da maneira normal, o que parece contribuir para a estimativa de seletividade zero na junção em questão quando considera predicados inferiores na árvore de entrada (portanto, estatísticas de carregamento parabId).Permitir que a junção externa mantenha uma estimativa do lado interno de linha zero (em vez de aumentar para uma linha) (para que todas as linhas externas se qualifiquem) fornece uma estimativa de junção 'sem erros' com qualquer uma das calculadoras. Se você estiver interessado em explorar isso, o sinalizador de rastreamento não documentado é 9473 (somente):
O comportamento da estimativa de cardinalidade de junção com
CSelCalcExpressionComparedToExpressiontambém pode ser modificado para não contabilizarbIdcom outro sinalizador de variação não documentado (9494). Menciono tudo isso porque sei que você tem interesse nessas coisas; não porque eles oferecem uma solução. Até que você relate o problema à Microsoft e eles o resolvam (ou não), expressar a consulta de maneira diferente é provavelmente o melhor caminho a seguir. Independentemente de o comportamento ser intencional ou não, eles devem estar interessados em saber sobre a regressão.Por fim, para esclarecer outra coisa mencionada no script de reprodução: a posição final do filtro no plano de perguntas é o resultado de uma exploração baseada em custo
GbAggAfterJoinSelmovendo o agregado e o filtro acima da junção, já que a saída da junção tem uma saída tão pequena numero de linhas. O filtro estava inicialmente abaixo da junção, como você esperava.