Índice – FILLFACTOR (Fator de Preenchimento)

Posted on março 11, 2011

3


 

Opções de Índices

No SQL Server, a criação de um  índice apresenta algumas opções. Essas opções dizem o modo como o índice quando criado/alterado irá se comportar. Nesse post irei falar sobre o FILLFACTOR ou seja Fator de Preenchimento dos dados no nível folha do índice.

FILLFACTOR (Fator de Preenchimento)

Definição de quanto o nível folha será preenchida quando criamos ou recriamos um índice. Podemos especificar100% de FILLFACTOR, isso quer dizer que todo o nível folha do índice será preenchido, podemos também especificar 80%, que quer dizer que, 20% de cada página de dados no nível folha não será preenchida ou seja ficará livre. Podemos escolher a porcentagem de 0 – 100% e isso irá dizer como o índice irá se comportar.

O  opção FILLFACTOR só se aplica para o nível folha de um índice ou seja, a porcentagem especificada não será aplicada nem no nível intermediário e nem no nível raiz da B-Tree (Árvore Balanceada), para essa configuração de preenchimento em nível intermediário e nível raiz utilizamos o PAD_INDEX, mais nesse post não iremos entrar em detalhe sobre ele.

A configuração da quantidade de FILLFACTOR ou seja Fator de Preenchimento pode ser aplicada diretamente na criação do índice e em nível instância do banco de dados, sendo que se especificada na instância do SQL Server, todos os índices que forem criados sem ser especificado o FILLFACTOR, será criado com o valor especificado por padrão.

Quando há 100% de preenchimento dos dados no nível folha, podemos ter alguns problemas de performance (Insert’s, Update’s e Deletes), esse problema pode ocorrer porque quando não há mais espaço no índice a Engine do SQL Server tem que realizar do que é chamado de “PAGE SPLIT”, ou seja a divisão das páginas, como não há espaço disponível para ser adicionados mais dados, o SQL Server terá que alocar mais páginas de dados, para que os dados inseridos sejam colocados na estrutura do índice. Quando o PAGE SPLIT ocorre, 50% dos dados permanecem na página e os outros 50% são movidos para a nova pagina alocada, mantendo os dados logicamente em ordem.

Problemas Relacionados ao PAGE SPLIT:

  •  Os dados não são armazenados fisicamente em ordem;
  •  Geração de muitos IOs extra para a busca de informações, isso ocorre porque quando a divisão é realizada, os dados ficam espalhados e as pegínas que foram divididas ficam fragmentadas, fazendo com que os dados fiquem espalhados e “mal organizados”.

  • Menor densidade por página;

Fragmentação de Índices:

A fragmentação do índice ocorre porque quando há muitos PAGE SPLITS ou seja a divisão das páginas o SQL Server ao adicionar novas páginas não realiza a organização dos dados, sendo assim quanto mais PAGE SPLITS tivermos mais nossos dados ficaran “desorganizados”, fazendo com que cada vez mais o acesso aos dados fique mais difícil.

Para esse problema temos duas soluções:

Realização de REORGANIZE do Índice – Quando a porcentagem de fragmentação é menor ou igual à 15%

Realização de REBUILD do Índice – Quando a porcentagem de fragmentação é maior que 30%

Sendo que a grande diferença entre eles é que o REORGANIZE realiza a desfragmentação somente o nível folha, enquanto o REBUILD desfragmenta todos os níveis da B-Tree.

 

Mais como sabemos dessa fragmentãção?

Podemos utilizar a DM’V’s (Dynamic Management View’s)

SELECT *

FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,‘DETAILED’)

WHERE alloc_unit_type_desc = ‘IN_ROW_DATA’

image

 

Nessa consulta conseguimos ver informações de nível de fragmentação, de cada índice, tipo, quantidade de registros contidos no índice, nome do índice e outras informações, para saber se precisamos realizar um REBUILD ou REORGANIZE do índice, nos baseamos na coluna avg_fragmentation_in_percent.

 

Para solução, podemos criar planos de manutenção para nos ajudar a otimizar nosso banco de dados, utilizamos geralmente reorganize dos índices de dois em dois dias ou mais vezes semanalmente, dependendo do seu ambiente e rebuild finais de semana, porque para realizá-lo o banco de dados precisa colocar um LOCK  no índice porque em um REBUILD ele de fato estará fisicamente  organizando  as páginas de  dados.

 

Então sempre veja como está sua rotina de manutenção para índices!! antes que elas começem a te dar muitas dores de cabeça.

Marcado:
Posted in: Índices