Nesta série de artigos, vamos entender o que são as famosas estatísticas no banco de dados e também, quando, por que e quem as utiliza, para posteriormente analisarmos a influência que elas exercem 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 – no primeiro artigo, foram abordados os principais conceitos, passando por detalhes da estrutura interna e como e quando as estatísticas são criadas e atualizadas. Para esta segunda e última parte da série, vamos analisar o impacto que as estatísticas desatualizadas podem exercer negativamente na performance das querys e também em diferentes estratégias de atualização, provendo o menor impacto possível.
Ambiente para teste
Na tentativa de simular situações mais próximas da realidade, vamos criar o banco de dados DB_Statistics com algumas tabelas. Mas a tabela que realmente importa pra nós é a Teste_Estatisticas que será populada com 3 milhões de registros.
USE MASTER GO CREATE DATABASE DB_Statistics GO -- O Padrão já em ON... somente para confirmar e demonstrar o comando ALTER DATABASE DB_Statistics SET AUTO_CREATE_STATISTICS ON GO ALTER DATABASE DB_Statistics SET AUTO_UPDATE_STATISTICS ON GO
Criando a tabela Teste_Estatisticas e iniciando o processo para inserir os 3 milhões de registros.
USE DB_Statistics GO CREATE TABLE dbo.Carga_Teste1 ( First_Name VARCHAR(20) NULL, Last_Name VARCHAR(20) NULL, First_Name_Mother VARCHAR(20) NULL, Last_Name_Mother VARCHAR(20) NULL ) GO INSERT INTO dbo.Carga_Teste1 VALUES ('John','Anderson','Mary','Jones'); INSERT INTO dbo.Carga_Teste1 VALUES ('Kate','Walker','Julia','Taylor'); INSERT INTO dbo.Carga_Teste1 VALUES ('Ray','Kennedy','Juliette','Rock'); INSERT INTO dbo.Carga_Teste1 VALUES ('Tom','Jackson','Mary','Johnson'); INSERT INTO dbo.Carga_Teste1 VALUES ('Homer','Simpson','Martha','Smith'); INSERT INTO dbo.Carga_Teste1 VALUES ('John','White','Olivia','Miller'); INSERT INTO dbo.Carga_Teste1 VALUES ('Bill','Elliott','Emma','Moore'); INSERT INTO dbo.Carga_Teste1 VALUES ('James','Morgan','Emily','Davis'); INSERT INTO dbo.Carga_Teste1 VALUES ('Robert','Garcia','Mary','Robinson'); INSERT INTO dbo.Carga_Teste1 VALUES ('Kevin','Bauer','Rachel','Green'); INSERT INTO dbo.Carga_Teste1 VALUES ('Matt','Lewis','Kate','Brown'); INSERT INTO dbo.Carga_Teste1 VALUES ('Bobby','Ewans','Samantha','Ewing'); GO SELECT TOP 10 * INTO dbo.Carga_Teste2 FROM dbo.Carga_Teste1 ORDER BY 1 GO SELECT BS(CHECKSUM(NEWID()))%2 AS RandNum1, CONVERT(INTEGER, ABS(CHECKSUM(NEWID()))/100.0) AS RandNum2, Carga_Teste1.First_Name, Carga_Teste1.Last_Name, Carga_Teste2.First_Name_Mother, Carga_Teste2.Last_Name_Mother INTO dbo.Teste_Estatisticas FROM dbo.Carga_Teste1 CROSS JOIN dbo.Carga_Teste2 GO CREATE CLUSTERED INDEX idx_Teste_Estatisticas ON dbo.Teste_Estatisticas (RandNum2) GO INSERT INTO dbo.Teste_Estatisticas SELECT ABS(CHECKSUM(NEWID()))%2 as RandNum1, CONVERT(INTEGER,ABS(CHECKSUM(NEWID()))/100.0) as RandNum2, Carga_Teste2.First_Name, Carga_Teste2.Last_Name, Carga_Teste1.First_Name_Mother, Carga_Teste1.Last_Name_Mother FROM dbo.Carga_Teste1 CROSS JOIN dbo.Carga_Teste2 GO INSERT INTO dbo.Teste_Estatisticas SELECT ABS(CHECKSUM(NEWID()))%2, CONVERT(INTEGER,ABS(CHECKSUM(NEWID()))/100.0), TE1.[First_Name], TE2.[Last_Name], TE1.[First_Name_Mother], TE2.[Last_Name_Mother] FROM dbo.Teste_Estatisticas TE1 CROSS JOIN dbo.Teste_Estatisticas TE2 GO INSERT INTO dbo.Teste_Estatisticas SELECT TOP 2942160 ABS(CHECKSUM(NEWID()))%2,CONVERT(INTEGER,ABS(CHECKSUM(NEWID()))/100.0), TE2.First_Name, TE1.Last_Name, TE2.First_Name_Mother, TE1.Last_Name_Mother FROM dbo.Teste_Estatisticas TE1 CROSS JOIN dbo.Teste_Estatisticas TE2 GO
Simplificando, o processo para popular a tabela com 3 milhões de registros utiliza duas tabelas auxiliares, Carga_Teste1 e Carga_Teste2. Com isso, populamos com algumas linhas iniciais e posteriormente utilizando o CROSS JOIN entre elas e inserindo na Teste_Estatisticas – isso mesmo CROSS JOIN. Assim, forçamos o produto cartesiano e assim alcançando as 3 milhões de linhas. Nesse caso, os valores internos não têm nenhuma relevância pra nós, o que realmente importa é quantidade de registros.
Prova real
Vamos alinhar os pensamentos. O intuito principal deste artigo é avaliar se realmente as estatísticas desatualizadas influenciam de forma negativa na performance do SQL Server, certo? Sim, isso mesmo, por isso criamos uma tabela com 3 milhões de registros. E como vamos testar isso? Bom a ideia é simples: simular o que ocorre no dia a dia em uma tabela, gerando grandes alterações na tabela, assim as estatísticas ficaram desatualizadas.
O primeiro passo é alterar o banco para NÃO atualizar mais as estatísticas automaticamente. Executando esse comando, o SQL Server passa para nós a responsabilidade de quando atualizar as estatísticas:
ALTER DATABASE DB_Statistics SET AUTO_CREATE_STATISTICS OFF GO ALTER DATABASE DB_Statistics SET AUTO_UPDATE_STATISTICS OFF GO
Simulando alterações na tabela, inserindo 500 mil e alterando 732110 mil registros. Como as estatísticas foram alteradas para “manual”, com essas operações de DML elas estão totalmente desatualizadas.
INSERT INTO dbo.Teste_Estatisticas SELECT TOP 500000 ABS(CHECKSUM(NEWID()))%2,CONVERT(INTEGER,ABS(CHECKSUM(NEWID()))/100.0), TE2.First_Name, TE1.Last_Name, TE2.First_Name_Mother, TE1.Last_Name_Mother FROM Teste_Estatisticas TE1 CROSS JOIN Teste_Estatisticas TE2 GO DELETE FROM Teste_Estatisticas WHERE RandNum2 < 4500000 GO
Vamos aos resultados. Primeiro, limpe o cache para garantir que o otimizador não utilize nada em memória.
CHECKPOINT GO DBCC DROPCLEANBUFFERS GO DBCC DROPCLEANBUFFERS GO
Execute o select e observe o plano de execução gerado para essa query. Lembre-se de que esse plano foi gerado com base nas estatísticas desatualizadas.
SELECT * FROM Teste_Estatisticas WHERE RandNum2 > 5000000
Observe que o otimizador estimou 2260440 mil registros, mas foram retornadas 2686663 linhas (2260440–2686663=426223). O tempo de execução foi de 43 segundos com um esforço de CPU de 2,48664 e com um custo estimado de 21,4727.
Ok, feito isso, atualize as estatísticas, limpe o cache e execute novamente o select.
UPDATE STATISTICS dbo.Teste_Estatisticas GO CHECKPOINT GO DBCC DROPCLEANBUFFERS GO DBCC DROPCLEANBUFFERS GO SELECT * FROM Teste_Estatisticas WHERE RandNum2 > 5000000 GO
Observe o plano de execução:
Podemos notar algumas melhorias, como o fato de que foram retornadas 2686663 linhas das 2686570 estimadas (2686663-2686570=96). O tempo de execução foi de 31 segundos (contra 43 segundos da execução anterior) com um esforço de CPU de 2,95538 e com um custo estimado de 15,3585.
Com esse teste, podemos concluir que sim, estatísticas desatualizadas influenciam negativamente no desempenho. Agora imagine se essa query fosse parte de um relatório utilizado por dezenas de usuários e executado centenas de vezes no dia. O impacto final poderia ser muito significante, e isso porque estamos falando de uma única query. Por isso, a manutenção das estatísticas, assim como dos índices, deve ser realizada com muita atenção.
Também deve ser observado que o processo para atualizar as estatistas é “custoso”, por isso deve ser programada e analisada a melhor forma de fazer, e não simplesmente sair atualizando tudo a toda a hora. Dessa forma, estaríamos criando outro problema.
O impacto sempre vai existir, cabe a nós analisar qual estratégia será melhor para nosso ambiente.
Em operações de manipulações de dados, como DELETE, UPDATE, MERGE, a grosso modo, internamente o primeiro processo é localizar os dados e em seguida realizar a operação. Por exemplo, ao executar um comando de DELETE, primeiro é feita a busca dos dados e depois a deleção. Essa busca nada mais é do que um select para localizar esses registros; com isso, em muitos casos, as estatísticas também podem impactar nesse tipo de operação.
Comparação
Em verde o resultado da consulta com as estatísticas atualizadas, e em vermelho o resultado da mesma consulta com as estatistas desatualizadas.
Com esse teste, podemos identificar um ganho aproximado de 20% somente por estar com as estatísticas atualizadas.
E, como disse anteriormente, se pensarmos no ambiente como um todo, onde milhares de consultas são executadas por centenas de usuários, ganhos de segundos em cada operação são muito importantes para manter o ambiente com um nível legal de desempenho.
Grande abraço e bons estudos.