Indexes can become fragmented over time in Microsoft SQL Server. In order to maintain best performance in WIMS these indexes should be checked on a regular basis (monthly). Microsoft recommends the following based on avg_fragmentation_in_percent:
| avg_fragmentation_in_percent value |
Corrective statement |
| > 5% and < = 30% |
ALTER INDEX REORGANIZE |
| > 30% |
ALTER INDEX REBUILD |
To determine avg_fragmentation_in_percent value:
SELECT
object_name(IPS.object_id) AS [TableName], SI.name AS [IndexName], IPS.Index_type_desc,
IPS.avg_fragmentation_in_percent,IPS.avg_fragment_size_in_pages,IPS.avg_page_space_used_in_percent,
IPS.record_count,IPS.ghost_record_count,IPS.fragment_count,IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(db_id(DB_NAME()), NULL, NULL, NULL , 'DETAILED') IPS
JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0
order by IPS.avg_fragmentation_in_percent desc
To Rebuild/reorganize a Heap type Index. (Heap indexes can only be rebuilt)
ALTER
TABLE MDEVARS rebuild
To rebuild a non Heap Index (i.e. NONCLUSTERED INDEX):
ALTER
INDEX PK_DATADD4_AT ON DATADD4_AT REBUILD
To reorganize a non Heap Index (i.e. NONCLUSTERED INDEX):
ALTER
INDEX PK_DATADDH ON DATADDH REORGANIZE