Rebuild or Reorganize indexes

Question

I want to reorganize or rebuild indexes. I know the sql to perform this action on all indexes in a table is

alter index all on table_name reorganize;

But I only want to rebuild or reorganize if fragmentation percentage on each index is between a certain range. Is this possible to do?

Answer 1

Here is a procedure what I wrote

CREATE PROCEDURE dbo.ixRebuild @fillfactor int = 100, @Force bit = 0, @Schema varchar(255) = NULL, @Table  varchar(255) = NULL, @PkOnly bit = 0
AS
/*
 * ixRebuild
 * Rebuild all indices in a database.
 * Indices with >30% fragmentation are rebuilt.
 * Indices with >6%  fragmentation are just reorganised.
 * 
 * The default fill factor is 100%.
 *
 * Required permissions are:
 * GRANT VIEW DATABASE STATE TO <user>
 * GRANT ALTER TO <user>
 * GRANT EXEC ON ixRebuild TO <user>
 * 
 * Created 17/9/08 by rwb.
 */
BEGIN
    DECLARE @db int
    DECLARE @tab varchar(256)
    DECLARE @ix int
    DECLARE @ixName varchar(256)
    DECLARE @frag float
    DECLARE @cmd varchar(1024)
    DECLARE @type int
    
    DECLARE c CURSOR FAST_FORWARD FOR
        SELECT DISTINCT s.database_id, s.index_id, i.name, 
            Convert(float, s.avg_fragmentation_in_percent),
            ss.name + '.' + t.name AS tableName,
            i.type
        FROM sys.dm_db_index_physical_stats(Db_Id(), NULL, NULL, NULL, NULL) s
            INNER JOIN sys.indexes i  ON s.object_id = i.object_id  AND s.index_id = i.index_id
            INNER JOIN sys.tables t ON i.object_id = t.object_id
            INNER JOIN sys.schemas ss ON t.schema_id = ss.schema_id
        WHERE (@Schema IS NULL OR ss.name = @Schema)
            AND (@Table IS NULL OR t.name = @Table)
            AND (@PkOnly = 0 OR i.is_primary_key = 1)
            AND (
                @Force = 1
                OR (
                    avg_fragmentation_in_percent > 6
                    AND page_count > 100 -- rebuilding small indices does nothing
                    AND i.name IS NOT NULL -- for tables with no PK
                )
            )
        -- DISTINCT because ys.dm_db_index_physical_stats
        -- contains a row for each part of a partitioned index on a partitioned table.
    OPEN c
    FETCH NEXT FROM c INTO @db, @ix, @ixName, @frag, @tab, @type
    WHILE @@Fetch_Status = 0
    BEGIN
        PRINT Db_Name( @db ) + ' / ' + @tab + ' / ' + @ixName + '   ' + Cast(@frag as varchar(16))
        SET @cmd = ''
        
        IF @frag < 10.0 AND @Force = 0
        BEGIN
            SET @cmd = 'ALTER INDEX ' + @ixName + ' ON ' + @tab + ' REORGANIZE'
        END
        ELSE
        BEGIN
            SET @cmd = 'ALTER INDEX ' + @ixName + ' ON ' + @tab + 
                CASE 
                    WHEN @type IN (1, 2) THEN ' REBUILD WITH (FILLFACTOR = ' + Cast(@fillfactor AS varchar(4)) + ')'
                    ELSE ''
                END
        END
        
        RAISERROR(@cmd, 0, 1) WITH NOWAIT;
        
        EXEC (@cmd)
        
        FETCH NEXT FROM c INTO @db, @ix, @ixName, @frag, @tab, @type
    END
    CLOSE c
    DEALLOCATE c

END

Answer 2

Generally rebuilding all indexes of a table is a bad idea, because some can have no fragmentation, while some other can be horribly bloated ! Rebuiliding index is a heavy process and is blocking the access of the table the time to do so, except in ONLINE mode available only with the Enterprise version.

So you need to rebuild only under certains circumstances... Authors (which I think due to my old age in RDBMS, I am a part of them...) agree to say that small tables don't care and a percentage of fragmentation under 30 does not matter. I alway add some complexity over these ideas, with another point : "big" rows of table or indexes (1600 bytes is a max for an index) will appear always to be fragmented, but are not... This because evaluating a frag percent take in account a percent of free space on a page and with a 8 Kb page, a natural no-recoverable space will stay inside the page (20% max for an index and 49 % max for a table).

So a good practice is to mix all those considerations to build a proper code to rebuild or defrag all objects includes in your database.

As an example, a short code to do so, can be :

DECLARE @SQL NVARCHAR(max) = N'';
SELECT @SQL = @SQL + CASE WHEN i.name IS NULL
               THEN N'ALTER TABLE [' + s.name + '].[' + o.name + '] REBUILD;'
            WHEN avg_fragmentation_in_percent > 30
               THEN N'ALTER INDEX [' + i.name + '] ON [' + s.name + '].[' + o.name + '] REBUILD;'
            ELSE N'ALTER INDEX [' + i.name + '] ON [' + s.name + '].[' + o.name + '] REORGANIZE;'
         END
FROM   sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS ips
         JOIN sys.objects AS o ON ips.object_id = o.object_id
         JOIN sys.schemas AS s ON o.schema_id = s.schema_id
         JOIN sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE  ips.page_count > 64 AND  avg_fragmentation_in_percent > 10;
EXEC (@SQL);