>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
Advertisements

Posted on January 25, 2011, in Index, Performance Tuning, Sql Server 2008. Bookmark the permalink. 4 Comments.

  1. >Very good, but in script missed ")" im row (DB_ID)marlonrs.wordpress.com

  2. >Good work, thanx for sharing

  3. >@marlonrs thanks for pointing out

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: