Stored Procedure to rebuild or reorganise fragmented database indexes
A re-useable Stored Procedure that you can call to re-index any database by entering the name of the database.
Aim
This is a handy Stored Procedure that you can literally copy and paste into your Master or Utilities database, to re-index all of the tables within a particular database.
The most useful part of this is that you enter the database name when you call the procedure, meaning you can loop through all of your databases in one go by calling the same procedure as follows, if you have linked servers you can also call it from another server when called correctly.
Multiple call SQL
EXEC [Maint].DatabaseReIndex 'ClaytabaseAcademy'
EXEC [Maint].DatabaseReIndex 'YourDatabaseName2'While it is possible to run this during working hours, it is always more sensible to run this when there is very little work being done on the server.
SQL
USE [ClaytabaseAcademy]GOBEGIN TRYEXEC sp_executesql N'CREATE SCHEMA Maint'END TRYBEGIN CATCHEND CATCHGOCREATE PROC Maint.DatabaseReIndex(@Database VARCHAR(100)) AS BEGINDECLARE @DbID SMALLINT=DB_ID(@Database)--Get Database IDDECLARE @I TABLE (IndexTempID INT IDENTITY(1,1),SchemaName NVARCHAR(128),TableName NVARCHAR(128),IndexName NVARCHAR(128),IndexFrag FLOAT)INSERT INTO @IEXEC ('USE '+@Database+';SELECT sch.name,OBJECT_NAME(ind.OBJECT_ID) AS TableName,ind.name IndexName,indexstats.avg_fragmentation_in_percentFROM sys.dm_db_index_physical_stats('+@DbID+', NULL, NULL, NULL, NULL) indexstatsINNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_idINNER JOIN sys.objects obj on obj.object_id=indexstats.object_idINNER JOIN sys.schemas as sch ON sch.schema_id = obj.schema_idWHERE indexstats.avg_fragmentation_in_percent > 10 AND indexstats.index_type_desc<>''HEAP''ORDER BY indexstats.avg_fragmentation_in_percent DESC')--Get index data and fragmentation, set the percentage as high or low as you needDECLARE @IndexTempID BIGINT=0,@SchemaName NVARCHAR(128),@TableName NVARCHAR(128),@IndexName NVARCHAR(128),@IndexFrag FLOATSELECT * FROM @I--View your results, comment out if not needed...-- Loop through the indexesWHILE @IndexTempID IS NOT NULL BEGIN SELECT @SchemaName=SchemaName,@TableName=TableName,@IndexName=IndexName,@IndexFrag=IndexFrag FROM @I WHERE IndexTempID=@IndexTempID IF @IndexName IS NOT NULL AND @SchemaName IS NOT NULL AND @TableName IS NOT NULL BEGIN IF @IndexFrag<30. BEGIN--Low fragmentation can use re-organise, set at 30 as per most articles PRINT 'USE '+@Database+'; ALTER INDEX ' + @IndexName + N' ON ' + @SchemaName + N'.' + @TableName + N' REORGANIZE' EXEC('USE '+@Database+'; ALTER INDEX ' + @IndexName + N' ON ' + @SchemaName + N'.' + @TableName + N' REORGANIZE') END ELSE BEGIN--High fragmentation needs re-build PRINT 'USE '+@Database+'; ALTER INDEX ' + @IndexName + N' ON ' + @SchemaName + N'.' + @TableName + N' REBUILD' EXEC('USE '+@Database+'; ALTER INDEX ' + @IndexName + N' ON ' + @SchemaName + N'.' + @TableName + N' REBUILD') END END SET @IndexTempID=(SELECT MIN(IndexTempID) FROM @I WHERE IndexTempID>@IndexTempID)ENDENDGO