This stored procedure reorganize the indexes for the DB_Name() – DB_ID() 🙂
I don’t remember from where i took some parts for this stored procedure. I’ve modify it according to my need in order to be used. I generally use this SP in a maintenance plan from RBCGxServerGeneral – RBC Etiqette
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 |
/****************************************************************************** ** Stored Procedure for Reorganize Indexes ** ** Return : ** Input : ** Output : ** Exception: ** ** Auth: Alexandru Toma ** Date: 19.12.2014 ******************************************************************************* ** Change History ******************************************************************************* ** Date: Author: Description: ** ---------- -------- ----------------------------------------- ** 19.12.2014 Alexandru Toma Creates this procedure *******************************************************************************/ CREATE PROCEDURE [dbo].[SP_Reorganize_Indexes] AS BEGIN SET NOCOUNT ON BEGIN TRY DECLARE @Maxfrag DECIMAL DECLARE @Minfrag DECIMAL DECLARE @DatabaseID INT DECLARE @IndexId INT DECLARE @IndexName VARCHAR(255) DECLARE @AvgFrag DECIMAL DECLARE @ObjectID INT DECLARE @Schemaname VARCHAR(255) DECLARE @TableName VARCHAR(255) DECLARE @Command VARCHAR(6000) DECLARE @Partition_number INT DECLARE @partitioncount int DECLARE @updateStatsThreshold INT SELECT @Maxfrag = 30.0 SELECT @Minfrag = 5.0 SELECT @updateStatsThreshold = 7 IF OBJECT_ID('tempdb..#IndexFragList') IS NOT NULL DROP TABLE #IndexFragList -- FILL INDEXES SELECT a.database_id DatabaseID , a.index_id IndexId , a.object_id ObjectID , b.name IndexName , avg_fragmentation_in_percent AvgFrag , t.name TableName , partition_number partition_number INTO #IndexFragList FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id JOIN sys.tables as t ON a.object_id = t.object_id WHERE a.index_id >= 1 AND avg_fragmentation_in_percent >= 5 --AND page_count >= 1000 DECLARE _indexes CURSOR FOR SELECT DatabaseID, IndexId, ObjectID, IndexName, IndexId, AvgFrag, TableName, partition_number FROM #IndexFragList ORDER BY AvgFrag DESC OPEN _indexes FETCH NEXT FROM _indexes INTO @DatabaseID, @IndexId, @ObjectID, @IndexName, @IndexId, @AvgFrag, @TableName, @Partition_number WHILE @@FETCH_STATUS = 0 BEGIN SET @Command = '' SELECT @Schemaname = s.name FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id WHERE o.object_id = @ObjectID IF @AvgFrag > @Maxfrag -- REBUILD INDEX SET @Command='ALTER INDEX [' + RTRIM(@IndexName) + '] ON [' + @Schemaname + '].[' + @TableName + '] REBUILD' ELSE IF @AvgFrag > @Minfrag -- REORGANIZE INDEX SET @Command='ALTER INDEX [' + RTRIM(@IndexName) + '] ON [' + @Schemaname + '].[' + @TableName + '] REORGANIZE' if @Command <> '' BEGIN SELECT @Partitioncount = count (*) FROM sys.partitions WHERE object_id = @ObjectID AND index_id = @IndexId IF @AvgFrag > @Maxfrag and @partitioncount = 0 SET @Command = @Command + ' WITH ( PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON , SORT_IN_TEMPDB = ON , ONLINE = ON )' IF @partitioncount > 1 SET @Command = @Command + N' PARTITION=' + CAST(@Partition_number AS nvarchar(10)) PRINT @Schemaname + '.' + @TableName + ' - Fragmentation:' + Cast(@AvgFrag as varchar(10)) PRINT @Command -- + ' --' + Cast(@AvgFrag as varchar(10)) BEGIN TRY EXEC(@Command) END TRY BEGIN CATCH PRINT ERROR_MESSAGE() END CATCH END FETCH NEXT FROM _indexes INTO @DatabaseID, @IndexId, @ObjectID, @IndexName, @IndexId, @AvgFrag, @TableName, @Partition_number END DROP TABLE #IndexFragList CLOSE _indexes DEALLOCATE _indexes END TRY BEGIN CATCH PRINT ERROR_MESSAGE() RETURN -1 END CATCH END |
The procedure can be modified so that should reorganize indexes with an specific maximum fragementation percentage. Now this SP, reorganize indexes who are fragmented with more then 5 %.
The execution of this SP should be as following.
|
1 2 3 4 5 |
DECLARE @return_value int EXEC @return_value = [dbo].[SP_Reorganize_Indexes] SELECT 'Return Value' = @return_value |
The output message should be something like this.
Enjoy it, and don’t forget to modify it as you need. 🙂