Consulta 100x mais lenta no SQL Server 2014, linha de spool de contagem de linhas estima o culpado?

Por que essa consulta precisa de um operador Row Count Spool? ... que otimização específica ele está tentando fornecer?

A cust_nbrcoluna em #existingCustomersé anulável. Se realmente contiver nulos, a resposta correta aqui é retornar zero linhas ( NOT IN (NULL,...) sempre produzirá um conjunto de resultados vazio).

Portanto, a consulta pode ser considerada como

SELECT p.*
FROM   #potentialNewCustomers p
WHERE  NOT EXISTS (SELECT *
                   FROM   #existingCustomers e1
                   WHERE  p.cust_nbr = e1.cust_nbr)
       AND NOT EXISTS (SELECT *
                       FROM   #existingCustomers e2
                       WHERE  e2.cust_nbr IS NULL) 

Com o spool de contagem de linhas lá para evitar ter que avaliar o

EXISTS (SELECT *
        FROM   #existingCustomers e2
        WHERE  e2.cust_nbr IS NULL) 

Mais de uma vez.

Este parece ser apenas um caso em que uma pequena diferença nas suposições pode fazer uma diferença catastrófica no desempenho.

Depois de atualizar uma única linha como abaixo...

UPDATE #existingCustomers
SET    cust_nbr = NULL
WHERE  cust_nbr = 1;

... a consulta foi concluída em menos de um segundo. As contagens de linhas nas versões reais e estimadas do plano agora estão quase corretas.

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

SELECT *
FROM   #potentialNewCustomers
WHERE  cust_nbr NOT IN (SELECT cust_nbr
                        FROM   #existingCustomers 
                       ) 

Linhas zero são geradas conforme descrito acima.

Os histogramas de estatísticas e os limites de atualização automática no SQL Server não são granulares o suficiente para detectar esse tipo de alteração de linha única. Indiscutivelmente, se a coluna for anulável, pode ser razoável trabalhar com base no fato de que contém pelo menos um NULL, mesmo que o histograma de estatísticas não indique atualmente que haja algum.

Por que essa consulta precisa de um operador Row Count Spool? Não acho que seja necessário para correção, então qual otimização específica ele está tentando fornecer?

Veja a resposta completa de Martin para esta pergunta. O ponto-chave é que, se uma única linha dentro de NOT INfor NULL, a lógica booleana funcionará de forma que "a resposta correta seja retornar zero linhas". O Row Count Spooloperador está otimizando essa lógica (necessária).

Por que o SQL Server estima que a associação ao operador Row Count Spool remove todas as linhas?

A Microsoft fornece um excelente white paper sobre o SQL 2014 Cardinality Estimator . Neste documento, encontrei as seguintes informações:

O novo CE assume que os valores consultados existem no conjunto de dados, mesmo que o valor esteja fora do intervalo do histograma. O novo CE neste exemplo usa uma frequência média que é calculada multiplicando a cardinalidade da tabela pela densidade.

Freqüentemente, essa mudança é muito boa; ele alivia muito o problema da chave ascendente e geralmente produz um plano de consulta mais conservador (estimativa de linha mais alta) para valores que estão fora do intervalo com base no histograma de estatísticas.

No entanto, neste caso específico, assumir que um NULLvalor será encontrado leva à suposição de que a junção a Row Count Spoolirá filtrar todas as linhas de #potentialNewCustomers. No caso em que de fato existe uma NULLlinha, essa é uma estimativa correta (conforme visto na resposta de Martin). No entanto, no caso de não haver uma NULLlinha, o efeito pode ser devastador porque o SQL Server produz uma estimativa pós-junção de 1 linha, independentemente de quantas linhas de entrada aparecem. Isso pode levar a opções de junção muito ruins no restante do plano de consulta.

Isso é um bug no SQL 2014? Se sim, vou arquivar no Connect. Mas eu gostaria de uma compreensão mais profunda primeiro.

Acho que está na área cinzenta entre um bug e uma suposição ou limitação de impacto no desempenho do novo Estimador de cardinalidade do SQL Server. No entanto, essa peculiaridade pode causar regressões substanciais no desempenho em relação ao SQL 2012 no caso específico de uma NOT INcláusula anulável que não possui nenhum NULLvalor.

Portanto, registrei um problema de conexão para que a equipe SQL esteja ciente das possíveis implicações dessa alteração no Estimador de cardinalidade.

Atualização: Estamos no CTP3 agora para SQL16, e confirmei que o problema não ocorre lá.

A resposta de Martin Smith e sua auto-resposta abordaram todos os pontos principais corretamente, só quero enfatizar uma área para futuros leitores:

Portanto, esta questão é mais sobre entender essa consulta específica e planejar em profundidade e menos sobre como formular a consulta de maneira diferente.

O objetivo declarado da consulta é:

-- Prune any existing customers from the set of potential new customers

Esse requisito é fácil de expressar em SQL, de várias maneiras. Qual deles é escolhido é tanto uma questão de estilo quanto qualquer outra coisa, mas a especificação da consulta ainda deve ser escrita para retornar resultados corretos em todos os casos. Isso inclui a contabilização de nulos.

Expressando completamente o requisito lógico:

• Retorne clientes em potencial que ainda não são clientes
• Liste cada cliente em potencial no máximo uma vez
• Exclua clientes potenciais e existentes nulos (seja qual for o significado de um cliente nulo)

Podemos então escrever uma consulta que corresponda a esses requisitos usando qualquer sintaxe que preferirmos. Por exemplo:

WITH DistinctPotentialNonNullCustomers AS
(
    SELECT DISTINCT 
        PNC.cust_nbr 
    FROM #potentialNewCustomers AS PNC
    WHERE 
        PNC.cust_nbr IS NOT NULL
)
SELECT
    DPNNC.cust_nbr
FROM DistinctPotentialNonNullCustomers AS DPNNC
WHERE
    DPNNC.cust_nbr NOT IN
    (
        SELECT 
            EC.cust_nbr 
        FROM #existingCustomers AS EC 
        WHERE 
            EC.cust_nbr IS NOT NULL
    );

Isso produz um plano de execução eficiente, que retorna resultados corretos:

Podemos expressar NOT INcomo <> ALLou NOT = ANYsem afetar o plano ou resultados:

WITH DistinctPotentialNonNullCustomers AS
(
    SELECT DISTINCT 
        PNC.cust_nbr 
    FROM #potentialNewCustomers AS PNC
    WHERE 
        PNC.cust_nbr IS NOT NULL
)
SELECT
    DPNNC.cust_nbr
FROM DistinctPotentialNonNullCustomers AS DPNNC
WHERE
    DPNNC.cust_nbr <> ALL
    (
        SELECT 
            EC.cust_nbr 
        FROM #existingCustomers AS EC 
        WHERE 
            EC.cust_nbr IS NOT NULL
    );

WITH DistinctPotentialNonNullCustomers AS
(
    SELECT DISTINCT 
        PNC.cust_nbr 
    FROM #potentialNewCustomers AS PNC
    WHERE 
        PNC.cust_nbr IS NOT NULL
)
SELECT
    DPNNC.cust_nbr
FROM DistinctPotentialNonNullCustomers AS DPNNC
WHERE
    NOT DPNNC.cust_nbr = ANY
    (
        SELECT 
            EC.cust_nbr 
        FROM #existingCustomers AS EC 
        WHERE 
            EC.cust_nbr IS NOT NULL
    );

Ou usando NOT EXISTS:

WITH DistinctPotentialNonNullCustomers AS
(
    SELECT DISTINCT 
        PNC.cust_nbr 
    FROM #potentialNewCustomers AS PNC
    WHERE 
        PNC.cust_nbr IS NOT NULL
)
SELECT
    DPNNC.cust_nbr
FROM DistinctPotentialNonNullCustomers AS DPNNC
WHERE 
    NOT EXISTS
    (
        SELECT * 
        FROM #existingCustomers AS EC
        WHERE
            EC.cust_nbr = DPNNC.cust_nbr
            AND EC.cust_nbr IS NOT NULL
    );

Não há nada de mágico nisso, ou algo particularmente censurável em usar IN, ANY, ou ALL- só precisamos escrever a consulta corretamente, para que ela sempre produza os resultados corretos.

A forma mais compacta usa EXCEPT:

SELECT 
    PNC.cust_nbr 
FROM #potentialNewCustomers AS PNC
WHERE 
    PNC.cust_nbr IS NOT NULL
EXCEPT
SELECT
    EC.cust_nbr 
FROM #existingCustomers AS EC
WHERE 
    EC.cust_nbr IS NOT NULL;

Isso também produz resultados corretos, embora o plano de execução possa ser menos eficiente devido à ausência de filtragem de bitmap:

A pergunta original é interessante porque expõe um problema que afeta o desempenho com a implementação de verificação nula necessária. O objetivo dessa resposta é que escrever a consulta corretamente também evita o problema.