Por que o operador de concatenação estima menos linhas do que suas entradas?

Para citar Campbell Fraser neste item do Connect :

Essas "inconsistências de cardinalidade" podem surgir em várias situações, inclusive quando concat é usado. Eles podem surgir porque a estimativa de uma subárvore específica no plano final pode ter sido realizada em uma subárvore estruturada de forma diferente, mas logicamente equivalente. Devido à natureza estatística da estimativa de cardinalidade, estimar em árvores diferentes, mas logicamente equivalentes, não garante obter a mesma estimativa. Portanto, no geral, nenhuma garantia de consistência esperada é fornecida.

Para expandir um pouco: A maneira que gosto de explicar é dizer que a estimativa de cardinalidade inicial (realizada antes do início da otimização baseada em custo) produz estimativas de cardinalidade mais "consistentes", pois toda a árvore inicial é processada, com cada subseqüente estimativa dependente diretamente da anterior.

Durante a otimização baseada em custo, partes da árvore do plano (um ou mais operadores) podem ser exploradas e substituídas por alternativas, cada uma das quais pode exigir uma nova estimativa de cardinalidade. Não há uma maneira geral de dizer qual estimativa será geralmente melhor do que outra, então é bem possível acabar com um plano final que parece "inconsistente". Isso é simplesmente o resultado de costurar "pedaços de planos" para formar o arranjo final.

Dito isso, houve algumas alterações detalhadas no novo estimador de cardinalidade (CE) introduzido no SQL Server 2014 que torna isso um pouco menos comum do que no caso do CE original.

Além de atualizar para a atualização cumulativa mais recente e verificar se as correções do otimizador com 4199 estão ativadas, suas principais opções são tentar alterações de estatísticas/índice (observando os avisos de índices ausentes) e atualizações ou expressar a consulta de maneira diferente. O objetivo é adquirir um plano que exiba o comportamento que você precisa. Isso pode ser congelado com um guia de plano, por exemplo.

O plano anônimo dificulta a avaliação dos detalhes, mas também examinaria cuidadosamente os bitmaps para ver se eles são do tipo 'otimizado' (Opt_Bitmap) ou pós-otimização (Bitmap). Também desconfio dos Filtros.

Se as contagens de linhas forem precisas, isso parece uma consulta que pode se beneficiar de columnstore. Além dos benefícios usuais, você pode aproveitar a concessão de memória dinâmica para operadores de modo de lote ( o sinalizador de rastreamento 9389 pode ser necessário).

Construir um banco de teste reconhecidamente bastante simples no SQL Server 2012 (11.0.6020) me permite recriar um plano com duas consultas combinadas de hash sendo concatenadas por meio de um arquivo UNION ALL. Meu test-bed não exibe a estimativa incorreta que você vê. Talvez este seja um problema do SQL Server 2014 CE.

Obtenho uma estimativa de 133.785 linhas para uma consulta que na verdade retorna 280 linhas, no entanto, isso é esperado, como veremos mais adiante:

IF OBJECT_ID('dbo.Union1') IS NOT NULL
DROP TABLE dbo.Union1;
CREATE TABLE dbo.Union1
(
    Union1_ID INT NOT NULL
        CONSTRAINT PK_Union1
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , Union1_Text VARCHAR(255) NOT NULL
    , Union1_ObjectID INT NOT NULL
);

IF OBJECT_ID('dbo.Union2') IS NOT NULL
DROP TABLE dbo.Union2;
CREATE TABLE dbo.Union2
(
    Union2_ID INT NOT NULL
        CONSTRAINT PK_Union2
        PRIMARY KEY CLUSTERED
        IDENTITY(2,2)
    , Union2_Text VARCHAR(255) NOT NULL
    , Union2_ObjectID INT NOT NULL
);

INSERT INTO dbo.Union1 (Union1_Text, Union1_ObjectID)
SELECT o.name, o.object_id
FROM sys.objects o;

INSERT INTO dbo.Union2 (Union2_Text, Union2_ObjectID)
SELECT o.name, o.object_id
FROM sys.objects o;
GO

SELECT *
FROM dbo.Union1 u1
    INNER HASH JOIN sys.objects o ON u1.Union1_ObjectID = o.object_id
UNION ALL
SELECT *
FROM dbo.Union2 u2
    INNER HASH JOIN sys.objects o ON u2.Union2_ObjectID = o.object_id;

Acho que o motivo é a falta de estatísticas para as duas junções resultantes que são UNIONed. O SQL Server precisa fazer suposições fundamentadas na maioria dos casos em torno da seletividade das colunas quando confrontado com a falta de estatísticas.

Joe Sack tem uma leitura interessante sobre isso aqui .

Para um UNION ALL, é seguro dizer que veremos exatamente o número total de linhas retornadas por cada componente da união, no entanto, como o SQL Server está usando estimativas de linha para os dois componentes do UNION ALL, vemos que ele adiciona o total de linhas estimadas de ambos consultas para obter a estimativa para o operador de concatenação.

No meu exemplo acima, o número estimado de linhas para cada parte do UNION ALLé 66,8927, que quando somado é igual a 133,785, que vemos para o número estimado de linhas para o operador de concatenação.

O plano de execução real para a consulta união acima se parece com:

Você pode ver o número "estimado" vs "real" de linhas. No meu caso, adicionar o número "estimado" de linhas retornadas pelos dois operadores de correspondência de hash é exatamente igual ao valor mostrado pelo operador de concatenação.

Eu tentaria obter a saída do rastreamento 2363 etc, conforme recomendado na postagem de Paul White que você mostra em sua pergunta. Como alternativa, você pode tentar usar OPTION (QUERYTRACEON 9481)na consulta para reverter para a versão 70 CE para ver se isso "corrige" o problema.