Estatísticas SQL Server
Depois de analisar as principais dificuldades de performance em vários clientes, identificamos alguns pontos que prioritariamente reduzem a performance do ERP Protheus de forma considerável.
Um desses pontos é a estatística do Banco de Dados SQL Server ,que impacta diretamente na performance do ERP. Muitos ainda insistem em deixar desabilitado alguns parâmetros sem nenhum teste que valide essa informação.
Para o leitor que somente quer saber qual é a configuração indicada, essa é a indicação para configuração das estatísticas no SQL Server:
Auto Create Statistics = TRUE
Auto Update Statistics = TRUE
Já para o leitor mais curioso, que deseja entender como e o porquê desse resultado, seguem abaixo os testes realizados.
Esse artigo visa analisar as estatísticas no Banco de Dados SQL Server 2019 e, para isso, utilizamos a ferramenta interna desenvolvida pela Engenharia Protheus, que simula processos do ERP Padrão e realiza operações DML no banco de dados, onde o Protheus utiliza estatística em tempo de execução.
Ou seja o teste é muito mais eficiente quando a estatística está sempre atualizada.
Nessa demonstração, utilizamos o dicionário padrão do Protheus 12.1.27 com dicionário no banco de dados. Utilizamos 4 processos frequentemente usados no Protheus para os testes iniciais, simulando 40 usuários inserindo dados nas principais rotinas desses módulos.:
- Pedidos de Venda
- Documentos de Entrada
- Movimentações Internas
- Produtos
Vamos usar no primeiro exemplo a inserção de dados (1.000.000 milhão de registros) pelas rotinas nas seguintes tabelas:
- SA1 — Clientes
- SA2 — Fornecedores
- SB1 — Produtos
- SF1 — Documentos de Entrada
- SF2 — Documentos de Saída
- SD3 — Movimentos Internos
- SE1 — Títulos a Receber
Mas antes de entender a importância das estatísticas, precisamos entrar de forma macro em alguns conceitos para compreender o funcionamento do SGBD (Sistema de Gerenciamento de Banco de Dados).
Importância das estatísticas
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 e verifica se possui um plano e se o mesmo precisa ser atualizado, senão ele cria 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.
O primeiro fator é a cardinalidade que, em resumo, significa a quantidade de linhas que serão acessadas pela query.
O segundo fator são os operadores usados na query que mudam, e muito, o plano de execução.
Vamos para um exemplo prático, para entender sobre o que estamos falando. A seguinte query foi realizada na tabela SD3990 com 1 milhão de registros:
SELECT SD3.* FROM SD3990 SD3
where SD3.D3_DOC >= ‘000648000’ AND SD3.D3_DOC <= ‘000648050’;
OBS: Lembrando que “select *” NÃO é uma boa prática e foi usado somente para simular de forma mais rápida o exemplo.
Essa query demorou 1489 ms. Se olharmos para o plano de execução, notamos que o SQL Server nos indica a criação de um índice:
Pronto, é só criar o índice e resolvemos! certo?
NÃO!!!
Você pode até criar o índice e, realmente, irá melhorar a performance da query, porém, criar um índice é indicado somente em último caso, se a query for utilizada de forma recorrente e não houver a possibilidade de alterar para usar um índice existente na tabela. Criar muitos índices no banco de dados pode gerar lentidão nas rotinas de Insert, Update e Delete.
Sendo assim, nesse caso não iremos criar o índice!
Em nosso exemplo o próprio SQL Server nos dá um alerta. Se você prestar atenção na figura, temos uma exclamação no Clustered Index Scan da imagem acima:
E o que significa esse “alerta”:
Quando passamos o mouse no Warning que o SQL apresenta, ele nos indica a falta de estatística no campo D3_DOC.
Vamos simplesmente habilitar o parâmetro “Auto Create Statistics” na base de dados analisada. O parâmetro pode ser habilitado nas propriedades do banco de dados na page “Options”, alterando de FALSE para TRUE:
Ou por linha de comando:
USE [master]
go
ALTER DATABASE [statistic_teste3]
SET auto_create_statistics ON
go
OBS: altere o “statistic_teste3” para seu banco de dados.
Parâmetro
Após alterar o parâmetro para ON ou True na parte gráfica:
Rodamos novamente a mesma query e podemos 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:
Quando comparamos o tempo antes de habilitar e depois, verificamos a redução do tempo de 1489 ms para 274 ms:
Com o seguinte DBCC, podemos verificar a criação de uma nova estatística logo após rodarmos a query de exemplo, que foi criada pelo SQL Server automaticamente e demorou alguns milissegundos para ser criada.
DBCC SHOW_STATISTICS ( SD3990, D3_DOC)
Estatística criada:
Limpei o buffer e rodei novamente a query após a estatística ser criada e temos o Trial 3, que indica o novo tempo “Total Execution Time” de 274 ms para 143 ms.
Novamente, retirei o parâmetro do banco de dados que habilita a criação da estatística e apaguei a que estava criada no banco de dados.
Ao rodar novamente a query aumentamos o tempo de 143 ms para 1499ms:
Mas alguém pode dizer “ Ok, tudo isso não me importa, é menos de 1 seg.”
Quando começamos esse artigo, explicamos dois fatores que impactam diretamente na criação do plano de execução, e estes dois fatores são a cardinalidade e os operadores.
Sendo assim, aumentamos cardinalidade, incluindo nessa tabela mais de 20 milhões de registros, e verificamos a performance da query nos mesmos testes já realizados:
Trial 1: Banco de dados com Auto Create Statistics False.
- O Query Optimizer não encontra estatística e indica a criação da estatística e de um índice. Tempo de 48 Seg.
Trial 2: Banco de dados com Auto Create Statistics True.
- O Query Optimizer não encontra estatística e cria uma nova estatística. Tempo de 2,9 Seg.
Trial 3: Banco de dados com Auto Create Statistics True.
- O Query Optimizer agora encontra estatística e utiliza a mesma. Tempo de 1,4 Seg.
Trial 4: Banco de dados com Auto Create Statistics False novamente.
- Mesmo comportamento do Trial 1.Tempo de 48 Seg.
Comprovado!
Comprovamos então que, quanto maior a quantidade de dados, maior será o impacto quando não temos as estatísticas habilitadas.
Com mais de 20 milhões de registros, quando estamos sem estatística, saímos de 1,4 seg para 48 seg.
Realizei os mesmos procedimentos de teste acima, agora iniciando com 25 milhões de linhas e a partir do Trial 8 — Auto Update Statistics, nos testes tivemos os seguintes resultados:
Trial 1: Banco de dados com Auto Create Statistics False.
- O Query Optimizer não encontra estatística e indica a criação da estatística e de um índice. Tempo de 35 Seg.
Trial 2: Banco de dados com Auto Create Statistics True.
- O Query Optimizer não encontra estatística e cria uma nova estatística. Tempo de 1,8 Seg.
Trial 3: Banco de dados com Auto Create Statistics True.
- O Query Optimizer agora encontra estatística e utiliza a mesma. Tempo de 1,3 Seg.
Trial 4: Banco de dados com Auto Create Statistics False novamente.
- Mesmo comportamento Trial 1.Tempo de 35 Seg.
Trial 5: Banco de dados com Auto Create Statistics True.
- Mesmo comportamento Trial 1.Tempo de 1,7 Seg.
Trial 6: Banco de dados com Auto Create Statistics True.
- O Query Optimizer agora encontra estatística e utiliza a mesma. Tempo de 1,3 Seg.
Trial 7: Banco de dados com Auto Create Statistics True. Inclusão de 10 milhões de linhas.
- O Query Optimizer encontra estatística porém está desatualizada. Tempo de 1,9 Seg.
Trial 8: Banco de dados com Auto Create Statistics True e Auto Update Statistics True. Inclusão de 10 linhas.
- O Query Optimizer encontra estatística porém está desatualizada, 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 Seg.
Trial 9: Banco de dados com Auto Create Statistics True e Auto Update Statistics True.
- O SQL Server atualiza a estatística. Tempo de 1,8 Seg.
Nestes últimos testes incluímos o auto update statistics a partir do Trial 8, e na próxima vez que o SQL realizar uma alteração irá verificar que a estatística está desatualizada e atualizará a mesma. Sendo assim, incluímos 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 para que o SQL Server use um limite de atualização de estatísticas dinâmico e decrescente, principalmente em versões anteriores ao que usamos nos testes, MSSQL Server 2019. Mas essa opção deve ser analisada caso a caso com um DBA para verificar qual é a melhor configuração para o ambiente do cliente.
E se você quiser se aprofundar nos conceitos de estatística, pode ler os seguintes artigos:
Um abraço e todos e até a próxima!
