Desfragmentando índices

Quando uma página de dados completa 100% e mais dados precisam ser adicionados, o page split acontece. Para abrir caminho para mais dados, o SQL SERVER precisa mudar metade dos dados da página cheia para a nova página de dados. A nova página de dados, é criada no final de todas as páginas de dados. Desta forma, ao invés de ir de uma página para a seguinte para procurar os dados, o Sql Server precisa ir de uma página para uma outra que pode estar em qualquer lugar, não necessariamente a próxima.

Isto é fragmentação de índice.A severidade da fragmentação de índices pode ser determinada através da query:

SELECT
i.name
,avg_fragmentation_in_percent
,fragment_count
,avg_fragment_size_in_pages
,page_count
FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) s
inner join sys.indexes i on (i.object_id=s.object_id and s.index_id=i.index_id)
where avg_fragmentation_in_percent>0
order by 3,4

Podemos desfragmentar os índices de várias formas, por exemplo, com o INDEXDEFRAG:

DBCC INDEXDEFRAG(NOME_DO_BANCO,NOME_TABELA,NOME_INDICE)

Porém o index defrag, tem grandes chances de ser deprecated em versões futuras do sql server. Podemos realizar
este procedimento com o comando alter index.

ALTER INDEX nome_indice ON nome_tabela REORGANIZE

Ou podemos usar o comando REBUILD, reconstruindo o índice e determinando novas opções de FILLFACTOR:

alter index NOME_INDICE on NOME_TABELA
REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON)

Podemos realizar a mesma operação, em todos os índices de uma determinada tabela:

alter index all on NOME_TABELA
REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON)

Antes de realizar qualquer alteração no seu banco de dados, realize os devidos backups (e verifique a sua integridade). O comando DBCC INDEXDEFRAG, trabalha com transação portanto se você parar a execução do comando no meio, perderá toda a desfragmentação realizada.

Até a próxima!