Loading…

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.

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

20 thoughts on “Rotina para Atualizar as Estatísticas do seu Banco de Dados

  1. 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?

    1. 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.

      1. 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.

    1. 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.

  2. 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

  3. 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

    1. 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

  4. 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

    1. 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.

  5. 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!

Deixe uma resposta

%d blogueiros gostam disto: