Blog Post

Rebuild Indexes of database with fragmentation more than 30%


Rebuild of indexes is the prime job of a DBA as heavily fragmented indexes can degrade query performance and cause your application to respond slowly. We should reorganize indexed with fragmentation between > 5% and < 30% and rebuild indexes if fragmentation more than 30%. To rebuild indexes we have a Rebuild indexes task in maintenance Plan.



If we see the T-SQL of this task, it will rebuild all indexes. As rebuilding all indexes would be nightmare for the server because as it take much resources. This is drawback of Rebuild Index Task.
To overcome this we write a custom script that only select indexes to rebuild that have Fragmentation more than 30%.
Sys.dm_db_index_physical_stats database management function return handy information about index fragmentation. We will use this function in script below. This script use table variable and does not require any cursor or temp table.

declare @frag_Temp as Table


    ID int identity(1,1),

       [objectid][int] NULL,

       [indexid][int] NULL,

       [partitionnum][int] NULL,

       [frag] [float] NULL


Declare @Count int

Declare @i tinyint=1

DECLARE @schemaname sysname;

DECLARE @objectname sysname;

DECLARE @indexname sysname;

DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @partitionnum bigint;

DECLARE @partitioncount bigint;

DECLARE @SQLCommand as Nvarchar(3000)

insert into @frag_Temp


    object_idAS objectid,

    index_id AS indexid,

    partition_numberAS partitionnum,

    avg_fragmentation_in_percentAS frag

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')

--Arguments 1(Database_ID,object_Id,Index_ID,partition,mode

WHERE avg_fragmentation_in_percent >=30.0 AND index_id> 0;

select @Count=Count(*) from @frag_Temp --Get Total Count



             select @objectid=objectid,@indexid=indexid,@partitionnum=partitionnum

                from @frag_Tempwhere ID=@i

              --Get tableName and its schema

                 select, from

                 sys.objects o

                 inner join  sys.schemas c on o.schema_ID=c.schema_ID

                 where o.object_id=@objectid

               --Get Index Name


                     whereindex_id=@indexid and object_id=@objectid

               --Get Partition Count
               select @partitioncount=count(*) from sys.partitions

                     where object_id=@objectid and index_id=@indexid


                SELECT @SQLCommand= 'Alter Index ' + @indexname + ' ON ' +                                          @schemaname + '.' + @objectname + ' REBUILD'  


                      SELECT @SQLCommand=@SQLCommand +  ' PARTITION='                                              convert(Char,@partitionnum);


               --Increment Count
               set @i=@i+1



You rated this post out of 5. Change rating




You rated this post out of 5. Change rating