Verificando Índices

Verificando a taxa de fragmentação da database AdventureWorks tabela Person.Address

SELECT avg_page_space_used_in_percent,avg_fragmentation_in_percent,* FROM sys.dm_db_index_physical_stats
(DB_ID(N'AdventureWorks'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');

Reorganizando índices

alter index AK_Address_rowguid on Person.Address
reorganize

Reconstruindo índices

alter index AK_Address_rowguid on Person.Address
Rebuild

Quando usar Rebuild ou Reorganize:
Se avg_page_space_used_in_percent for menor que 75 e maior que 60
ou avg_fragmentation_in_percent for maior que 10 e menor 15
Neste caso usar Reorganize

Se avg_page_space_used_in_percent menor que 60 ou avg_fragmentation_in_percent maior que 15
Neste caso usar Rebuild