Loading…

Monitorando a fragmentação dos índices

Um dos grandes problemas que temos com relação a performance é devido a fragmentação de nossos índices. Com o grande número de inserções, alterações e exclusões que ocorrem em nossas tabelas, os índices se fragmentam cada vez mais, ocasionando uma lentidão na manipulação dos dados desses índices.

Como você resolveria esse problema de fragmentação?

R: Essa é fácil Fabrício, criando uma rotina periódica que executa os procedimentos de REBUILD ou REORGANIZE para os meus índices.

Exato, mas com que frequencia você rodaria essa rotina? Quais as tabelas que precisam ter seus índices desfragmentados com uma maior frequência?

Imagina que você possua muitas tabelas com milhares de registros e só tenha uma janela de 1 hora para desfragmentar todos os seus índices. Voce terá que dividir a desfragmentação dos índices em vários dias além de também definir a frequência com que os índices serão reorganizados ou reconstruídos.

Esse é exatamente o ambiente que possuo, com muitas tabelas grandes e com apenas uma hora de janela para realizar esse procedimento.

Digamos que eu possua 100 tabelas em uma determinada database, desfragmentar as 80 menores tabelas demoram 40 minutos, outras 10 tabelas juntas demoram 50 min, outras 5 tabelas juntas demoram 45 minutos, 3 tabelas demoram 40 minutos e 2 tabelas demoram 50 minutos.

Nessa situação, eu criaria 5 jobs de manutenção dos índices:

  • Job 1 – 80 tabelas
  • Job 2 – 10 tabelas
  • Job 3 – 5 tabelas
  • Job 4 – 3 Tabelas
  • Job 5 – 2 Tabelas

Mas como eu saberia com que frequência rodar cada um desses jobs?

Para definir essa frequência, eu utilizo uma rotina que armazena diariamente a fragmentação de todos os índices de minhas principais databases. Como a query para verificar a fragmentação dos índices é um pouco custosa, eu utilizo um servidor D-1 que recebe um restore diário das bases de produção para executar esse procedimento. Caso você não possua um servidor D-1 você deve agendar um job para executar esse procedimento em um horário de pouco movimento.

Inicialmente, devemos criar uma tabela que armazenará nosso histórico de fragmentação.

CREATE TABLE [dbo].[Hitorico_Fragmentacao_Indice](
[Id_Hitorico_Fragmentacao_Indice] [int] IDENTITY(1,1) NOT NULL,
[Dt_Referencia] [datetime] NULL,
[Nm_Servidor] [varchar](50) NULL,
[Nm_Database] [varchar](50) NULL,
[Nm_Tabela] [varchar](50) NULL,
[Nm_Indice] [varchar](70) NULL,
[Avg_Fragmentation_In_Percent] [numeric](5, 2) NULL,
[Page_Count] [int] NULL,
[Fill_Factor] [tinyint] NULL)

Para popular essa tabela, basta rodar a query abaixo que retorna a fragmentação de todos os índices da database em que ela está sendo executada.

INSERT INTO Hitorico_Fragmentacao_Indice(Dt_Referencia,Nm_Servidor,Nm_Database,Nm_Tabela,Nm_Indice,Avg_Fragmentation_In_Percent,
Page_Count,Fill_Factor)
SELECT getdate(), @@servername,  db_name(db_id()), object_name(B.Object_id), B.Name,  avg_fragmentation_in_percent,page_Count,fill_factor
FROM sys.dm_db_index_physical_stats(db_id(),null,null,null,null) A
join sys.indexes B on a.object_id = B.Object_id and A.index_id = B.index_id
ORDER BY object_name(B.Object_id), B.index_id

OBS: Lembrando que essa é a query demorada e deve ser executada em um horário de pouco movimento do banco de dados.

Com essa importante informação sendo armazenada no banco de dados, recebo no meu CheckList diário do banco de dados um relatório de todos os índices que estão com uma fragmentação maior que 5% e consigo identificar os índices que mais estão se fragmentando para analisar um possível aumento na frequência do job de desfragmentação.

Segue abaixo a query que gera os dados para a planilha:

declare @Dt_Referencia datetime
set @Dt_Referencia = cast(floor(cast( getdate() as float)) as datetime)

SELECT Nm_Servidor, Nm_Database, Nm_Tabela, Nm_Indice, Avg_Fragmentation_In_Percent, Page_Count, Fill_Factor
FROM Hitorico_Fragmentacao_Indice (nolock)
WHERE Avg_Fragmentation_In_Percent > 5
AND page_count > 1000   -- Eliminar índices pequenos
AND Dt_Referencia >= @Dt_Referencia

Essa query retorna o seguinte resultado:

Quando a fragmentação de algum índice ultrapassa 15%, essa linha ja vem vermelha e em negrito na minha planilha de CheckList.

Devido a facilidade de implementação dessa rotina (para quem possui uma janela de execução), acredito que seria bem interessante guardar essa informação e aumentar a baseline do seu banco de dados.

Além disso, no meu próximo post mostrarei como essa informação me ajuda a definir o valor do FILL FACTOR de minhas tabelas.

 

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

32 thoughts on “Monitorando a fragmentação dos índices

  1. Grande Fabricio,

    Parabens pelo post. Mas me ficou uma duvida aqui, depois voce executa manualmente um rebuild ou reorganize nessas tabelas, ou você possui alguma procedure na qual consegues fazer um controle de quais tabelas serao realizadas o procedimento?

    Abraços e mais uma vez… meus parabens.
    Marcos Freccia
    @SqlFreccia

    1. Valeu Marcos

      Não executo os REBUILDs manualmente, mas criei procedures e defini manualmente quais tabelas serão reindexadas por cada procedure.
      stpReindex_NomeDatabase_1
      stpReindex_NomeDatabase_2
      stpReindex_NomeDatabase_3

      Entretanto, você também pode ser criar uma lógica dentro das procedures para definir as tabelas que serão reindexadas com alguma tabela física de controlando. Ainda não fiz isso mas posso fazer no futuro.

      Abraços

  2. Fala Fabricio,

    excelente artigo, e posso dizer: facilitou bastante a adm dos meus indices…
    pra melhorar, coloquei a query no send_dbmail e agora recebo um email com o relatorio dos indices mais criticos…

    abcs,

    Kleber.

  3. Parabéns pelo Artigo Fabrício!

    Esse script ficou excelente. Só tenho uma dúvida: Porque que em menos de 1 hora meus índices estão todos fragmentados novamente? Algumas vezes ao ponto de ter de fazer REBUILD.

    Obrigado.

    1. Obrigado Afrânio.

      Os seus índices devem sofrer muito update/delete/insert e os novos dados devem estar sendo incluídos no meio dos índices e não no final, como acontece em um indice de uma coluna identity.

      Verifica como está o FILLFACTOR dos seus índices. Talvez será necessário algum ajuste (cada caso é um caso).

      Abraços

  4. Boa Tarde! Estou recebendo um erro quando executo o INSERT ( String or binary data would be truncated ). Será que poderia me ajudar? Muito Obrigado e Parabéns por ser um grande profissional e dividir suas informações com a gente!

  5. Boa Tarde, Parabéns pelo Post, muito bem explicado.

    Poderia postar um exemplo de Rebuild e Reorganize.

    Como saber qual a melhor opção a ser usada?

    Obrigado.

  6. Olá boa tarde

    Os índices que estão com a coluna “avg_fragmentation_in_percent” como “0” ou “0,15….” significam que não seria necessário fazer a desfragmentação ?

    Obrigado
    Thiago

        1. Olá Paulo,

          Índices com menos de 1000 páginas são muito pequenos e não fariam muita diferença. Por isso, se preocupe apenas com índices com mais de 1000 páginas e com uma fragmentação maior do que 10%.

          Abraço e bons estudos.

          Fabrício

  7. Boa noite Fabrício!
    Ao executar o comando para inserção dos registros na tabela criada está sendo apresentada a seguinte mensagem de erro:

    Msg 102, Level 15, State 1, Line 4
    Incorrect syntax near ‘)’.

    Já realizei diversas alterações e a mensagem de erro continua sendo apresentada.

    Pode me ajudar?!

    Atenciosamente,

      1. Estou tendo o mesmo problema para o Insert

        Msg 102, Level 15, State 1, Line 4
        Incorrect syntax near ‘)’.

        Teria alguma dica para me ajudar?

        Obrigado

        Rodrigo

  8. Olá Fabrício

    Muito legal…como você costuma fazer com a fragmentação das heaps?

    Sei que o ideal é ter um índice Clustered, mas nem sempre é possível.

    Abraço

  9. Fabrício, terial algum script semelhante para rodar em SQL com compatibilidade do 2000?
    Estou apanhando para criar algum job para isso.

  10. Ola fabricio,

    Sigo seu blog e adoro suas dicas, e utilizo algumas, porem tenho uma instancia aqui que vem me causando problemas já algum tempo, esta comendo memoria que nem um come come ( velhos tempos de fliperama) bom, estava hoje fazendo verificação dos meus indices fragmentados, e existem muito 4010 indices com fragmetação acima dos 30%, fiz alguns manualmente mas quase instantaneamente ou passado algums minutos ja volta a 90% 95% do que estava antes, há alguma query ou algo que consiga fazer uma verificação se há algum vicio disso, visto que algumas dessas tabelas tem cerca de 430 registros.. estou achando muito estranho.

Deixe uma resposta

%d blogueiros gostam disto: