Como e por que as estatísticas influenciam no desempenho do SQL Server? – Parte 02/02

Post Importado luiz.wordpress.old

Nesta série de artigos, vamos entender o que são as famosas estatísticas no Banco de Dados e também, quando, porque e quem as utiliza. Para posteriormente analisarmos a influência que ela exerce 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 (aqui) foram abordados os principais conceitos, passando por detalhes da estrutura interna e como e quando as estatísticas são criadas e atualizadas. Para essa 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. Neste caso os valores internos não tem nenhuma relevância pra nós, o que realmente importa é quantidade de registros.

PROVA REAL

Vamos alinhar os pensamentos, o intuito principal desse 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 à 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, 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, porem 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, 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 esta query fosse parte de um relatório utilizada por dezenas de usuários e executada centenas de vezes no dia, o impacto final poderia ser muito significante e isso que estamos falando de uma única query. Por isso a manutenção das estatísticas assim como dos índices devem ser realizadas com muita atenção.

Também deve ser observado, que o processo para atualizar as estatistas também é “custoso” e isso, deve ser programado e analisado a melhor forma de fazer, e não simplesmente sair atualizando tudo a toda a hora, assim 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 é feito 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ário, ganhos de segundos em cada operação são muito importante para manter o ambiente com um nível legal de desempenho.

Grande abraço e bons estudos.

Comente sobre isso: