Como o SQL Server sabe que os predicados estão correlacionados?

Considere a consulta simples do AdventureWorks e o plano de execução mostrado abaixo. A consulta contém predicados conectados com AND. A estimativa de cardinalidade do otimizador é de 41.211 linhas:

-- Estimate 41,211 rows
SELECT COUNT_BIG(*)
FROM Production.TransactionHistory AS TH
WHERE 
    TH.TransactionID BETWEEN 100000 AND 168336
    AND TH.TransactionDate BETWEEN '2007-09-01' AND '2008-03-13';

Usando estatísticas padrão

Dadas apenas estatísticas de coluna única, o otimizador produz essa estimativa estimando a cardinalidade para cada predicado separadamente e multiplicando as seletividades resultantes juntas. Esta heurística assume que os predicados são completamente independentes.

Dividir a consulta em duas partes facilita a visualização do cálculo:

-- Estimate 68,336.4 rows
SELECT COUNT_BIG(*)
FROM Production.TransactionHistory AS TH
WHERE 
    TH.TransactionID BETWEEN 100000 AND 168336;

A tabela Histórico de transações contém 113.443 linhas no total, portanto, a estimativa de 68.336,4 representa uma seletividade de 68336,4/113443 = 0,60238533 para esse predicado. Essa estimativa é obtida usando as informações do histograma para a TransactionIDcoluna e os valores constantes especificados na consulta.

-- Estimate 68,413 rows
SELECT COUNT_BIG(*)
FROM Production.TransactionHistory AS TH
WHERE 
    TH.TransactionDate BETWEEN '2007-09-01' AND '2008-03-13';

Este predicado tem uma seletividade estimada de 68413,0/113443 = 0,60306056 . Novamente, é calculado a partir dos valores constantes do predicado e do histograma do TransactionDateobjeto de estatísticas.

Assumindo que os predicados são completamente independentes, podemos estimar a seletividade dos dois predicados juntos, multiplicando-os. A estimativa de cardinalidade final é obtida multiplicando a seletividade resultante pelas 113.443 linhas na tabela base:

0,60238533 * 0,60306056 * 113443 = 41210,987

Após o arredondamento, esta é a estimativa de 41.211 vista na consulta original (o otimizador também usa matemática de ponto flutuante internamente).

Não é uma boa estimativa

As colunas TransactionIDe TransactionDatetêm uma correlação próxima no conjunto de dados AdventureWorks (como as chaves e as colunas de data que aumentam monotonicamente costumam fazer). Essa correlação significa que a suposição de independência é violada. Como consequência, o plano de consulta pós-execução mostra 68.095 linhas em vez das 41.211 estimadas:

Sinalizador de rastreamento 4137

A ativação desse sinalizador de rastreamento altera a heurística usada para combinar predicados. Em vez de assumir total independência, o otimizador considera que as seletividades dos dois predicados são próximas o suficiente para que possam ser correlacionadas:

-- Estimate 68,336.4
SELECT COUNT_BIG(*)
FROM Production.TransactionHistory AS TH
WHERE 
    TH.TransactionID BETWEEN 100000 AND 168336
    AND TH.TransactionDate BETWEEN '2007-09-01' AND '2008-03-13'
OPTION (QUERYTRACEON 4137);

Lembre-se de que o TransactionIDpredicado sozinho estimou 68.336,4 linhas e o TransactionDatepredicado sozinho estimou 68.413 linhas. O otimizador escolheu a menor dessas duas estimativas em vez de multiplicar as seletividades.

Esta é apenas uma heurística diferente, é claro, mas que pode ajudar a melhorar as estimativas para consultas com ANDpredicados correlacionados. Cada predicado é considerado para uma possível correlação, e há outros ajustes feitos quando muitas ANDcláusulas estão envolvidas, mas esse exemplo serve para mostrar o básico disso.

O equivalente ao sinalizador de rastreamento 4137 no estimador de cardinalidade atual é a ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES dica de consulta .

Faz com que o SQL Server gere um plano usando seletividade mínima ao estimar os predicados AND para os filtros para contabilizar a correlação total. Esse nome de dica é equivalente ao sinalizador de rastreamento 4137 quando usado com o modelo de estimativa de cardinalidade do SQL Server 2012 (11.x) e versões anteriores e tem efeito semelhante quando o sinalizador de rastreamento 9471 é usado com o modelo de estimativa de cardinalidade do SQL Server 2014 (12.x ) ou mais alto.

Estatísticas de várias colunas

Isso pode ajudar em consultas com correlações, mas as informações do histograma ainda são baseadas apenas na coluna inicial das estatísticas. As seguintes estatísticas candidatas de várias colunas, portanto, diferem de maneira importante:

CREATE STATISTICS
    [stats Production.TransactionHistory TransactionID TransactionDate]
ON Production.TransactionHistory
    (TransactionID, TransactionDate);

CREATE STATISTICS
    [stats Production.TransactionHistory TransactionDate TransactionID]
ON Production.TransactionHistory
    (TransactionDate, TransactionID);

Tomando apenas um deles, podemos ver que a única informação extra são os níveis extras da densidade 'todos'. O histograma ainda contém apenas informações detalhadas sobre a TransactionDatecoluna.

DBCC SHOW_STATISTICS
    (
        'Production.TransactionHistory', 
        'stats Production.TransactionHistory TransactionDate TransactionID'
    );

Com essas estatísticas de várias colunas no lugar...

SELECT COUNT_BIG(*)
FROM Production.TransactionHistory AS TH
WHERE 
    TH.TransactionID BETWEEN 100000 AND 168336
    AND TH.TransactionDate BETWEEN '2007-09-01' AND '2008-03-13';

...o plano de execução mostra uma estimativa que é exatamente a mesma de quando apenas as estatísticas de coluna única estavam disponíveis: