Nesta série de artigos, vamos entender o que são as famosas estatísticas no banco de dados e também, quando, porque e quem as utiliza. Para posteriormente analisarmos a influência que ela exerce no desempenho do SQL Server, avaliando até que ponto uma estatística desatualizada pode degradar a performance da query.

O assunto foi dividido em dois artigos. Neste primeiro abordarei os conceitos principais, focando na estrutura interna e como as estatísticas são criadas e atualizadas. Ficando para o segundo artigo o teste onde analisaremos o impacto das estatísticas desatualizadas na performance do SQL Server.

O que são statistics?

Estatísticas (statistics) são objetos que detêm informações importantes sobre a distribuição dos dados dentro de tabelas e views indexadas. As estatísticas são de extrema importância para o SQL Server, uma vez que o Otimizador de Consulta (Query Optimizer) utiliza as estatísticas para analisar a seletividade e cardinalidade dos dados, a fim de criar um plano de alta qualidade traçando a melhor “rota” para execução da query.

Conceitualmente o Books Online diz o seguinte:

 “Statistics for query optimization are objects that contain statistical information about the distribution of values in one or more columns of a table or indexed view. The query optimizer uses these statistics to estimate the cardinality, or number of rows, in the query result. These cardinality estimates enable the query optimizer to create a high-quality query plan. For example, the query optimizer could use cardinality estimates to choose the index seek operator instead of the more resource-intensive index scan operator, and in doing so improve query performance.”

Estrutura interna das statistics

Internamente as estatísticas são divididas em três partes, assim contemplando o objeto como um todo, onde inclui o cabeçalho que contém os metadados referentes à estatística em questão, um histograma que é a parte mais importante, onde armazena os valores de distribuição referente à primeira coluna da chave da tabela ou view indexada e por último um vetor de densidade utilizado para medir e manipular a correlação entre as colunas.

Toda consulta executada no SQL Server deve gerar um plano de execução, o que nada mais é do que uma sequência de operações para retornar determinado resultado. O plano de execução utiliza as estatísticas como input de dados para analisar informações como seletividade, quantidade de linhas e números de páginas utilizados pela tabela.

Quando uma statistic é criada? 

Por padrão, ao criar um banco de dados, a opção AUTO_CREATE_ STATISTICS vem configurada como ON, garantindo a criação de estatísticas sempre que necessário.

Como assim, o SQL Server cria estatística pra tudo? Por quê? Sim, toda operação que manipula dados está passiva de geração de estatísticas. Toda consulta gera um plano de execução e todo plano de execução baseia-se em estatística para analisar o custo da operação, assim, definindo quais operadores e índices serão utilizados para retornar os dados da forma mais performática possível.

Existem três modos que uma estatística pode ser criada: automática, explicita e implicitamente.

• Estatísticas criadas automaticamente:

Como dito anteriormente, a configuração AUTO_CREATE_ STATISTICS por default é ON. Isso permite que, caso necessário, o SQL Server crie estatísticas em operações de manipulação de dados.

A primeira imagem demonstra que não existe nenhuma estatística para a tabela Endereco:

EXEC sp_helpstats 'dbo.Endereco', 'all'

Ao executar o Select, teoricamente forçará o SQL Server a criar um plano de execução e como não existe nenhuma estatística para a tabela, internamente e automaticamente será criada para o predicado.

SELECT idCidade, Numero, Logradouro, Complemento
FROM dbo.Endereco
WHERE Numero = 1254

E ao executarmos novamente o comando:

EXEC sp_helpstats 'dbo.Endereco', 'all'

A estatística foi criada para a tabela Endereco (Numero).

O Select a seguir retorna informações mais detalhadas:

SELECT * FROM sys.stats
WHERE object_id = OBJECT_ID('dbo.Endereco')

O campo auto_created armazena o valor “1”, confirmando a criação automática.

• Estatísticas criadas implicitamente

É quando criamos um índice – seja ele clustered ou não – e por consequência são criadas estatísticas implicitamente para os campos chaves do índice.

ALTER TABLE dbo.Endereco
ADD CONSTRAINT pkEndereco PRIMARY KEY CLUSTERED (Id)
GO

Adicionando uma PK a tabela Endereco, um índice também foi criado e implicitamente a estatística foi gerada para a chave do índice (Id).

EXEC sp_helpstats 'dbo.Endereco', 'all'

• Estatísticas cridas manualmente

Também podemos criá-las manualmente, utilizando o comando CREATE STATISTICS.

CREATE STATISTICS St_Endereco_Manual
ON dbo.Endereco (Numero, Logradouro);

SELECT * FROM sys.stats
WHERE object_id = OBJECT_ID('dbo.Endereco')

Como e quando a statistics devem ser atualizadas?

A atualização das estatísticas é de suma importância, pois como foi dito anteriormente, o plano de execução utiliza as estatísticas para avaliar o custo da query. Estatísticas desatualizas podem gerar planos ineficientes impactando na performance da query.

Por default, a configuração AUTO_UPDATE_STATISTICS é configurada como ON, garantindo por meio de monitoramento das modificações a atualização periódica das estatísticas. O SQL Server utiliza uma lógica baseando-se na quantidade de alterações efetuadas na tabela. Ou seja, existe uma regra para atualização e quando o limite de alteração é atingido, a estatística é atualizada.

O campo rowmodctr da tabela sysindexes é utilizado internamente para controlar as alterações na tabela. Como assim? Quando digo alterações, não estou falando especificamente de UPDATE e sim qualquer operação que mude o estado da tabela, ou seja, INSERT, UPDATE, DELETE. Com isso, a cada operação que manipula dados, o campo rowmodctr é incrementado, registrando todas as alterações na tabela. Após a atualização da estatística o campo rowmodctr é redefinido como zero.

A lógica de atualização automática das estatísticas utiliza a regra das 500 modificações mais 20%, ou seja, até que a tabela não atinge as 500 modificações nenhuma atualização automática é realizada. Com isso após a tabela alcançar a marca de 500 alterações será necessário mais 20%, necessitando então de mais 100 alterações aproximadamente de acordo com o campo rowmodctr da tabela sysindexes. E assim por diante, utilizando como regra geral, a estatística será atualizada como 20% dos dados sofrerem alteração.

É importante observar que quanto maior a tabela, maior será o intervalo de atualização. Por isso devemos realizar a atualização manualmente, assim garantindo que as estatísticas sempre estarão atualizadas.

A atualização manual pode ser feita utilizando os comandos UPDATE STATISTICS ou SP_UPDATESTATS.

SP_UPDATESTATS atualiza as estatísticas do banco inteiro, enquanto UPDATE STATISTICS pode atualizar por tabela e podendo chegar ao nível de atualizar uma determinada estatística.

UPDATE STATISTICS dbo.Endereco
WITH FULLSCAN, ALL
GO

O comando a seguir exibe varias informações, como ultima data de atualização, dados sobre o histograma e informações referentes ao vetor de densidade.

DBCC SHOW_STATISTICS ('Endereco', pkEndereco);
GO

É importante ressaltar, que a atualização das estatísticas obriga a recompilação das query, o que exige um esforço e carga adicional sobre o banco de dados.

Até o próximo artigo.

Abraços!