Tuesday, January 25, 2011

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

4 comments:

  1. Very good, but in script missed ")" im row (DB_ID)

    marlonrs.wordpress.com

    ReplyDelete
  2. Good work, thanx for sharing

    ReplyDelete
  3. There's this too: http://sqlfool.com/2010/04/index-defrag-script-v4-0/

    ReplyDelete

All suggestions are welcome