Topic
How to find and clean fragmented indices (indexes) to mitigate SQL performance issues.
Instructions
The following t-sql command will retrieve the fragmentation on the indices. Replace 'DATABASENAME' with the name of the desired database.
Use DATABASENAMEGOSELECT OBJECT_NAME(i.object_id) AS TableName ,i.name AS TableIndexName ,phystat.avg_fragmentation_in_percentFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') phystatinner JOIN sys.indexes i ON i.object_id = phystat.object_id AND i.index_id = phystat.index_idWHERE phystat.avg_fragmentation_in_percent > 10Rebuild or reorganize any index with greater than 10% fragmentation. For more information, see Reorganize and Rebuild Indexes in Microsoft documentation.
Additional Information
Fragmentation of indexes can occur when there are a large number of read-write or delete operations occurring on a database. This can cause logical pages to be out of place with where they should be located within the data file. Fragmented databases can cause slow query times and SQL performance degradation. Index Fragmentation should be kept under 10%. If the index is 5%-30% then reorganization is recommended. If it is greater than 30% then it is necessary to rebuild.
