Fundamentos sobre Estatísticas
Overview
O SQL Server coleta e armazena dados de estatísticas sobre as colunas utilizadas em predicados de consulta e sobre os índices que são criados para as colunas da tabela. As estatÍsticas são informações utilizadas pelo de otimizador consulta para ajudar a escolher qual algorítmo que será utilizado pelo SQL Server, e como estimar a quantidade de linhas que serão processadas por essas operações; baseado nessa informação o SQL Server pode tomar a melhor decisão para criar um plano de execução.
Conceitos
Quando estamos falando de estatísticas e estimativa de registros dentro do SQL Server, geralmente esbarramos em alguns conceitos que são utilizados com bastante frequência, sendo assim, saber os conceitos o ajudará bastante a absorver o conteúdo a seguir neste artigo. Vamos para alguns conceitos:
Predicado: Uma condição que avalia para "Verdadeiro" e "Falso". Predicados aparecem em cláusulas JOINs e WHERE dentro de uma consulta.
Seletividade: Uma fração de um input de dados que safistaz um predicado específico. Quanto maior a seletividade menor será a quantidade de linhas que serão retornadas em uma consulta. Por exemplo, sabemos que no territorio nacional, temos dois tipos de sexo, sendo eles: masculino e feminino. Sendo assim, utilizar a coluna de sexo para criar um indice não é uma boa escolha, pois, ela NÃO é seletiva. Nesse caso, o valor de CPF é um excelente candidato, por ser bastante seletiva pelo fato de não haver duas pessoas com o mesmo CPF.
Cardinalidade: O número de linhas que atende uma determinada condição ou basicamente podemos dizer que é a porcentagem de linhas que serão estimadas para serem retornadas de uma determinada tabela.
Densidade: Se uma coluna é densa, ela retornará mais linhas para uma determinada consulta, pois, ela retornará mais valores duplicados para uma determinada coluna.Imagine o cenário: Uma tabela de clientes possui as colunas de BairroID e CidadeID (ambas colunas são chaves estrangeiras das tabelas Bairro e Cidade). Existe a possibilidade que a coluna CidadeID possua mais valores duplicados (ou menos valores únicos) do que a coluna BairroID, isso que dizer que, a coluna CidadeID teria uma densidade maior do que a coluna BairroID. Para calcular a densidade de uma coluna no SQL Server, basta apenas utilizar o comando abaixo:
SELECT (1.0 / COUNT(DISTINCT NomeDaColuna)) FROM NomeDaTabela
Obs: Sempre utilize comandos em ambientes de desenvolvimento para questões de análise.
Filtro: Uma condição que é utilizada para avaliar se um registro deve ser parte de um indice filtrado ou estatistica filtrada. Após o SQL Server 2008 é possivel criar índices e estatísticas filtradas utilizando a cláusula WHERE, assim como fazemos hoje nas consultas. Por exemplo, imagine que o sistema XYZ possui uma tabela de Pedidos que possui um alguns milhões de registros. No entanto, a maioria das consultas do sistema fazem referência a data de cadastro do pedido e se os pedidos estão ativos, conforme a seguir:
SELECT * FROM Pedidos WHERE dataPedido = 'XXXXX' and Ativo = 1
Sendo que o filtro citado acima representa apenas 10% do total da minha tabela. Talvez criar um índice ou estatística filtrada ajudaria no que diz respeito a armazenamento, influenciando diretamente em nossas rotinas de manutenção.
Obs: Não se preocupe muito se os conceitos não fazem total sentido nesse momento, mas, quando começarmos a analisar as estatísticas e seus respectivos resultados dentro do SQL Server, as coisas vão se tornar mais claras.
A estatísticas dentro do SQL Server
As estatísticas são estruturas que mantem uma amostragem de dados de uma determinada coluna ou um conjunto de colunas. Essa amostra das estatísticas no SQL Server são criadas e atualizadas automaticamentes dentro da engine do SQL Server se a propriedade Auto Create Statistics e Auto Update Statistics estiver habilitada. Por padrão, essas features já vem habilitadas, até mesmo porque é uma boa prática, fazendo com que o SQL Server faça boas escolhas para criar planos de execução mais eficientes. Como podemos ver através da consulta abaixo na view de sistema sys.databases, conforme a seguir:
Vale lembrar que quando as estatisticas são criada automaticamentes dentro do SQL Server, o SQL Server cria estatísticas apenas para uma coluna e não para um conjunto de colunas se a consulta possuir diversos predicados. Por exemplo: Se a consulta abaixo for realizada e a tabela não possuir estatísticas, o SQL Server criará uma estatística para cada coluna de forma independente, no caso, uma estatística para TransactionDate e outra para Quantity vejamos a seguir:
Execução da Consulta.
SELECT * FROM Production.TransactionHistory WHERE TransactionDate = '20070906' and Quantity > 10
Se executarmos as consultas nas DMVs (sys.stats e sys.stats_columns) para identificar quais estatísticas foram criadas, podemos ver que o SQL Server criou duas novas estatísticas que é identificado através da coluna auto_created, conforme a seguir:
Consulta de metadados das estatísticas:
USE AdventureWorks2014
GO
SELECT OBJECT_NAME(a.object_id) AS Objeto, a.name, a.auto_created
FROM sys.stats a
WHERE OBJECT_NAME(a.object_id) = 'TransactionHistory'
O resultado da consulta anterior retorna as estatísticas que foram criadas automaticamentes e suas respectivas colunas:
Como interpretar e ler o Histograma?
Agora que sabemos o que é são as estatísticas no SQL Server, alguns conceitos básicos e como identifica-lás nas nossas tabelas e colunas, vamos aprender como ler o nosso histograma que é criado para cada estatística.
Histograma é uma amostragem dos dados que são representados em forma de tabela, a quantidade de valores que podem ser representadas nessa determinada tabela podem chegar até 200 passos/amostras. Fazendo uma analogia, imagine que o sistemo XYZ possui uma tabela no SQL Server que possuem alguns milhões de registros, e para uma determinada coluna dessa sua tabela existe uma estatística. A estatística dessa coluna é apenas uma amostragem de como os dados estão distribuidos e como o SQL Server pode consultar essa distribuição para estimar a quantidade de linhas que possui para um determinado valor ou em um intervalo de registros.
Para ver o histograma de uma estatística nós vamos utilizar o comando DBCC SHOW_STATISTICS, conforme comando abaixo:
USE AdventureWorks2014
GO
DBCC SHOW_STATISTICS("Production.TransactionHistory", _WA_Sys_00000005_22401542)
Após o comando ser executado, teremos como retorno o seguinte resultado:
Como pode ser visto, foram retornados três results, sendo eles na seguinte ordem: STAT_HEADER, DENSITY_VECTOR e HISTOGRAM. O maior foco desse artigo é trabalhar em cima do HISTOGRAMA, no entanto, as demais informações serão vistas em um outro momento.
STAT HEADER é o primeiro retorno que possuem algumas informações pertinentes a estatistica em si que veremos em um outro artigo.
DENSITY_VECTOR é o segundo conjunto de informações que temos sobre nossas estatísticas. O Otimizador usa informações do vetor de densidade para melhorar a cardinalidade das consultas que irão rertonar muitos valores. Existe uma densidade para cada coluna (ou conjunto de coluna) da estatística, por exemplo, se nós tivéssemos uma estatística criada manualmente para as colunas: PedidoId e ClienteID. Teríamos uma informação de densidade da coluna PedidoId e uma outra informação de densidade das colunas PedidoId e ClienteId.
HISTOGRAM é Terceira e última informação sobre as nossas estatísticas, esse resultado de fato é a distribuição dos dados em si de uma estatísticas e possuem as seguintes colunas:
RANGE_HI_KEY - Mostra o valor chave da coluna. Por exemplo, se a coluna da estatística é a data do pedido, você verá uma data nessa coluna.
RANGE_ROWS - A quantidade de linhas que está no range de um determinado valor. O Range Rows começa no RANG_HI_KEY + 1 da linha atual até o RANGE_HI_KEY -1 da linha atual. Por exemplo, dado a imagem anterior (colocar o número da figura) se olharmos para a linha 4 temos 466 registros no RANGE_ROWS. Essas 466 linhas existem do valor 2013-08-04 (que é a mesma coisa que o RANGE_HI_KEY + 1 da linha 3) e vai até o valor 2013-08-05(que é a mesma coisa que o RANGE_HI_KEY -1 da linha 4).
EQ_ROWS - A quantidade de valores exatos para os registros que aparecem na coluna RANGE_HI_KEY.
DISTINCT_RANGE_ROWS - A quantidade de valores distintos dentro do RANGE_ROWS. Isso significa que de 466 registros do RANGE_ROWS da linha 4, dois desses valores sao DISTINTOS, provavelmente um valor 2013-08-04 e outro valor 2013-08-05.
AVG_RANGE_ROWS - Esse valor basicamente é o valor do RANGE_ROWS / DISTINCT_RANGE_ROWS.
Obs: Não se preocupe se parece confuso, agora iremos fazer essa leitura na pratica, isso tornará nosso exemplo muito mais simples de entender.
Se executarmos a consulta a seguir, o SQL Server deve estimar um total de 374 linhas, pois, esse e o valor que existe para a coluna EQ_ROWS dentro do HISTOGRAMA para o valor ‘2013-080-03’ na linha três.
USE AdventureWorks2014
GO
SELECT * FROM Production.TransactionHistory
WHERE TransactionDate = '2013-08-03'
Plano de Execução
Como o SQL Server chegou nesse valor?
Agora vamos fazer uma consulta sobre um valor que não está no RANGE_HI_KEY, isso significa que o registro só pode estar entre algum outro intervalo do histograma. Se executarmos a consulta a seguir, o SQL Server deve estimar um total de 233 linhas.
USE AdventureWorks2014
GO
SELECT * FROM Production.TransactionHistory
WHERE TransactionDate = '2013-08-04'
Plano de execução:
Como o valor utilizado como parâmetro, não aparece no RANGE_HI_KEY, o SQL Server utilizou o valor que está de maneira “oculta” na linha de número 4 (olhar a explicação contida sobre RANGE_ROWS discutido anteriormente).
Basicamente o SQL Server pegou o valor da coluna RANGE_ROWS do HISTOGRAMA e dividiu pelo valor DISTINCT_RANGE_ROWS, sendo 466/2. Conforme imagem a seguir:
Conclusão:
Nesse artigo podemos aprender um pouco sobre conceitos sobre estatísticas e de como o SQL Server utiliza as estatísticas para estimar uma quantidade de linhas que serão retornadas por uma operação. No próximo artigo discutiremos mais como as estatísticas funcionam em consultas utilizando valores de um determinado intervalo.
Referências:
https://msdn.microsoft.com/en-us/library/ms174384.aspx
https://technet.microsoft.com/en-us/library/dd535534(v=sql.100).aspx
https://blogs.msdn.com/b/bartd/archive/2011/01/25/query_5f00_tuning_5f00_key_5f00_terms.aspx
Comments
Anonymous
June 29, 2015
No trecho que começa com "Agora vamos fazer uma consulta sobre um valor que não está no RANGE_HI_KEY" acho que faltou a consulta utilizada.Anonymous
July 06, 2015
Vladimir, Obrigado e Corrigido!Anonymous
July 29, 2015
Thiago, bom dia. Cara parabéns, ótimo post, fora de série mesmo. Se me permite, posso utilizar este conteúdo nas minha disciplinas de banco de dados? Até mais.Anonymous
August 05, 2015
Thiagão, Excelente post sobre o assunto. Muito simples de entender e bem completo. Estou aguardando os próximos hein. Grande abraço.Anonymous
August 07, 2015
Boa! Explicou muito bem cada detalhe!Anonymous
August 21, 2015
Júnior Galvão muito obrigado. O intuito dos posts no nosso blog é compartilhar conhecimento e experiência apresentando conteúdo técnico e experiência em campo. Não vejo problema em compartilhar o conhecimento nas suas disciplinas com os seus alunos. Se preferir fique a vontade para compartilhar a fonte da informação para que os alunos tenham mais alternativas de pesquisa. Mais uma vez muito obrigado e abssssssssAnonymous
August 21, 2015
Muito Obrigado Vitor Fava!!!Anonymous
August 21, 2015
Muito Obrigado André César e até a próxima!!!! Abs