Para um COUNT(DISTINCT)que tem aproximadamente 1 bilhão de valores distintos, estou obtendo um plano de consulta com uma agregação de hash estimada em apenas aproximadamente 3 milhões de linhas.
Por que isso está acontecendo? O SQL Server 2012 produz uma boa estimativa, então isso é um bug no SQL Server 2014 que devo relatar no Connect?
A consulta e estimativa ruim
-- Actual rows: 1,011,719,166
-- SQL 2012 estimated rows: 1,079,130,000 (106% of actual)
-- SQL 2014 estimated rows: 2,980,240 (0.29% of actual)
SELECT COUNT(DISTINCT factCol5)
FROM BigFactTable
OPTION (RECOMPILE, QUERYTRACEON 9481) -- Include this line to use SQL 2012 CE
-- Stats for the factCol5 column show that there are ~1 billion distinct values
-- This is a good estimate, and it appears to be what the SQL 2012 CE uses
DBCC SHOW_STATISTICS (BigFactTable, _WA_Sys_00000005_24927208)
--All density Average Length Columns
--9.266754E-10 8 factCol5
SELECT 1 / 9.266754E-10
-- 1079126520.46229
O plano de consulta
roteiro completo
Aqui está uma reprodução completa da situação usando apenas um banco de dados de estatísticas .
O que eu tentei até agora
Pesquisei nas estatísticas da coluna relevante e descobri que o vetor de densidade mostra aproximadamente 1,1 bilhão de valores distintos. O SQL Server 2012 usa essa estimativa e produz um bom plano. O SQL Server 2014, surpreendentemente, parece ignorar a estimativa muito precisa fornecida pelas estatísticas e, em vez disso, usa uma estimativa muito menor. Isso produz um plano muito mais lento que não reserva memória suficiente e derrama para tempdb.
Eu tentei trace flag 4199, mas isso não corrigiu a situação. Por fim, tentei me aprofundar nas informações do otimizador por meio de uma combinação de sinalizadores de rastreamento (3604, 8606, 8607, 8608, 8612), conforme demonstrado na segunda metade deste artigo . No entanto, não consegui ver nenhuma informação explicando a estimativa incorreta até que ela aparecesse na árvore de saída final.
Problema de conexão
Com base nas respostas a esta pergunta, também registrei isso como um problema no Connect
A maneira como a estimativa de cardinalidade é derivada certamente parece contra-intuitiva para mim. O cálculo de contagem distinto (visível com eventos estendidos ou sinalizadores de rastreamento 2363 e 3604) é:
Observe o boné. A lógica geral disso parece muito razoável (não pode haver valores mais distintos), mas o limite é aplicado a partir de amostras de estatísticas de várias colunas:
Isso mostra 2.980.235 linhas amostradas de 3.439.431.721 com um vetor de densidade no nível Col5 de 3,35544E-07. O recíproco disso dá um número de valores distintos de 2.980.235 arredondados usando matemática real para 2.980.240.
Agora a questão é, dadas as estatísticas amostradas, quais suposições o modelo deve fazer sobre o número de valores distintos. Eu esperaria que extrapolasse, mas isso não é feito, e talvez deliberadamente.
Mais intuitivamente, eu esperaria que, em vez de usar as estatísticas de várias colunas, ele examinasse a densidade em Col5 (mas não):
Aqui a densidade é 9,266754E-10, cujo recíproco é 1.079.126.528 .
Uma solução óbvia nesse meio tempo é atualizar as estatísticas de várias colunas com varredura completa. A outra é usar o estimador de cardinalidade original.
O item Conectar que você abriu, as estatísticas de várias colunas amostradas do SQL 2014 substituem as estatísticas de coluna única mais precisas para colunas não principais , está marcado como Fixo para SQL Server 2017.