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

SELECT

    object_id AS objectid,

    index_id AS indexid,

    partition_number AS partitionnum,

    avg_fragmentation_in_percent AS 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

       While(@i<=@Count)

          Begin

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

                from @frag_Temp where ID=@i

              --Get tableName and its schema

                 select @objectname=o.name,@schemaname=c.name from

                 sys.objects o

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

                 where o.object_id=@objectid

               --Get Index Name

                     select @indexname=name

                     from sys.indexes

                     where index_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'  

                      IF(@partitioncount>1)

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

                     

                   EXEC(@SQLCommand);

               --Increment Count

               set @i=@i+1

          End