Rebuild, recreate, or reindex indexes on SQL Server tables that have fragmentation.
DECLARE @TableName varchar(200),
@IndexName varchar(200),
@SqlCommand nvarchar(500)
DECLARE TablesAndIndexes CURSOR FOR
SELECT Tables.Name as TableName,
Indexes.Name as IndexName
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') AS Stats
inner join sys.indexes as Indexes on Stats.Index_id = Indexes.Index_ID and Stats.object_ID = Indexes.object_ID
inner join sys.objects as Tables on Indexes.Object_ID = Tables.Object_ID
WHERE Indexes.Name IS NOT NULL
AND avg_fragmentation_in_percent > 10.0
AND Stats.Index_id > 0
AND Page_Count > 300
OPEN TablesAndIndexes
FETCH TablesAndIndexes
INTO @TableName,
@IndexName
-- start the main processing loop.
WHILE @@Fetch_Status = 0
BEGIN
--Print
Print('Rebuilding: ' + @IndexName + ' ON ' + @TableName)
--Rebuild
SET @SqlCommand = 'ALTER INDEX ' + @IndexName + ' ON [' + @TableName + '] REBUILD'
EXEC (@SqlCommand)
-- Get the next row.
FETCH TablesAndIndexes
INTO @TableName,
@IndexName
END
CLOSE TablesAndIndexes
DEALLOCATE TablesAndIndexes