SQL Server – Script to Rebuild / Reorganize Indexes based on Fragmentation and No of Pages

Heavily fragmented indexes can degrade query performance and cause applications to respond slowly. Reorganizing or rebuilding of indexes will fix this issue. However, this is a very resource intensive process. Therefore identify and only rebuild the indexes that need to be rebuilt is important.

Following criteria can be used to determine which indexes to rebuild, which indexes to reorganize, and which indexes to leave alone.

• REBUILD index : if fragmentation is > 30% and number of pages > 1000
• REORGANIZE index : if fragmentation is > 10 % but < 30% and number of pages > 1000

We can use system function sys.dm_db_index_physical_stats to find out fragmentation information of indexes like:

• avg_fragmentation_in_percent: The percent of logical fragmentation (out-of-order pages in the index).
• fragment_count: The number of fragments (physically consecutive leaf pages) in the index.
• avg_fragment_size_in_pages: Average number of pages in one fragment in an index.

Following is a complete SQL script copied from http://www.sqlmusings.com/2009/03/15/a-more-effective-selective-index-rebuildreorganize-strategy/ for the above purpose. Setting “report_only = 1” of the script will only analyze the database without updating.

-- Ensure a USE <databasename> statement has been executed first.

SET NOCOUNT ON

-- adapted from "Rebuild or reorganize indexes (with configuration)" from MSDN Books Online

-- (http://msdn.microsoft.com/en-us/library/ms188917.aspx)

-- =======================================================

-- || Configuration variables:

-- || - 10 is an arbitrary decision point at which to reorganize indexes.

-- || - 30 is an arbitrary decision point at which to

-- || switch from reorganizing, to rebuilding.

-- || - 0 is the default fill factor. Set this to a

-- || a value from 1 to 99, if needed.

-- =======================================================

DECLARE @reorg_frag_thresh   float       SET @reorg_frag_thresh   = 10.0

DECLARE @rebuild_frag_thresh float       SET @rebuild_frag_thresh = 30.0

DECLARE @fill_factor         tinyint     SET @fill_factor         = 80

DECLARE @report_only         bit         SET @report_only         = 1

-- added (DS) : page_count_thresh is used to check how many pages the current table uses

DECLARE @page_count_thresh smallint     SET @page_count_thresh   = 1000

-- Variables required for processing.

DECLARE @objectid       int

DECLARE @indexid        int

DECLARE @partitioncount bigint

DECLARE @schemaname     nvarchar(130)

DECLARE @objectname     nvarchar(130)

DECLARE @indexname      nvarchar(130)

DECLARE @partitionnum   bigint

DECLARE @partitions     bigint

DECLARE @frag           float

DECLARE @page_count     int

DECLARE @command        nvarchar(4000)

DECLARE @intentions     nvarchar(4000)

DECLARE @table_var      TABLE(

                          objectid     int,

                          indexid      int,

                          partitionnum int,

                          frag         float,

                          page_count   int

                        )

-- Conditionally select tables and indexes from the

-- sys.dm_db_index_physical_stats function and

-- convert object and index IDs to names.

INSERT INTO

    @table_var

SELECT

    [object_id]                    AS objectid,

    [index_id]                     AS indexid,

    [partition_number]             AS partitionnum,

    [avg_fragmentation_in_percent] AS frag,

       [page_count]                         AS page_count

FROM

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

WHERE

    [avg_fragmentation_in_percent] > @reorg_frag_thresh

       AND

       page_count > @page_count_thresh

       AND

    index_id > 0

      

-- Declare the cursor for the list of partitions to be processed.

DECLARE partitions CURSOR FOR

    SELECT * FROM @table_var

-- Open the cursor.

OPEN partitions

-- Loop through the partitions.

WHILE (1=1) BEGIN

    FETCH NEXT

        FROM partitions

        INTO @objectid, @indexid, @partitionnum, @frag, @page_count

    IF @@FETCH_STATUS < 0 BREAK

    SELECT

        @objectname = QUOTENAME(o.[name]),

        @schemaname = QUOTENAME(s.[name])

    FROM

        sys.objects AS o WITH (NOLOCK)

        JOIN sys.schemas as s WITH (NOLOCK)

        ON s.[schema_id] = o.[schema_id]

    WHERE

        o.[object_id] = @objectid

    SELECT

        @indexname = QUOTENAME([name])

    FROM

        sys.indexes WITH (NOLOCK)

    WHERE

        [object_id] = @objectid AND

        [index_id] = @indexid

    SELECT

        @partitioncount = count (*)

    FROM

        sys.partitions WITH (NOLOCK)

    WHERE

        [object_id] = @objectid AND

        [index_id] = @indexid

    -- Build the required statement dynamically based on options and index stats.

    SET @intentions =

        @schemaname + N'.' +

        @objectname + N'.' +

        @indexname + N':' + CHAR(13) + CHAR(10)

    SET @intentions =

        REPLACE(SPACE(LEN(@intentions)), ' ', '=') + CHAR(13) + CHAR(10) +

        @intentions

    SET @intentions = @intentions +

        N' FRAGMENTATION: ' + CAST(@frag AS nvarchar) + N'%' + CHAR(13) + CHAR(10) +

        N' PAGE COUNT: '    + CAST(@page_count AS nvarchar) + CHAR(13) + CHAR(10)

    IF @frag < @rebuild_frag_thresh BEGIN

        SET @intentions = @intentions +

            N' OPERATION: REORGANIZE' + CHAR(13) + CHAR(10)

        SET @command =

            N'ALTER INDEX ' + @indexname +

            N' ON ' + @schemaname + N'.' + @objectname +

            N' REORGANIZE; ' +

            N' UPDATE STATISTICS ' + @schemaname + N'.' + @objectname +

            N' ' + @indexname + ';'

    END

    IF @frag >= @rebuild_frag_thresh BEGIN

        SET @intentions = @intentions +

            N' OPERATION: REBUILD' + CHAR(13) + CHAR(10)

        SET @command =

            N'ALTER INDEX ' + @indexname +

            N' ON ' + @schemaname + N'.' +     @objectname +

            N' REBUILD'

    END

    IF @partitioncount > 1 BEGIN

        SET @intentions = @intentions +

            N' PARTITION: ' + CAST(@partitionnum AS nvarchar(10)) + CHAR(13) + CHAR(10)

        SET @command = @command +

            N' PARTITION=' + CAST(@partitionnum AS nvarchar(10))

    END

    IF @frag >= @rebuild_frag_thresh AND @fill_factor > 0 AND @fill_factor < 100 BEGIN

        SET @intentions = @intentions +

            N' FILL FACTOR: ' + CAST(@fill_factor AS nvarchar) + CHAR(13) + CHAR(10)

        SET @command = @command +

            N' WITH (FILLFACTOR = ' + CAST(@fill_factor AS nvarchar) + ')'

    END

    -- Execute determined operation, or report intentions

    IF @report_only = 0 BEGIN

        SET @intentions = @intentions + N' EXECUTING: ' + @command

        PRINT @intentions     

        EXEC (@command)

    END ELSE BEGIN

        PRINT @intentions

    END

       PRINT @command

END

-- Close and deallocate the cursor.

CLOSE partitions

DEALLOCATE partitions

GO

Rebuilding an index can be executed online or offline. Reorganizing an index is always executed online: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes