Best Way to Index Rebuild and Reorganize

Index rebuild is very essential part in database. Now a days OLTP database doing millions of transactions in system. TO keep performance keep of SQL query we need to daily schedule job to rebuild indexes.

I have gone through the internet, search many sites. Most of them suggested if Fragmentation is more than 30% we need rebuild the index.

And it is suggested if Fragmentation is between 5 and 30 then it is sufficient to perform Index Re-Organize.

Here i write the open procedure to handle both of case. It will automatically handle above situation and execute rebuild/re-organize of indexes accordingly

IF OBJECT_ID('IndexRebuildReOrg') IS NOT NULL 
        DROP TABLE IndexRebuildReOrg

BEGIN TRY
    SET NOCOUNT ON;
    DECLARE @v_obj_id INT;
    DECLARE @v_ind_id INT;
    DECLARE @partitioncount BIGINT;
    DECLARE @v_part_num BIGINT;
    DECLARE @v_frag FLOAT;
    DECLARE @v_page_cnt INT;
    DECLARE @Query NVARCHAR(4000);
    DECLARE @v_obj_name nvarchar(255);
    DECLARE @v_ind_name nvarchar(255);
    DECLARE @v_schema_name nvarchar(255);
    
-- Load all index Name and fragmentation Value in Table

    SELECT  a.object_id AS v_obj_id ,
            a.index_id AS v_ind_id ,
            partition_number AS v_part_num ,
            avg_fragmentation_in_percent AS v_frag ,
            page_count AS page_count,
            object_name (a.object_id) Obj_Name,
            i.name as Ind_name ,
            schema_name (schema_id) schema_name
    INTO    IndexRebuildReOrg
    FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL,'LIMITED') a
    join sys.indexes i on i.index_id = a.index_id and i.object_id = a.object_id
    join sys.objects  o on i.object_id = o.object_id
    WHERE   a.index_id > 0;

-- Cursor Handling, close if already open
IF CURSOR_STATUS('global', 'indRebuild') >= -1
BEGIN
 PRINT 'indRebuild CURSOR DELETED' ;
    CLOSE indRebuild
    DEALLOCATE indRebuild
END

-- Cursor for Index to be fragmentation/Open/Loop indRebuild cursor
    DECLARE indRebuild CURSOR LOCAL FOR SELECT * FROM IndexRebuildReOrg where v_frag > 5;
    OPEN indRebuild;
    WHILE ( 1 = 1 )
        BEGIN;
            FETCH NEXT FROM indRebuild INTO @v_obj_id, @v_ind_id, @v_part_num, @v_frag, @v_page_cnt, @v_obj_name, @v_ind_name, @v_schema_name;

            IF @@FETCH_STATUS < 0
                BREAK;

            If (@v_frag > 5 and @v_frag < 30)
            BEGIN
            SET @Query = N'ALTER INDEX ' + @v_ind_name + N' ON ' + @v_schema_name + N'.' + @v_obj_name + N' REORGANIZE';
            END
            
            If @v_frag > 30 
            BEGIN
            SET @Query = N'ALTER INDEX ' + @v_ind_name + N' ON ' + @v_schema_name + N'.' + @v_obj_name + N' REBUILD';

            IF @partitioncount > 1
                SET @Query = @Query + N' PARTITION=' + CAST(@v_part_num AS NVARCHAR(10));
            END
                EXEC (@Query);
                --print (@Query); 

            PRINT N'Rebuilding/Reorganizing index ' + @v_ind_name + ' on table ' + @v_obj_name;
            PRINT N'Fragmentation: ' + CAST(@v_frag AS VARCHAR(15));
            PRINT N'Page Count: ' + CAST(@v_page_cnt AS VARCHAR(15));
        END;

    CLOSE indRebuild;
    DEALLOCATE indRebuild;

    DROP TABLE IndexRebuildReOrg;

END TRY
BEGIN CATCH
    PRINT 'ERROR ENCOUNTERED:' + ERROR_MESSAGE()
END CATCH