In order to keep the fragmentation ratio of indices low, you should re-organize the indices from time to time on MSSQL. Microsoft describes in the link below how you can check the fragmentation of indices on tables in MSSQL and then how you can re-organize (re-build) the indices to reduce the fragmentation percentage:
It would be nice to have a procedure that automatically re-organizes the indices in case the fragmentation percentage reaches a specific threshold. The procedure below does that:
create procedure ui_reorganize_indices @db_name nvarchar(127), @table_name nvarchar(127) as begin declare @avg_fragmentation_in_percent float declare @index_name nvarchar(127) declare @alter_index_command nvarchar(256) declare rebuild_index_cursor cursor local for select name AS IndedxName, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID (@db_name) , OBJECT_ID(@table_name) , NULL , NULL , NULL) AS a INNER JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id and avg_fragmentation_in_percent > 20.0; open rebuild_index_cursor fetch next from rebuild_index_cursor into @index_name, @avg_fragmentation_in_percent while @@fetch_status = 0 begin select @alter_index_command = formatmessage('alter index %s on %s REORGANIZE', @index_name, @table_name) print @alter_index_command exec (@alter_index_command) fetch next from rebuild_index_cursor into @index_name, @avg_fragmentation_in_percent end close rebuild_index_cursor end go
Have you guys used similar procedures to improve the health of your indices?