Assim como o disco, fisicamente, torna-se fragmentado; também acontece com toda organização lógica que venha fazer uso desta mídia. Tablespaces, tabelas, e, índices. Todos se fragmentam. Faz parte do negócio. É regra do jogo, se quiser brincar, aceite-as. A fragmentação (não importa o objeto de banco de dados em questão) acontecerá mais, ou menos, dependendo do tipo de operação do banco de dados.

Bancos de dados transacionais (OLTP) tendem a se fragmentar muito muito mais do bancos de dados de consulta massiva (OLAP/DW). Pelo menos, se sustentados da maneira correta.

Quanto mais escrita, maiores as probabilidades de fragmentação. A modelagem podem afetar, também, a fragmentação.

Claro que, tudo que está fragmentado está bagunçado. E, tudo que está bagunçado não vai bem. No caso de banco de dados, isto significa uma redução de performance.

O efeito colateral mais esperado da desfragmentação é o aumento de performance, ou, se preferir: recuperação da performance perdida. Neste artigo, trataremos, puramente, da fragmentação de índices.

Mas, para os loucos por um código pronto, vamos ao que interessa: Fragmentação de Índices no Microsoft SQL Server. Lidar com fragmentação, envolve duas etapas básicas: identificação e ação.

Como identificar índices fragmentados no SQL Server? Simples, segue a “receitosa” do bolo:

USE <sua base>;

SELECT dbschemas.[name] AS ‘Schema’ , dbtables.[name] AS ‘Tabela’ ,
                dbindexes.[name] ‘Indice’, ROUND(indexstats.avg_fragmentation_in_percent,1) ‘Fragmentacao’
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]
JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
JOIN sys.indexes dbindexes ON dbindexes.[object_id] = indexstats.[object_id] AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID() AND dbindexes.name IS NOT NULL
ORDER BY indexstats.avg_fragmentation_in_percent desc;

Receberemos do SQL Server uma lista de todos os índices, de todas as tabelas, ordenados pela sua fragmentação: do mais fragmentado, para, o índice com a menor fragmentação. Obviamente, que quanto mais fragmentado: pior.

No dia-a-dia, na sustentação de bases SQL Server, eu adoto a seguinte política: índices com 35% ou mais de de fragmentação: capo fora, oops, faço a recriação do índice (rebuild). Já os caras com mais de 10% e menos de 34%, eu faço a reorganização dos mesmos.

Reorganizando um único índice:

ALTER INDEX <nome_do_índice> ON <schema>.<tabela>  REORGANIZE ;

Recriando (Rebuild) de um único índice:

ALTER INDEX <nome_do_índice> ON <schema>.<tabela>  REBUILD ;

Largando o aço na geral, e, fazendo todas as tabelas de uma única vez:

ALTER INDEX ALL ON <schema>.<table>  REORGANIZE [ou REBUILD] ;

Nota: Só use ALL se você conhecer muito bem o seu banco de dados, e, seus “tempos” (ou, se quiser irritar muito o seu chefe).

De quanto em quanto tempo devo desfragmentar meus índices? Primeiro, você deve executar a primeira parte do plano: Identificação. Sinceramente, índices desfragmentados tornam-se um problema a partir de 20~25%. Enquanto tiver tabelas com menos de 10~15% nem mexa nisso. É importante que haja uma tarefa agendada para rodar, semanalmente, uma verificação de fragmentação. Quanto mais fragmentar, menor será a periodicidade de sua desfragmentação.