Rotina para Atualizar as Estatísticas do seu Banco de Dados
Fala Pessoal,
Nós sempre ouvimos que as estatísticas de um Banco de Dados SQL Server devem estar sempre atualizadas para que o Query Optimizer possa gerar o melhor plano de execução para nossas consultas. Para isso, devemos habilitar a opção ‘Auto Update Statistics’ nas nossas databases.
Entretanto, com essa opção habilitada, se nossa tabela tiver mais de 500 registros (praticamente todas), as estatísticas dessa tabela só serão atualizadas quando tivermos (500 + 20% do tamanho da tabela) de alterações na tabela. O pior é que isso pode acontecer no meio do dia gerando um custo no seu ambiente de produção.
No meu ambiente, tenho uma tabela que é muito utilizada e a mesma possui 70 milhões de registros. Se eu fosse esperar as estatísticas serem atualizadas automaticamente, desconsiderando as estatísticas de índices que são atualizadas por exemplo com um REBUILD, minhas estatísticas seriam atualizadas quando eu tivesse (500 + 20%*70.000.000 = 14.000.500) de alterações. No meu caso, isso iria demorar um tempo muito grande e querys com planos não ótimos poderiam ser geradas.
Para tentar minimizar esse problema, resolvi eu mesmo atualizar minhas estatísticas quando tivesse 0,5% de alterações na minha tabela (e não 20% como é o default). Defini esse valor pois a operação de UPDATE STATISTICS WITH FULLSCAN é muito custosa e eu não tenho uma janela para atualizar todas as estatísticas diariamente. Com isso, essa minha tabela teria as estatísticas atualizadas quando tivesse (70.000.000 * 0.005 = 350.000) alterações.
Como de madrugada já tenho janelas de manutenção de índices e dezenas de rotinas rodando, fiquei na VPN até às 22:40 e rodei um UPDATE STATISTICS nesse horário para analisar o impacto que teria no meu Banco de Dados que é 7×24. Então, defini minha janela de 22:40 às 23:50 para executar esse procedimento.
Em cada database que quero executar a atualização das estatísticas, criei a procedure abaixo. Em seguida fiz um job que roda diariamente às 22:40 com um step para cada database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
CREATE PROCEDURE [dbo].[stpAtualiza_Estatisticas] As BEGIN SET NOCOUNT ON -- Sai da rotina quando a janela de manutenção é finalizada IF GETDATE()> dateadd(mi,+50,dateadd(hh,+23,cast(floor(cast(getdate()as float))as datetime)))-- hora > 23:50 BEGIN RETURN END Create table #Atualiza_Estatisticas( Id_Estatistica int identity(1,1), Ds_Comando varchar(4000), Nr_Linha int) ;WITH Tamanho_Tabelas AS ( SELECT obj.name, prt.rows FROM sys.objects obj JOIN sys.indexes idx on obj.object_id= idx.object_id JOIN sys.partitions prt on obj.object_id= prt.object_id JOIN sys.allocation_units alloc on alloc.container_id= prt.partition_id WHERE obj.type= 'U' AND idx.index_id IN (0, 1)and prt.rows> 1000 GROUP BY obj.name, prt.rows) insert into #Atualiza_Estatisticas(Ds_Comando,Nr_Linha) SELECT 'UPDATE STATISTICS ' + B.name+ ' ' + A.name+ ' WITH FULLSCAN', D.rows FROM sys.stats A join sys.sysobjects B on A.object_id = B.id join sys.sysindexes C on C.id = B.id and A.name= C.Name JOIN Tamanho_Tabelas D on B.name= D.Name WHERE C.rowmodctr > 100 and C.rowmodctr> D.rows*.005 and substring( B.name,1,3) not in ('sys','dtp') ORDER BY D.rows declare @Loop int, @Comando nvarchar(4000) set @Loop = 1 while exists(select top 1 null from #Atualiza_Estatisticas) begin IF GETDATE()> dateadd(mi,+50,dateadd(hh,+23,cast(floor(cast(getdate()as float))as datetime)))-- hora > 23:50 am BEGIN BREAK -- Sai do loop quando acabar a janela de manutenção END select @Comando = Ds_Comando from #Atualiza_Estatisticas where Id_Estatistica = @Loop EXECUTE sp_executesql @Comando delete from #Atualiza_Estatisticas where Id_Estatistica = @Loop set @Loop= @Loop + 1 end END |
Caso alguém tenha alguma outra sugestão para uma rotina de atualização de estatística, pode deixar um comentário.
Lembre-se, se você tem uma janela grande, você pode atualizar todas as estatísticas diariamente que o Query Optimizer irá agradecer.
Gostou dessa Dica?
Curta, comente, compartilhe…
Assine meu canal no Youtube e curta minha página no Facebook para receber Dicas de Leituras, Vídeos e Eventos sobre SQL Server.
Aprenda a criar essa e outras rotinas para administrar melhor seu SQL Server no meu Treinamento de Tarefas do Dia a Dia de um DBA.
Até a próxima.
Fabrício Lima
MCITP – Database Administrator
Consultor e Instrutor SQL Server
Trabalha com SQL Server desde 2006
Muito bom o artigo!
Já li quase todo o seu blog e agradeço a sua iniciativa de compartilhar suas experiências. Elas são valiosas no aprendizado e no dia-a-dia de qualquer DBA.
Analisando a lógica que você usou para montar a estratégia de otimização do plano de execução através da atualização de estatísticas, me surgiu uma dúvida: Não seria necessário estabelecer uma limpeza programada do cache como parte da estratégia?
Fui pesquisar … 🙂
Certain changes in a database can cause an execution plan to be either inefficient or invalid, based on the new state of the database. SQL Server detects the changes that invalidate an execution plan and marks the plan as not valid. A new plan must then be recompiled for the next connection that executes the query. The conditions that invalidate a plan include the following:
Changes made to a table or view referenced by the query (ALTER TABLE and ALTER VIEW).
Changes to any indexes used by the execution plan.
Updates on statistics used by the execution plan, generated either explicitly from a statement, such as UPDATE STATISTICS, or generated automatically.
Dropping an index used by the execution plan.
An explicit call to sp_recompile.
Large numbers of changes to keys (generated by INSERT or DELETE statements from other users that modify a table referenced by the query).
For tables with triggers, if the number of rows in the inserted or deleted tables grows significantly.
Executing a stored procedure using the WITH RECOMPILE option.
Obrigado por compartilhar essa informação Augusto.
Como você mesmo postou, o SQL Server já faz esse trabalho de tirar os planos do cache para nós. Dava para fazer um teste analisando os planos de execução que estão em cache e atualizando as estatísticas para ver se os mesmos sairiam do cache. Quando der eu faço isso.
Abraços.
Uma dúvida,
O correto não seria:
D.rows * 1.005
ao invés de:
D.rows * .005
Fala Guilherme,
Não sei se entendi bem a dúvida, mas esse valor é definido por você.
70.000.000 * 1.005 = 70.350.000 , o que daria mais que a tabela toda. Muito maior que os 20% que o SQL já faz a atualização por default.
70.000.000 * 0.005 = 350.000 — Valor que eu defini
Poderia ser:
70.000.000 * 0.01 = 700.000
Fica a seu critério esse valor.
Olá Fabricio beleza?
As atualizações de estatísticas como as manutenções em ambientes 24×7 são bem complicadas mesmo. Conheço bem!!! 😉
Uma dica: em vez de criar um procedimento com o mesmo código TSQL para casa base, por que você não cria um procedimento na base “master” com a inicial “sp_”. Claro, isso é uma recomendação para procedimentos administrativos. Que é o caso desse procedimento de manutenção de estatísticas
Guilherme,
Respondendo a sua pergunta: O valor “D.rows*.005” está certo mesmo haja vista que a coluna “C.rowmodctr” guarda a quantidade de modificações para o HEAP ou index!
Abraços,
Leivio
IF GETDATE()> dateadd(mi,+50,dateadd(hh,+23,cast(floor(cast(getdate()as float))as datetime)))– hora > 23:50
Incorrect syntax near ‘–’
Não reconhece hora
Fala Luis,
Quando você copia a query do wordpress alguns caracteres ficam desformatados.
No seu caso da para ver claramente que é antes da palavra hora. Mude para –(comentário) no sql server.
Abraços
Excelente artigo sobre estatísticas.
Fala Fabricio,
Mesmo depois de algum tempo, o artigo continua sendo útil para os profissionais de SQL Server. Parabéns!.
Abraços.
Obrigado Rafael.
Oi Fabrício,
Excelente post! Concordo com o comentário do Rafael e queria te agradecer pela partilha de suas experiências/conhecimento. Tenho duas perguntas:
1) Você já utilizou o TF2371 (threshold dinâmico pra auto-update stats) em algum ambiente? Tem algum comentário sobre isso?
2) Sobre a rotina, algum motivo em específico pra criar uma step pra cada base?
[]’s
Olá Renato,
Respondendo a suas perguntas:
1) Você já utilizou o TF2371 (threshold dinâmico pra auto-update stats) em algum ambiente? Tem algum comentário sobre isso?
Nunca utilizei não. Não sou muito fã de utilizar TF em produção.
2) Sobre a rotina, algum motivo em específico pra criar uma step pra cada base?
Não tem não. Pode fazer um script que rode em todas as databases.]
Obrigado.
Olá! gostei muito do artigo mas tenho uma dúvida: qual o motivo dessa cláusula: “and prt.rows> 1000” ?
Obrigado Dani.
O motivo é apenas para ignorar tabelas pequenas ja que não faz direfença.
Entendi!
Obrigada por responder! 🙂
Obrigada por responder! 🙂
De nada. Estamos ai.
Fabricio, bom dia!
Se passaram mais de 11 anos desde quando esse artigo foi escrito e com isso surgiram novas funcionalidades no SQL Server.
Ao executar essa rotina em um banco que está sendo auditado pelo CDC do SQL Server, ele encontra algumas tabelas que não estão passiveis de atualização e acaba explodindo um erro no meio da execução com a mensagem “Tabela dbo_XXX… não encontrada”
Por conta disso, atualizei o pedaçõ do código que carrega o nome das tabelas e pegando apenas as tabelas com Owner DBO, uma vez que as tabelas de auditoria que geram erro são do Owner CDC.
Dessa forma passou liso.
Basta atualizar esse bloco:
;WITH Tamanho_Tabelas AS (
SELECT obj.name, prt.rows
FROM sys.objects obj
JOIN sys.indexes idx on obj.object_id= idx.object_id
JOIN sys.partitions prt on obj.object_id= prt.object_id
JOIN sys.allocation_units alloc on alloc.container_id= prt.partition_id
WHERE obj.type= ‘U’ AND idx.index_id IN (0, 1)and prt.rows> 1000
GROUP BY obj.name, prt.rows)
insert into #Atualiza_Estatisticas(Ds_Comando,Nr_Linha)
SELECT ‘UPDATE STATISTICS ‘ + B.name+ ‘ ‘ + A.name+ ‘ WITH FULLSCAN’, D.rows
FROM sys.stats A
join sys.sysobjects B on A.object_id = B.id
join sys.sysindexes C on C.id = B.id and A.name= C.Name
join sys.sysusers U on U.uid = B.uid
JOIN Tamanho_Tabelas D on B.name= D.Name
WHERE U.name = ‘dbo’
and C.rowmodctr > 100
and C.rowmodctr> D.rows*.005
and substring( B.name,1,3) not in (‘sys’,’dtp’)
ORDER BY D.rows
Obrigado e até mais!
Obrigado Luciano.
No POwer Alerts V3 devo ter atualizado isso também.