Index reorg/rebuild script

Index fragmentation may be causing more I/O than necessary for efficient query processing. Fragmentation occurs as a result of inserting items into the middle of the index instead of appending them to the end. Inserting items into an index causes the page where the items are being inserted to “split” into two pages. The resulting fragmentation can make it harder for SQL Server to find the rows required for a given query.

Erik Darling discussed when to consider rebuilding or reorganizing an index – take his advice – he knows what he’s talking about! For the record, doing a reorg or rebuild of data stored on a SAN, a RAID array, or an SSD disk is unlikely to have an appreciable effect on performance, but hey, you’ve been warned – rebuild away!

We’re not talking about this kind of rebuild, ok?!
https://flic.kr/p/oQ5ih – wireheadinc

If you have a lot of page splits, as is the case with using a non-sequential ID value for the index key, and you’re storing your data on a standard hard drive, you may see performance decrease inversely with index fragmentation. Having said that, you should be certain you need to de-fragment an index before automatically de-fragmenting every index using the script below.

If you do decide you need to rebuild indexes on an ongoing basis, and have determined that automating statistics updates isn’t cutting it, you might consider using this script. The script only updates row-store clustered, non-clustered, and XML indexes.

SET NOCOUNT ON;
USE master;
GO
IF EXISTS (
    SELECT 1 
    FROM sys.procedures p 
        INNER JOIN sys.schemas s ON p.schema_id = s.schema_id
    WHERE s.name = 'dbo' 
        AND p.name = 'alter_index_reorg_rebuild'
    )
BEGIN
    DROP PROCEDURE dbo.alter_index_reorg_rebuild;
END
GO
CREATE PROCEDURE dbo.alter_index_reorg_rebuild
(
    --The cutoff between doing a reorg and switching to rebuild (percentage)
    @reorg_fragmentation_limit DECIMAL(10,4) = 50.0
    --The minimum fragmentation required before we do a reorg (percentage)
    , @reorg_fragmentation_floor DECIMAL(10,4) = 25.0
    --The minimum size of an index that will be reorg'd 
    , @minimum_page_count INT = 1000
    --Provides an override to prevent ONLINE = ON index rebuild operations
    , @no_online_operations BIT = 0
    --Provides a mechanism to override the instance-wide "Fill Factor (%)" option
    , @fill_factor_default_override TINYINT = NULL
    --set to 1 to only PRINT the ALTER INDEX statements (used for debugging!)
    , @debug_only BIT = 0 
)
AS
BEGIN
    /*
        dbo.alter_index_reorg_rebuild
        By:                Max Vernon 
        Compatability:    SQL Server 2012+
        
        This procedure will automatically defragment user-defined clustered, non-clustered, and xml indexes
        that meet certain criteria, controlled by the parameters passed into the procedure.
    */
    SET NOCOUNT ON;

    DECLARE @ObjectName NVARCHAR(1000);
    DECLARE @IndexName SYSNAME;
    DECLARE @PadIndex NVARCHAR(50);
    DECLARE @FillFactor NVARCHAR(50);
    DECLARE @AllowPageLocks NVARCHAR(50);
    DECLARE @PageLocksCmdOn NVARCHAR(1000);
    DECLARE @PageLocksCmdOff NVARCHAR(1000);
    DECLARE @AllowRowLocks NVARCHAR(50);
    DECLARE @DataSpace NVARCHAR(1000);
    DECLARE @IgnoreDupeKey NVARCHAR(50);
    DECLARE @CompressOption NVARCHAR(50);
    DECLARE @PartitionNumber INT;
    DECLARE @NumberOfFragments INT;
    DECLARE @NumberOfPages INT;
    DECLARE @AvgFragmentSizeInPages DECIMAL(18,4);
    DECLARE @AvgFragmentationInPercent DECIMAL(10,4);
    DECLARE @OnlineOperations BIT;
    DECLARE @Rebuild BIT;
    DECLARE @Connector NVARCHAR(50);
    DECLARE @FillFactorDefault INT;
    DECLARE @IsUnique BIT;
    DECLARE @IsPrimary BIT;
    DECLARE @IndexType NVARCHAR(60);
    DECLARE @IsPartitioned BIT;
    DECLARE @IsClustered BIT;
    DECLARE @OffLineOnly BIT;
    DECLARE @msg NVARCHAR(MAX);

    --online index rebuild is only supported on "Enterprise" engines, which includes "Developer" and "Trial"
    SET @OnlineOperations = CASE WHEN CONVERT(INT, SERVERPROPERTY('EngineEdition')) = 3 THEN 1 ELSE 0 END;
    IF @no_online_operations = 1 SET @OnlineOperations = 0;

    --get the instance-wide default fill factor
    SELECT @FillFactorDefault = CONVERT(INT, c.value)
    FROM sys.configurations c
    WHERE c.name = 'fill factor (%)';
    IF @FillFactorDefault = 0 SET @FillFactorDefault = 100;
    IF @fill_factor_default_override IS NOT NULL SET @FillFactorDefault = @fill_factor_default_override;

    DECLARE @cmd NVARCHAR(MAX);

    --create a forward, local-only, static cursor to loop through the index objects 
    --   local indicates this is not visible server-wide
    --   forward_only indicates we don't need to be able to move the cursor backwards or to specific rows
    --   static makes a copy of the data in tempdb which is used as we page through rows
    DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
    FOR
    WITH OffLineOnlyIndexes AS /* these indexes can only be rebuilt offline (ie no ONLINE = ON parameter) */
    (
        SELECT o.object_id
            , i.index_id
        FROM sys.indexes i
            INNER JOIN sys.objects o ON i.object_id = o.object_id
            INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
            INNER JOIN sys.columns c ON 
                (i.type <> 1 AND ic.object_id = c.object_id AND ic.column_id = c.column_id) /* non-clustered indexes */
                OR (i.type = 1 AND o.object_id = c.object_id) /* clustered indexes, include all columns */
            INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id
        WHERE (
            ty.name = 'text'
            OR ty.name = 'ntext'
            OR ty.name = 'image'
            OR ty.name = 'FILESTREAM'
            )
        GROUP BY o.object_id
            , i.index_id
    )
    SELECT ObjectName = QUOTENAME(s.name) + '.' + QUOTENAME(o.name)
        , IndexName = QUOTENAME(i.name)
        , PadIndex = CASE WHEN i.is_padded = 1 THEN 'PAD_INDEX = ON' ELSE 'PAD_INDEX = OFF' END
        , [FillFactor] = 'FILLFACTOR = ' + CONVERT(VARCHAR(50), CASE WHEN i.fill_factor = 0 THEN @FillFactorDefault ELSE i.fill_factor END)
        , [AllowPageLocks] = 'ALLOW_PAGE_LOCKS = ' + CASE WHEN i.allow_page_locks = 1 THEN 'ON' ELSE 'OFF' END
        , [AllowRowLocks] = 'ALLOW_ROW_LOCKS = ' + CASE WHEN i.allow_row_locks = 1 THEN 'ON' ELSE 'OFF' END
        , [DataSpace] = 'ON ' + QUOTENAME(ds.name)
        , IgnoreDupeKey = 'IGNORE_DUP_KEY = ' + CASE WHEN i.ignore_dup_key = 1 THEN 'ON' ELSE 'OFF' END
        , CompressOption = 'DATA_COMPRESSION = ' + p.data_compression_desc
        , PartitionNumber = p.partition_number
        , NumberOfFragments = ips.fragment_count
        , NumberOfPages = ips.page_count
        , AvgFragmentSizeInPages = ips.avg_fragment_size_in_pages
        , AvgFragmentationInPercent = ips.avg_fragmentation_in_percent
        , IndexType = i.type_desc
        , IsUnique = i.is_unique
        , IsPrimary = i.is_primary_key
        , IsPartitioned = CASE WHEN ps.data_space_id IS NULL THEN 0 ELSE 1 END
        , IsClustered = CASE WHEN i.type = 1 THEN 1 ELSE 0 END
        , OffLineOnly = CASE WHEN COALESCE(ooi.object_id, 0) <> 0 THEN 1 ELSE 0 END
    FROM sys.indexes i WITH (NOLOCK)
        INNER JOIN sys.objects o WITH (NOLOCK) ON i.object_id = o.object_id
        INNER JOIN sys.schemas s WITH (NOLOCK) ON o.schema_id = s.schema_id
        INNER JOIN sys.data_spaces ds WITH (NOLOCK) ON i.data_space_id = ds.data_space_id
        INNER JOIN sys.partitions p WITH (NOLOCK) ON o.object_id = p.object_id
                                        AND i.index_id = p.index_id
        LEFT JOIN sys.partition_schemes ps WITH (NOLOCK) ON ds.data_space_id = ps.data_space_id
        LEFT JOIN OffLineOnlyIndexes ooi ON i.index_id = ooi.index_id AND o.object_id = ooi.object_id
    CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), o.object_id, i.index_id, p.partition_number, 'SAMPLED') ips
    WHERE o.is_ms_shipped = 0
        AND NOT (
            o.type = 'TF' -- table valued function
            OR o.type = 'TT' -- table type
            OR o.type = 'SO' -- sequence object
            )
        AND i.index_id > 0
        AND i.is_disabled = 0
        AND i.is_hypothetical = 0
        AND (
            ds.type = 'FG' -- filegroup
            OR ds.type = 'PS' -- partition stream
            ) 
        AND i.type_desc IN 
            ( --we only support rebuilding/reorganizing these index types:
                'CLUSTERED'
                , 'NONCLUSTERED'
                , 'XML'
            )
        AND ips.index_level = 0 -- leaf-levels only
        AND ips.page_count > @minimum_page_count
        AND ips.fragment_count > 1
        AND ips.avg_fragmentation_in_percent > @reorg_fragmentation_floor
    ORDER BY ips.fragment_count * ips.avg_fragmentation_in_percent DESC; --start with the most fragmented indexes first

    OPEN cur;

    FETCH NEXT FROM cur INTO @ObjectName
        , @IndexName
        , @PadIndex
        , @FillFactor
        , @AllowPageLocks
        , @AllowRowLocks
        , @DataSpace
        , @IgnoreDupeKey
        , @CompressOption
        , @PartitionNumber
        , @NumberOfFragments
        , @NumberOfPages
        , @AvgFragmentSizeInPages
        , @AvgFragmentationInPercent
        , @IndexType
        , @IsUnique
        , @IsPrimary
        , @IsPartitioned
        , @IsClustered
        , @OffLineOnly;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        --here we generate a dynamic T-SQL statement used to perform the actual reorg/rebuild
        SET @cmd = '';
        SET @cmd = @cmd + '/*
    ';
        SET @cmd = @cmd + 'NumberOfFragments: ' + CONVERT(VARCHAR(50), @NumberOfFragments) + '
    ';
        SET @cmd = @cmd + 'NumberOfPages: ' + CONVERT(VARCHAR(50), @NumberOfPages) + '
    ';
        SET @cmd = @cmd + 'AvgFragmentSizeInPages: ' + CONVERT(VARCHAR(50), @AvgFragmentSizeInPages) + '
    ';
        SET @cmd = @cmd + 'AvgFragmentationInPercent: ' + CONVERT(VARCHAR(50), @AvgFragmentationInPercent) + '
';
        SET @cmd = @cmd + '*/
';
        SET @cmd = @cmd + 'ALTER INDEX ' + @IndexName + ' ON ' + @ObjectName + '';

        IF (@AvgFragmentationInPercent >= @reorg_fragmentation_limit) OR (@AllowPageLocks = 'ALLOW_PAGE_LOCKS = OFF')
            SET @Rebuild = 1
        ELSE
            SET @Rebuild = 0;

        IF @Rebuild = 1 
            SET @cmd = @cmd + ' REBUILD'
        ELSE
            SET @cmd = @cmd + ' REORGANIZE';

        IF @IsPartitioned = 1
            SET @cmd = @cmd + '
PARTITION = ' + CONVERT(NVARCHAR(50), @PartitionNumber);

        IF @Rebuild = 1
        BEGIN
            SET @Connector = '
WITH (
      ';
            SET @cmd = @cmd + @Connector + 'SORT_IN_TEMPDB = ON';
                SET @Connector =  CHAR(13) + CHAR(10) + CHAR(9) + ', ';
            SET @cmd = @cmd + @Connector + 'MAXDOP = 1'; --limit the degree of parallelism to prevent the new index from being fragmented at the outset
                SET @Connector =  CHAR(13) + CHAR(10) + CHAR(9) + ', ';
            IF @OnlineOperations = 1 AND @IsPartitioned = 0 AND @OffLineOnly = 0 AND @AllowPageLocks LIKE '% ON' 
            BEGIN
                SET @cmd = @cmd + @Connector + 'ONLINE = ON';
                SET @Connector =  CHAR(13) + CHAR(10) + CHAR(9) + ', ';
            END
            IF @IsPartitioned = 0
            BEGIN
                SET @cmd = @cmd + @Connector + @PadIndex;
                SET @Connector =  CHAR(13) + CHAR(10) + CHAR(9) + ', ';
            END
            IF @IsPartitioned = 0
            BEGIN
                SET @cmd = @cmd + @Connector + @FillFactor;
                SET @Connector =  CHAR(13) + CHAR(10) + CHAR(9) + ', ';
            END
            IF @IsPartitioned = 0
            BEGIN
                SET @cmd = @cmd + @Connector + @AllowPageLocks;
                SET @Connector =  CHAR(13) + CHAR(10) + CHAR(9) + ', ';
            END
            IF @IsPartitioned = 0
            BEGIN
                SET @cmd = @cmd + @Connector + @AllowRowLocks;
                SET @Connector =  CHAR(13) + CHAR(10) + CHAR(9) + ', ';
            END
            IF @IsPrimary = 0 AND @IsUnique = 0 AND @IsPartitioned = 0
            BEGIN
                SET @cmd = @cmd + @Connector + @IgnoreDupeKey;
                SET @Connector =  CHAR(13) + CHAR(10) + CHAR(9) + ', ';
            END
            SET @cmd = @cmd + @Connector + @CompressOption;
            SET @Connector =  CHAR(13) + CHAR(10) + CHAR(9) + ', ';
            SET @cmd = @cmd + '
    )'
            /* SET @cmd = @cmd + CHAR(13) + CHAR(10) + @DataSpace; --data space cannot be specified in ALTER INDEX, must use CREATE INDEX ... DROP EXISTING */
        END

            IF @AllowPageLocks LIKE '% OFF' AND @Rebuild = 0
            BEGIN --temporarily alter the index to allow page locks
                SET @PageLocksCmdOn = 'ALTER INDEX ' + @IndexName + ' ON ' + @ObjectName + ' SET (ALLOW_PAGE_LOCKS = ON);';
                SET @PageLocksCmdOff = 'ALTER INDEX ' + @IndexName + ' ON ' + @ObjectName + ' SET (ALLOW_PAGE_LOCKS = OFF);';
                    SET @msg = '/* Temporarily setting ALLOW_PAGE_LOCKS ON to allow index reorg (' + @IndexName + ' ON ' + @ObjectName + '). */';
                    RAISERROR (@msg, 0, 1) WITH NOWAIT;
                SET @cmd = CHAR(9) + REPLACE(@cmd, CHAR(13) + CHAR(10), CHAR(13) + CHAR(10) + CHAR(9)); -- indent the rebuild command
                SET @cmd = 'BEGIN TRANSACTION
BEGIN TRY
    ' + @PageLocksCmdOn + CHAR(13) + CHAR(10) + @cmd + CHAR(13) + CHAR(10) + CHAR(9) + @PageLocksCmdOff + '
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
END CATCH


';
            END
            ELSE
            BEGIN
                SET @cmd = @cmd + ';

';
            END
        IF @debug_only = 1
        BEGIN
            PRINT @cmd
        END
        ELSE
        BEGIN
            SET @msg = CASE WHEN @Rebuild = 1 THEN 'REBUILD' ELSE 'REORG' END + ' ' + @ObjectName + '.' + @IndexName;
            RAISERROR (@msg, 0, 1) WITH NOWAIT;
            EXEC sp_executesql @cmd;
        END

        FETCH NEXT FROM cur INTO @ObjectName
            , @IndexName
            , @PadIndex
            , @FillFactor
            , @AllowPageLocks
            , @AllowRowLocks
            , @DataSpace
            , @IgnoreDupeKey
            , @CompressOption
            , @PartitionNumber
            , @NumberOfFragments
            , @NumberOfPages
            , @AvgFragmentSizeInPages
            , @AvgFragmentationInPercent
            , @IndexType
            , @IsUnique
            , @IsPrimary
            , @IsPartitioned
            , @IsClustered
            , @OffLineOnly;

    END
    CLOSE cur;
    DEALLOCATE cur;

END;
GO
--mark the stored proc as a "system" object so it can be called from the context of other databases.
EXEC sys.sp_MS_marksystemobject 'dbo.alter_index_reorg_rebuild'; 
GO