Example and Notes
Index fragmentation can contribute to slower reads and heavier I/O. This sample lists fragmented indexes and generates a rebuild command.
CREATE Procedure [dbo].[uspIndexFragmentation]
As
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent ,
'ALTER INDEX ' + ind.name + ' ON ' + OBJECT_NAME(ind.OBJECT_ID) + ' REBUILD ' + char(13) + char(10) + 'GO' AS IndexRebuild
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 30
--Order By TableName
ORDER BY indexstats.avg_fragmentation_in_percent DESC
Production Review
WSI can adapt this script for your database, improve error handling, tune performance, document the logic, and help deploy it safely.