Árvore de páginas

Versões comparadas

Chave

  • Esta linha foi adicionada.
  • Esta linha foi removida.
  • A formatação mudou.
Dica
titleDica: Links relacionados

Os links descritos a seguir trazem informações relacionadas ao assunto abordado nesta página.

(Microsoft) DBCC TRACEON - Sinalizadores de rastreamento(Microsoft) Estatísticas • (Microsoft) Estimativa de cardinalidade (em inglês) • 

Informações
titleO que encontrarei nesta página?

Nesta página, você verá o que são as estatísticas de banco de dados, bem como quais os efeitos de não mantê-las atualizadas e como atualizá-las. Caso queira executar os procedimentos em sua base de dados, faça-o com o apoio de um DBA. Reforçamos que as seguintes opções devem estar habilitadas no banco de dados do Protheus: 

  • Auto Create Statistics = TRUE
  • Auto Update Statistics = TRUE

Caso estas opções não estejam habilitadas, será necessário realizar a atualização de estatísticas manualmente. Para isto, utilize o script a seguir com acompanhamento de um DBA:

Bloco de código
languagesql
titleAtualização de estatísticas
USE AdventureWorks2012;   
GO  
-- The following example updates the statistics for all tables in the database.   
EXEC sp_updatestats;

Índice

Estatísticas no SQL Server e o Protheus

O Protheus armazena diversos dados em um banco de dados à medida que é utilizado. Assim como outros fatores, a manutenção do software de banco de dados impacta na performance da aplicação. Se não realizada, ou feita de forma incorreta, a aplicação pode ficar mais lenta. Note que isto não significa que o banco de dados é o único causador de problemas de performance; se estiver passando por lentidão com o sistema, entre em contato com o suporte para análise.

Resumidamente, as estatísticas no banco de dados SQL Server são objetos (BLOB) que têm informações sobre a distribuição de valores em uma ou mais colunas de uma tabela.

Quando realizamos uma query no banco de dados, o SGBD procura trazer os dados da forma mais rápida possível. O SQL Server Query Optmizer realiza uma análise, verifica se existe um plano e se o mesmo precisa ser atualizado. Se não houver, ele criará um novo plano de acordo com a query. Podemos chamar o plano de consulta de caminho mais rápido para chegar à informação desejada, e para isso ele utiliza dois fatores principais: a cardinalidade, que significa, de maneira breve, a quantidade de linhas que serão acessadas pela query; e os operadores usados, que mudam (e muito) o plano de execução.

Para ilustrar isso de outra forma, pense em quando você traça uma rota entre dois pontos em um mapa. Se você utilizar um mapa desatualizado (como um mapa de papel de décadas atrás), há o risco de algumas ruas não existirem mais, ou estar com outro sentido, e consequentemente fazer com que você demore mais para chegar a seu destino; se você utilizar um mapa um pouco mais atual, corre menos riscos de pegar caminhos ruins. Quando você utiliza a versão mais recente possível, que recebe atualizações em tempo real, você pode evitar caminhos com congestionamentos, acidentes, alagamentos, e ter uma estimativa mais precisa do tempo que levará para alcançar seu destino.

A atualização de estatísticas é uma das manutenções que deve ser feita no banco de dados. Para tal, realizamos diversos testes de estresse para identificar pontos que possam ser ofensores na performance do Protheus.

Informações

No banco de dados do Protheus, as seguintes opções devem estar habilitadas: 

  • Auto Create Statistics = TRUE
  • Auto Update Statistics = TRUE

Para os testes, utilizamos o banco de dados SQL Server 2019 com uma ferramenta para testes de estresse desenvolvida pela Engenharia de Dados Protheus. Também utilizamos o dicionário padrão do Protheus 12.1.27, com dicionário no banco de dados. Os quatro processos utilizados no teste foram:

  • Pedido de venda
  • Documentos de entrada;
  • Movimentações internas;
  • Produtos.

As tabelas preenchidas foram: SA1 (Clientes), SA2 (Fornecedores), SB1 (Produtos), SF1 (Documentos de entrada), SF2 (Documentos de saída), SD3 (Movimentos internos) e a SE1 (Títulos a receber).

Informações
titleInformações - Performance da aplicação

A boa manutenção do banco de dados impacta diretamente na performance do produto Protheus, principalmente em cenários com dicionário no banco de dados. O tempo de resposta da aplicação também é relacionado com o banco de dados: se o banco de dados responde mais rápido, a aplicação pode responder mais rápido. Isto não significa que outros fatores não impactem a performance do produto, apenas que a saúde do banco de dados é um dos fatores que têm esse impacto. 

Testes realizados

Realizamos uma consulta de exemplo em uma base de testes, na tabela SD3990, que possuía 1 milhão de registros.

Bloco de código
languagesql
SELECT SD3.* FROM SD3990 SD3 
where SD3.D3_DOC >= '000648000' AND SD3.D3_DOC <= '000648050';
Nota
titleAtenção

Não utilize consultas com * em bases de produção ou em fontes customizados, pois isto impacta a performance da aplicação.

Este é apenas um exemplo executado em uma base de testes. 

Essa query demorou 1489 ms. Analisando o plano de execução, o SQL Server nos indica a criação de um índice:

Porém, a criação do índice não irá resolver a situação. Esta criação até pode melhorar a situação da query naquele momento, mas criar um índice é indicado somente em último caso, se a query for utilizada de forma recorrente e não houver a possibilidade de usar um índice já existente na tabela.

Nota
titleAtenção: Criação de índices

Criar muitos índices no banco de dados pode gerar lentidão nas rotinas de Insert, Update e Delete.

No exemplo acima, o próprio SQL Server dá um alerta no Clustered Index Scan:

Ao analisar o alerta, temos o seguinte:

Neste Warning, o SQL Server indica a falta de estatística no campo D3_DOC

Habilitamos o parâmetro Auto Create Statistics na base de dados analisada. Este parâmetro pode ser habilitado nas propriedades do banco de dados, na aba Options. O alteramos de FALSE para TRUE:

Ou por linha de comando:

Bloco de código
languagesql
USE [master] 
go 

ALTER DATABASE [statistic_teste3] 
SET auto_create_statistics ON 
go
Nota
Altere o “statistic_teste3” para o nome do seu banco de dados.

Após alterar o parâmetro, executamos novamente a query. É possível ver a alteração no plano de execução do SQL Server: agora, ele começa a utilizar índices já existentes no banco de dados, sem solicitar a criação de um novo índice:

Ao comparar o tempo de execução após habilitar este parâmetro, verificamos a redução do tempo de 1489 ms para 274 ms, uma redução de aproximadamente 81,6% no tempo da execução.

Nota
titlePercentual de redução no tempo da consulta

O percentual apresentado neste documento é referente às condições específicas do teste, com fatores que englobam a versão do banco de dados, a quantidade de registros na base de dados, os índices já existentes. Este valor não é fixo, e pode variar de acordo com as configurações de seu ambiente. 

Com o seguinte DBCC, é possível verificar a criação de uma nova estatística logo após executarmos a query de exemplo. Ela foi criada pelo SQL Server automaticamente, e demorou alguns milissegundos para ser criada.

Bloco de código
languagesql
DBCC SHOW_STATISTICS (SD3990, D3_DOC)

Estatística criada:

Após realizar a limpeza do buffer, a query foi executada novamente com a estatística criada. Temos, então, o Trial 3, onde o novo tempo de execução caiu de 274 ms para 143 ms, com uma redução de aproximadamente 52,2% entre estes testes e pouco mais de 90% em relação ao teste inicial. Lembre-se: estes valores são referentes às condições aqui aplicadas, e podem variar de acordo com seu cenário.

Para comprovar que a alteração das estatísticas afeta o tempo da consulta, o parâmetro AUTO CREATE STATISTICS foi retirado novamente, e a estatística que havia sido criada foi apagada.

Ao rodar novamente a query, o tempo teve um aumento de 143 ms para 1499 ms, ou seja, 1.048,25% em relação à consulta com estatísticas: 

 


Comprovação dos testes e conclusão

No início deste documento, foram pontuados dois fatores que impactam diretamente a criação do plano de execução, que são a cardinalidade e os operadores. 

Sendo assim, aumentou-se a cardinalidade, incluindo mais de 20 milhões de registros nesta tabela, e verificou-se a performance da query nos testes já apresentados. Trial 1: Banco de dados com

TesteStatus Auto Create Statistics
False
Resultado
Trial 1False

O Query Optimizer não encontra estatística e indica a criação da estatística e de um índice. Tempo de 48 segundos.

Trial 2
:  Banco de dados com Auto Create Statistics True
True

O Query Optimizer não  encontra estatística e cria uma nova estatística. Tempo de 2,9 segundos.

Trial 3
:  Banco de dados com Auto Create Statistics True
True

O Query Optimizer agora encontra estatística e a utiliza. Tempo de 1,4 segundos.

Trial 4
:  Banco de dados com Auto Create Statistics False novamente. Mesmo comportamento do Trial 1
FalseO Query Optimizer não encontra estatística e indica a criação da estatística e de um índice (idem à trial 1). Tempo de 48 segundos.

Comprovou-se  então que, quanto maior a quantidade de dados, maior será o impacto quando não houver as estatísticas habilitadas.

Com mais de 20 milhões de registros, quando não houver estatística atualizada sai de 1,4 seg para 48 seg.

E quando utilizou-se a estatística, saiu de 0,2 ms para 1,4 segundos que ainda pode ser melhorado com Rebuild de Index, mas esse tema fica para outro estudo.

Realizaram-se os mesmos procedimentos do teste acima, agora iniciando com 25 milhões de linhas e a partir do Trial 8 - Auto Update Statistics, nos testes apareceram os seguintes resultados:Trial 1: Banco de dados com

TesteStatus Auto Create Statistics
False. O
Status Auto Update StatisticsAlteraçõesResultadoTempo (s)
Trial 1FalseFalse-- Query Optimizer não encontra estatística e indica a criação da estatística e de um índice.
Tempo de 35 segundos.
 35 
Trial 2
:  Banco de dados com
TrueFalseAuto Create Statistics
True. 
O
Query Optimizer não  encontra estatística e cria uma nova estatística.
Tempo de
 1,
8 segundos.
Trial 3
:  Banco de dados com Auto Create Statistics TrueO
TrueFalse--Query Optimizer agora encontra estatística e utiliza a mesma.
Tempo de
 1,3
segundos.
Trial 4
:  Banco de dados com
FalseFalseAuto Create Statistics
False novamente. 

Mesmo comportamento do Trial 1.

Tempo de

 

35
segundos.
Trial 5
:  Banco de dados com
TrueFalseAuto Create Statistics
True. 

Mesmo comportamento do Trial 1.

Tempo de

 

1,7
segundos.O
Trial 6
:  Banco de dados com Auto Create Statistics True
TrueFalse

Query Optimizer agora encontra estatística e utiliza a mesma.

Tempo de

1,3
segundos.
Trial 7
:  Banco de dados com Auto Create Statistics True.
TrueFalseInclusão de 10 milhões de linhas
.
O

Query Optimizer encontra estatística porém está desatualizada.

Tempo de

1,9
segundos.
Trial 8
:  Banco de dados com Auto Create Statistics True e
TrueTrueAuto Update Statistics
True.
O

Query Optimizer encontra estatística porém está desatualizada.

Como nenhum dado foi inserido na tabela, o SQL Server não atualizou as estatísticas, mesmo com auto update habilitado

o SQL ainda não atualizou porque não foi inserido nenhum dado ainda na tabela. Tempo de

.

1,9
segundos.
Trial 9
:  Banco de dados com Auto Create Statistics True e Auto Update Statistics True.
TrueTrueInclusão de 10 linhas
.  

O SQL Server atualiza a estatística.

Tempo de

1,8
segundos.


Nestes últimos testes, incluiu-se o Auto update statistics a partir do Trial 8, e na próxima vez que houver uma alteração, o SQL irá verificar que a estatística está desatualizada e atualizar a mesma. Sendo assim, incluiu-se apenas 10 registros e a estatística foi atualizada automaticamente conforme esperado. 

O SQL Server tem um cálculo específico sobre a quantidade de registros para realizar a atualização da estatística.

É possível habilitar o sinalizador de rastreamento 2371, que altera o limite fixo de estatísticas de atualização para o limite de estatísticas de atualização linear, ou seja, para que o SQL Server utilize um limite de atualização de estatísticas dinâmico e decrescente, principalmente em versões anteriores ao que foi utilizado no teste. Esta opção deve ser analisada com acompanhamento de um DBA para verificar a melhor configuração para seu cenário.

Informações
titleArtigos sugeridos

Caso você queira se aprofundar nos conceitos de estatística, leia os seguintes artigos:

Microsoft: Estatísticas

Microsoft: Cardinality Estimation

SQL Performance: UPDATEs to Statistics