SQL Server: Simple Method to Resolve All Indexes Fragmentation

Recently a blog reader asked about a script or stored procedure which can resolve all indexes fragmentation as manually exploring each index of database for its level of fragmentation and then rebuilding or re-indexing it, according to its requirement, is bit painful task.

Here is a store procedure which I like to use for this purpose. I normally execute this stored procedure through an automated job during off peak hours.

CREATE PROCEDURE dbo.Proc_IndexDefragmentation

AS

    DECLARE @DBName NVARCHAR(255),

        @TableName NVARCHAR(255),

        @SchemaName NVARCHAR(255),

        @IndexName NVARCHAR(255),

        @PctFragmentation DECIMAL

    DECLARE @Defrag NVARCHAR(MAX)

    IF EXISTS ( SELECT  *

                FROM    sys.objects

                WHERE   OBJECT_ID = OBJECT_ID(N'#Frag') )

        DROP TABLE #Frag

    CREATE TABLE #Frag

        (

          DBName NVARCHAR(255),

          TableName NVARCHAR(255),

          SchemaName NVARCHAR(255),

          IndexName NVARCHAR(255),

          AvgFragment DECIMAL

        )

    EXEC sp_msforeachdb 'INSERT INTO #Frag(

                  DBName,

            TableName,

            SchemaName,

            IndexName,

            AvgFragment

            )

            SELECT ''?'' AS DBName

            ,t.Name AS TableName

            ,sc.Name AS SchemaName

            ,i.name AS IndexName

            ,s.avg_fragmentation_in_percent

            FROM ?.sys.dm_db_index_physical_stats(DB_ID(''?''),NULL,NULL,NULL,''Sampled'') AS s

            JOIN ?.sys.indexes i

            ON s.Object_Id = i.Object_Id

            AND s.Index_id = i.Index_id

            JOIN ?.sys.tables t

            ON i.Object_Id = t.Object_Id

            JOIN ?.sys.schemas sc

            ON t.schema_id = sc.Schema_Id

            WHERE s.avg_fragmentation_in_percent > 20

            AND t.TYPE = ''U''

            AND s.page_count > 8

            ORDER BY TableName,IndexName'

    DECLARE cList CURSOR

        FOR SELECT  *

            FROM    #Frag

    OPEN cList

    FETCH NEXT FROM cList INTO @DBName, @TableName, @SchemaName, @IndexName,

        @PctFragmentation

    WHILE @@FETCH_STATUS = 0

        BEGIN

            IF @PctFragmentation BETWEEN 20.0 AND 40.0

                BEGIN

                    SET @Defrag = N'ALTER INDEX ' + @IndexName + ' ON '

                        + @DBName + '.' + @SchemaName + '.[' + @TableName

                        + '] REORGANIZE'

                    EXEC sp_executesql @Defrag

                    PRINT 'Reorganize index: ' + @DBName + '.' + @SchemaName

                        + '.' + @TableName + '.' + @IndexName

                END

            ELSE

                IF @PctFragmentation > 40.0

                    BEGIN

                        SET @DeFrag = N'ALTER INDEX ' + @IndexName + ' ON '

                            + @DBName + '.' + @SchemaName + '.[' + @TableName

                            + '] REBUILD'

                        EXEC sp_executesql @Defrag

                        PRINT 'Rebuild index: ' + @DBName + '.' + @SchemaName

                            + '.' + @TableName + '.' + @IndexName

                    END

            FETCH NEXT FROM cList INTO @DBName, @TableName, @SchemaName,

                @IndexName, @PctFragmentation

        END

    CLOSE cList

    DEALLOCATE cList

    DROP TABLE #Frag

I think one more cleanly written, Tara Kizar stored procedure for this auto defregmentation process, can also help you. You can find it at Tara Kizar Blog