Conversa de DBA

Como “estatísticas” podem impactar na performance de seu banco de dados

A performance das consultas em uma base de dados SQL Server é fortemente determinada pela qualidade do trabalho realizado pelo Query Optimizer.

Query Optimizer

O query optimizer é uma funcionalidade do SQL Server que analisa um conjunto de possíveis planos de execução para uma determinada consulta, estimando o custo de cada um desses planos e selecionando aquele que for o mais “barato” entre as opções consideradas.

Esta “qualidade” é muito influenciada pela quantidade de informações disponíveis para que o otimizador funcione e destaca um trade-off clássico: deixar o banco ótimo para consultas ou para gravações?

O query optimizer utiliza meta-informações relacionadas com os diversos campos de cada tabela para determinar, com maior assertividade possível, que plano de execução selecionar. Esse conjunto de meta-informações é denominado “estatísticas”.

Estatísticas do SQL Server

As estatísticas de otimização de consulta são BLOBs (objetos binários grandes) que contêm informações estatísticas sobre a distribuição de valores em uma ou mais colunas de uma tabela ou exibição indexada. O query optimizer usa essas estatísticas para estimar a cardinalidade ou o número de linhas no resultado de consulta. Essas estimativas de cardinalidade permitem ao otimizador de consulta criar um plano de consulta de alta qualidade. Por exemplo, dependendo dos predicados, o query optimizer pode usar estimativas de cardinalidade para escolher o operador Index Seek em vez de o operador Index Scan, que utiliza mais recursos, melhorando com isso o desempenho das consultas.

Cada objeto de estatísticas é criado em uma lista de uma ou mais colunas de tabela e inclui um histograma que exibe a distribuição de valores na primeira coluna. Os objetos de estatísticas em várias colunas também armazenam informações estatísticas sobre a correlação de valores entre as colunas. Essas estatísticas de correlação, ou densidades, são derivadas do número de linhas distintas de valores de coluna.

Documentação da Microsoft

Sempre que um índice é criado para algumas colunas, estatísticas também são computadas para elas. Em um ambiente onde a opção “Auto Create Statistics” está habilitada, também são criadas estatísticas para as colunas não-indexadas, mas que aparecem nos predicados de consultas. Para manter essas estatísticas atualizadas, a opção ‘Auto Update Statistics’ deverá estar habilitada. Tanto a criação, quanto a atualização automática, de estatísticas geram “engasgos” eventuais no processamento do banco e, por isso, são operações que devem ser analisadas com cuidado.

O problema das “estatísticas desatualizadas”

Estatísticas desatualizadas podem fazer com que o Query Optimizer faça opções não ótimas. Para exemplificar, considere a seguinte consulta realizada em uma base AdventureWorks2019:

SELECT 
    * 
FROM Sales.SalesOrderDetail 
WHERE ProductID = 732 

Como pode ver, o Query Optimizer estimou que 130 linhas seriam lidas, e realmente retornou essas 130 linhas, resultando em um plano de execução ideal (Index Seek no índice IX_SalesOrderDetail_ProductID). 

Analisando o histograma das estatísticas desse índice, vemos como estão distribuídos os dados dessa tabela, e o motivo dele apurar esses valores:

DBCC SHOW_STATISTICS ('Sales.SalesOrderDetail', IX_SalesOrderDetail_ProductID);  

Alguns pontos a destacar: 

  • Data da última atualização da estatística: 27/10/2017 (data devido ao restore do backup
  • Número de registros utilizados para a amostra: 121.317 
  • Quantidade de passos: 200 
  • Densidade da amostra: 0,003759399 
  • Quantidade de linhas para o productID 732: 130 

Porém, executando a mesma consulta para um productID diferente, percebemos um plano de de execução distante do ideal: 

SELECT 
    * 
FROM Sales.SalesOrderDetail 
WHERE ProductID = 736 

Alguns aspectos chamam a atenção:

  • Número de linhas não condiz com o resultado da consulta (99 linhas retornadas), 121.317 linhas estimadas para leitura. 
  • Ignorou o melhor índice (IX_SalesOrderDetail_ProductID), para utilizar a primary key e, devido a isso, efetuou um scan no índice ao invés do seek. 
  • Grande número de leituras lógicas (1248), para retornar poucas linhas. 

O problema é que as estatísticas que foram utilizadas pelo otimizador estão desatualizadas.

Atualizar estatísticas pode ter um elevado custo computacional, por isso, o SQL Server não o faz todo o tempo. Até a versão 2016, eram necessárias 20% + 500 linhas modificadas para disparar o processo. Atualmente, bastam SQRT(1000 * número de linhas) modificações.

Quem não tem o relato de uma “crise” em produção devido a uma atividade administrativa sendo executada em horário ruim?

Quando e como atualizar estatísticas?

A atualização de estatísticas deverá ocorrer em periodicidade que só pode ser apurada com o dimensionamento da taxa de crescimento do banco. Geralmente, atualizações semanais são suficientes. Entretanto, em casos críticos, a frequência precisará ser bem maior.

As estatísticas para as colunas utilizadas em índices são atualizadas automaticamente sempre que ocorrer uma reindexação. Entretanto, colunas não indexadas podem precisar de atualização “manual” executada em operações de manutenção, sempre em momento oportuno.

UPDATE STATISTICS Sales.SalesOrderDetail WITH FULLSCAN 

Sempre que possível, é recomendável usar a opção WITH  FULLSCAN ao atualizar as estatísticas. Apesar de ser mais demorada e utilizar mais recursos, ela garante que toda a tabela seja lida para montar os histogramas das estatísticas, obtendo assim planos de execução mais precisos.

Em Resumo
  • O fato

    A performance do otimizador de consultas do SQL Server é afetada diretamente pela disponibilidade de estatísticas. Estatísticas disponíveis e atualizadas melhoram a performance das consultas mas podem impactar negativamente as operações de gravação.
  • O insight

    É importante desenvolver um plano de manutenção visando manter as estatísticas do banco de dados suficientemente atualizadas para ajudar o SQL Server encontrar os planos de execuções ideais, balanceando performance nas consultas que retornam resultados e nas operações com gravação.

Marcelo Michelon

Especialista na modelagem, manutenção e otimização de bases de dados e na gestão de servidores rodando SQL Server. Mais de 15 anos de experiência operando e potencializando soluções de larga escala.

Talvez você goste também

Carregando posts…

Mais posts da série Conversa de DBA

3 Comentários
  1. Carlos

    Olá, muito bom o artigo e esclarecedor.
    Sobre os NOSQL, eu posso criar um Crud 100% em Mongo? E não usar os SGBD? Qual seria os pontos positivos e neativos?
    Muito obrigado.

  2. Marcelo Michelon

    Olá Carlos,

    Quanto ao NoSQL, apesar de eu não ser especialista nele, é possível criar um CRUD sim.
    A questão na escolha do banco está mais relacionada com que tipo de dado você pretende armazenar no banco, e qual vai ser a utilização dele pelo seu aplicativo.
    Ambos bancos de dados tem seus prós e contras, e cada vez mais tem se optado por soluções híbridas nas aplicações focando no que cada um tem de melhor.
    NoSQL é simples de usar, mais flexível no quesito schema dos dados, fácil de recuperar documentos completos, porém os dados são desnormalizados, não foi feito para ficar sendo feito relacionamentos entre as tabelas nem updates em cascata, como são nos SGBDs. Talvez conhecendo melhor a finalidade do aplicativo, possa ser mais assetivo na resposta

    Grande abraço.

  3. Carlos

    Muito Obrigado, Marcelo.
    Me ajudou a dar uma clareada e entender melhor como unir o melhor de cada um e não apenas tentar resolver tudo com apenas uma solução.

    Um grande abraço.

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *