Fragmentação de Índices

[Total: 2    Average: 5/5]

Neste artigo vou falar um pouco sobre fragmentação de índices e as ações em alguns casos.

A necessidade de se ter um plano de manutenção para índices é fundamental, a frequência que você executará esses planos varia de banco para banco: criticidade, disponibilidade, taxa de CRUD, tamanho e quantidade de índices etc. Bancos maiores e com mais inserts, update e deletes tendem a necessitar manutenções mais frequentes.

Uma manutenção bem feita de índices ajuda bastante an performance geral do servidor, mas não pode ser feita sozinha, tem outros tipos de manutenções que também ajudam na performance, uma delas é uma manutenção de Estatísticas, mas isso fica para outro artigo.

Existem dois tipos de manutenções para os índices, rebuild e reorganize:

Rebuild:
– Apaga a estrutura do índice e cria uma nova, completa e reorganizada.
– Agora é hora de se calcular um Fill Factor efetivo e aplicar. Fill Factor a grosso modo é um “espaço vazio” deixado de propósito para o crescimento do índice, melhorando a sua fragmentação, O Fill Factor mais detalhado veremos em outro artigo.
– O Rebuild é um processo bem mais lento e custoso ao servidor, por isso agende muito bem uma janela para isso.

Reorganize:
– Como o próprio nome diz, apenas reorganiza, de modo sequencial os índices nas alocações/paginas em que eles já estão, tentando agrupar o máximo possível nas primeiras páginas.
– Não é possível nesse caso reajustar o Fill Factor.
– Atua apenas no nível leaf dos índices.

Quando usar o Rebuild e quando o Reorganize?

A boa prática (mercado) é que se utilize o Reorganize em índices com fragmentação entre 30% e 50% e o rebuild acima de 50%, simples assim?

Não!

Isso é só uma parte, mas é só o que se acha em 90% dos artigos da internet.
Primeiro vamos coletar alguns números com a function de sistema “sys.dm_db_index_physical_stats”. Ela analisa entre vários pontos, a fragmentação do índice.
A query para uma coleta mais simples é:

Dois campos muito importantes para esta primeira analise são avg_fragmentation_in_percent e page_count…
– avg_fragmentation_in_percent indica a média de fragmentação do índice dentro da tabela.
– page_count mostra a quantidade de páginas que a tabela ocupa.

Em um banco para testes, tenho 2 tabelas: tb_GrandeVolume (423.942 linhas) e tb_PequenoVolume (1.872 linhas)

Executando a query acima para estas duas tabelas, na situação que tenho aqui o retorno

Se fosse pela analise simples do 30% e 50% eu precisaria fazer o rebuild dos dois índices, mas analisando o índice da tabela tb_PequenoVolume, ela tem poucos registros, que ocupa apenas 2 páginas de dados, nesse caso um índice tem 50% de fragmentação porque está dividido nas 2 páginas, esse índice precisa ser tratado? De jeito nenhum!
Índices em tabelas com poucas páginas tendem a ter um percentual alto de fragmentação, mas isso não indica problema nenhum.
Então o próximo número a ser analisado é o page_count, a tabela tb_GrandeVolume tem muitas páginas de dados, e sua fragmentação está em 78,95%, sim, este está muito alto e precisa de um rebuild.
Por prática de mercado, é interessante somente levar em conta para estas analises índices com mais de 1.000 páginas, menos que isso é irrelevante.

Então não se assuste em ver altos números de fragmentação mesmo depois de rebuilds, claro, o percentual de fragmentação de tabelas pequenas não vai abaixar. Esse é o grande erro de uma análise superficial.

Vou dar um exemplo disso e mais um número para avaliação, o avg_fragment_size_in_pages, mas antes, é bom mostrar uma estrutura básica de um índice:

Esse é um exemplo de um índice BTree.
Indice 01

O BTree tem algumas nomenclaturas em sua estrutura que temos que levar em conta na próxima consulta:
Root: O primeiro nível (de cima para baixo) do índice
Leaf: O nível mais baixo do índice
Intermediarios ou branch: Todos os níveis entre o Root e o Leaf.

Na imagem, fica bem claro o caminho entre o Root e o Leaf, por exemplo, para buscar um registro número 60, o caminho é feito do Root para o Leaf como abaixo:

Indice 02

Uma execução detalhada da sys.dm_db_index_physical_stats, nos traz mais tres campos importantes: index_depth e index_level e avg_page_space_used_in_percent.

– index_depth: Números de níveis do índice BTree.
– index_level: Nível da analise.
– avg_page_space_used_in_percent: Percentual do índice gravado na página.

A query com este retorno exemplo é a seguinte:

Na query acima, vou utilizar os mesmos exemplos anteriores, com as tabelas tb_PequenoVolume e tb_GrandeVolume.

index_depth:
– O retorno nos mostra que o indice da tb_PequenoVolume tem apenas 2 níveis, o root e o leaf e a tabela tb_GrandeVolume tem 4 níveis, o root, 2 intermediarios e o leaf.

index_level
– Estes são os níveis, 0 é sempre o leaf e o maior é sempre o root.

avg_page_space_used_in_percent
– As fragmentações dos níveis mais altos não importa tanto, mas SEMPRE o leaf (index_level = 0), que é o último nível e o que tem mais tendencia a ficar fragmentado, até por ser mais numeroso que os níveis acima.
– Para este número a conta é diferente, quanto maior o valor, menos está fragmentado dentro da página e assim melhor agrupado, quanto menor o número, mais espalhado ele vai estar entre as páginas.

Então quanto menor for o avg_page_space_used_in_percent mais atenção o índice deve ter na hora de uma avaliação p/ manutenção. Outra prática de mercado diz que esta média deve estar maior que 75%, qualquer número abaixo disto, deve ser levado em conta para um rebuild ou reorganize.

Você pode aliar o avg_page_space_used_in_percent com avg_fragmentation_in_percent e o page_count para ter assim um ajuste bem mais fino de quais índices devem e como deve ser tratados na manutenção.

A query completa abaixo, vai deixar as coisas mais claras:

Comments

comments

No Comments Yet

Leave a Reply

%d bloggers like this: