Índice – FILLFACTOR (Fator de Preenchimento) (pt-BR)

Índice – FILLFACTOR (Fator de Preenchimento) (pt-BR)



  • Introdução 

  • 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. Iremos  falar sobre o FILLFACTOR ou seja Fator de Preenchimento dos dados no nível folha de um índice.

  • FILLFACTOR (Fator de Preenchimento)

O FILLFACTOR é a Definição de quanto o nível folha de um índice será preenchida quando criamos ou recriamos um índice. Podemos especificar 100% 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 (LEAF), 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.

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 da instância.

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 uma operação  que é chamada 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.
Leave a Comment
  • Please add 6 and 5 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
Page 1 of 1 (2 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Luan.Moreno - SQL Soul edited Original. Comment: TOC

  • Flávio Honda edited Revision 1. Comment: alteração toc,espaçamento em branco

Page 1 of 1 (2 items)