Os dados dentro de um índice são armazenados em ordem classificatória. Devido a repetidos eventos de inclusão e exclusão, podemos ter uma fragmentação dos índices, por exemplo, ao remover uma linha da tabela, a entrada referente ao índice também precisa ser removida, com isso, ficamos com uma lacuna na página do índice que não é recuperado pelo SQL Server devido ao custo da reclassificação do índice.
Vamos entender como controlar a taxa de fragmentação de nossos índices:
FILL FACTOR
Determina a porcentagem de espaço livre em cada página de índice, ou seja, o quanto pode ser preenchido e quanto deve ser mantido em branco reservando para alterações realizadas na tabela (inclusão e alteração). Por exemplo ao criarmos um índice, se determinarmos FILL FACTOR em 90%, isso quer dizer que o SQL Server irá reservar apenas 10% de cada página com espaço livre que resulta em uma reorganização mais rápida pois temos 10% de espaço em branco para manobras em cada página de dados.
E quando devo usar FILL FACTOR?
A resposta é depende… do número de alterações que sua tabela recebe.
CUIDADO: ao configurar o FILL FACTOR do seu índice com porcentagem de preenchimento muito baixo, pois o excesso de informação em branco nas páginas faz com que o SQL Server tenha que percorrer muitas paginas para retornar a informação desejada podendo tornar sua Query lenta.
Desfragmentando um índice
Com os espaços vazios deixados nas tabelas, periodicamente será necessário realizar a desfragmentação do índice que deve ser executada utilizando o comando ALTER INDEX:
REBUILD: Reconstrói todos os índices deixando as páginas com o preenchimento configurado na opção FILL FACTOR. A reconstrução do índice implica em criar toda a estrutura B-Tree novamente. Caso seja necessário a manutenção com concorrência no banco de dados, será necessário recriar o índice com a opção ONLINE para que seja obtido um bloqueio compartilhado e impeça alterações até a finalização do REBUILD.
Exemplo para reconstruir todos os índices de uma tabela:
ALTER INDEX ALL ON tabela REBUILD
REORGANIZE: Remove a fragmentação apenas do nível folha, ou seja, as páginas de nível intermediário e raiz não são desfragmentadas. REORGANIZE é uma operação que não gera bloqueio a longo prazo (online)
Exemplo para reorganizar o índice de uma tabela:
ALTER INDEX nome_índice ON tabela REORGANIZE
Recomendação: Devemos usar REBUILD quando a fragmentação do índice estiver acima de 40% e utilizar REORGANIZE quando a fragmentação estiver entre 10% a 40%
Consultando a fragmentação de um índice
A consulta abaixo deve ser utilizada para identificar o nível de fragmentação do seu índice:
SELECT a.index_id, name, avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorksLT2008'), OBJECT_ID(N'SalesLT.Product'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
avg_fragmentation_in_percent - O percentual de fragmentação.
fragment_count - O número de fragmentos (fisicamente páginas de folha consecutivos) no índice.
avg_fragment_size_in_pages - Número médio de páginas em um fragmento de um índice.
Desativando um ìndice
Um índice pode ser Desativado utilizando ALTER INDEX:
ALTER INDEX nome_índice ON tabela DISABLE
Obrigado
