Início / dba / Perguntas / 181543
Accepted
John
Asked: 2017-07-24 02:46:02 +0800 CST

Por que o SQL Server não faz histogramas de estatísticas de colunas compostas?

  • 772

O SQL Server tem uma coisa chamada "estatísticas de várias colunas", mas não é o que se pensa que isso significaria.

Vamos dar uma olhada na seguinte tabela de exemplo:

CREATE TABLE BadStatistics 
(
    IsArchived BIT NOT NULL,
    Id INT NOT NULL IDENTITY PRIMARY KEY,
    Mystery VARCHAR(200) NOT NULL
);

CREATE NONCLUSTERED INDEX BadIndex 
    ON BadStatistics (IsArchived, Mystery);

Com isso, estão sendo criadas duas estatísticas sobre os dois índices que temos:

Estatísticas para BadIndex:

+--------------+----------------+-------------------------+
| All density  | Average Length | Columns                 |
+--------------+----------------+-------------------------+
| 0.5          | 1              | IsArchived              |
+--------------+----------------+-------------------------+
| 4.149378E-06 | 37             | IsArchived, Mystery     |
+--------------+----------------+-------------------------+
| 4.149378E-06 | 41             | IsArchived, Mystery, Id |
+--------------+----------------+-------------------------+

+--------------+------------+---------+---------------------+----------------+
| RANGE_HI_KEY | RANGE_ROWS | EQ_ROWS | DISTINCT_RANGE_ROWS | AVG_RANGE_ROWS |
+--------------+------------+---------+---------------------+----------------+
| 0            | 0          | 24398   | 0                   | 1              |
+--------------+------------+---------+---------------------+----------------+
| 1            | 0          | 216602  | 0                   | 1              |
+--------------+------------+---------+---------------------+----------------+

Estatísticas para índice clusterizado:

+--------------+----------------+---------+
| All density  | Average Length | Columns |
+--------------+----------------+---------+
| 4.149378E-06 | 4              | Id      |
+--------------+----------------+---------+

+--------------+------------+---------+---------------------+----------------+
| RANGE_HI_KEY | RANGE_ROWS | EQ_ROWS | DISTINCT_RANGE_ROWS | AVG_RANGE_ROWS |
+--------------+------------+---------+---------------------+----------------+
| 1            | 0          | 1       | 0                   | 1              |
+--------------+------------+---------+---------------------+----------------+
| 240999       | 240997     | 1       | 240997              | 1              |
+--------------+------------+---------+---------------------+----------------+
| 241000       | 0          | 1       | 0                   | 1              |
+--------------+------------+---------+---------------------+----------------+

(Preenchi a tabela com dados de amostra aleatórios em que cerca de um décimo das linhas não são arquivadas. Executei uma atualização completa das estatísticas de verificação posteriormente.)

Por que o histograma das estatísticas de duas colunas usa apenas uma coluna? Eu sei que muitas pessoas escreveram sobre isso , mas qual é a lógica? Nesse caso, torna todo o histograma muito menos útil, porque a primeira coluna só tem dois valores. Por que as estatísticas seriam arbitrariamente restritas assim?

Observe que esta pergunta não se refere a histogramas multidimensionais, que são uma fera totalmente diferente. Trata-se de histogramas unidimensionais com a única dimensão sendo as tuplas contendo as respectivas colunas múltiplas.

sql-server statistics

1 respostas

  1. Best Answer

    Fundo

    O modelo atual do SQL Server usa apenas histogramas de coluna única e informações de densidade de várias colunas. Histogramas de coluna única são usados ​​para estimar a seletividade para predicados adequados, por exemplo, a = 1ou b > 50. Uma consulta com vários predicados simplesmente combina as seletividades individuais (com suposições) para produzir uma seletividade geral estimada.

    Por exemplo, veja meu artigo Cardinality Estimation: Combining Density Statistics

    A densidade de várias colunas informa ainda mais o modelo, fornecendo informações de correlação fraca para vários predicados de igualdade e cardinalidades de agrupamento para agregações.

    As estatísticas associadas aos índices são um complemento oportunista desse modelo: o mecanismo também pode coletar estatísticas (normalmente varredura completa) enquanto está construindo um índice. O SQL Server constrói automaticamente um histograma de coluna à esquerda e informações de densidade para as outras chaves.

    Histogramas para colunas não iniciais em um índice podem ser construídos sob demanda automaticamente pelo processador de consulta ou com antecedência usando sp_createstatsa @indexonlyopção (entre outras).

    Histogramas de várias colunas

    As suposições feitas ao combinar estatísticas de coluna única (como acima) podem ou não modelar bem a realidade dos dados. Em muitos casos, as opções disponíveis (recuo exponencial, independência, seletividade mínima) produzem uma estimativa “suficientemente boa”.

    Também filtramos estatísticas (e índices) como uma solução natural para índices de coluna à esquerda de baixa cardinalidade, como no exemplo da pergunta. Levar isso ao extremo lógico nos aproxima das estatísticas multidimensionais sobre as quais a questão não é.

    Quando as opções de modelagem disponíveis não podem fornecer uma estimativa adequada, um histograma estatístico de várias colunas pode, de fato, fornecer uma estimativa de seletividade melhor para predicados de índice adequados, em alguns casos. Existem algumas dificuldades em combinar diferentes tipos de dados em diferentes colunas, mas nada insuperável.

    Também precisaríamos de um histograma para cada nível das chaves de índice (para melhores resultados); então, para um índice, (a, b, c)isso significaria histogramas em (a, b)e (a, b, c)além do histograma de coluna única atual (a)sozinho.

    O mecanismo usado para detectar estatísticas desatualizadas também precisaria ser modificado para manter os histogramas de várias colunas afetados. Esses histogramas provavelmente acabariam sendo reconstruídos com mais frequência do que as estatísticas de coluna única, simplesmente porque as modificações em mais colunas os afetam.

    Tudo isso adiciona tamanho, complexidade e sobrecarga de manutenção.

    Estatísticas de várias colunas podem ser simuladas (até certo ponto) usando uma estatística criada em uma coluna computada cuidadosamente construída referenciando várias colunas. A consulta precisaria incluir um predicado na coluna calculada (ou uma correspondência textual exata para a fórmula subjacente) para aproveitar essa estatística. Provavelmente, existem apenas situações muito limitadas em que essa abordagem é prática. No entanto, ele tem alguns dos mesmos problemas de implementação que os histogramas automáticos de várias colunas teriam.

    Em última análise, as únicas pessoas que poderiam dizer com certeza por que o SQL Server não oferece suporte a estatísticas de várias colunas seriam os próprios designers. Se você acha que pode fazer um forte argumento para uma melhoria do produto nessa área com ampla aplicabilidade, você pode sugerir isso no Connect ou por meio do seu canal de suporte normal.

    Nota de rodapé

    Nesse caso, torna todo o histograma muito menos útil, porque a primeira coluna só tem dois valores

    O histograma ainda fornece informações úteis sobre a distribuição de valores na coluna inicial: quando as estatísticas foram criadas, havia 24.398 linhas em que IsArchivedera false e 216.602 linhas em que era true .

    Além disso, o objeto de estatísticas nos diz que existem (1 / 0,5) = 2 valores distintos para IsArchived, (1 / 4,149378E-06) ~= 241000 valores distintos para (IsArchived, Mystery)com um tamanho médio de linha de 37 bytes e a mesma frequência para (IsArchived, Mystery, Id)com 4 bytes extras por linha.

    Essas são todas boas informações de uso geral, que podem ser combinadas com informações estatísticas sobre outras colunas para produzir uma estimativa de seletividade em consultas com vários predicados (como mencionado).

    • 9

relate perguntas