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

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”.

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.