SQL Server Performance

Script to Rebuild indexes based on the average fragmentation percentage value -- SQL Server 2005

Discussion in 'Contribute Your Performance and Clustering Tips' started by Anoop, Aug 7, 2008.

  1. Anoop New Member

    I created this stored procedure to Rebuild indexes based on the average fragmentation percentage value. It works pretty well for me. I thought of sharing this with you all. You could limit the rebuild based on the page count as well if you like./*****************************************************************************************************************
    Description: This stored procedure rebuilds the indexes based on the average fragment percentage value from
    sys.dm_db_index_physical_stats dynamic view. It first captures all the indexes with average fragment
    percentage value >= 10 and then if the value is 30 or below it does the REORGANIZE and REBUILD for
    the indexes with value above 30. It also runs UPDATE STATISICS with REORGANIZE.
    Parameter: @db_name - name of the database to rebuild the indexes on.
    ********************************************************************************************************************/CREATE
    PROC [dbo].[usp_DB_Reindex](@db_name VARCHAR(100))AS
    SET NOCOUNT ON
    DECLARE
    @dbid VARCHAR(5)SELECT
    @dbid = database_idFROM
    sys.databasesWHERE
    name = @db_name--SELECT @dbidDECLARE
    @Query VARCHAR(5000)SET @Query = 'USE ' + @db_name + '
    SELECT o.object_id,
    i.index_id,
    LTRIM(RTRIM(s.name))+ ''.[''+LTRIM(RTRIM(o.name))+'']'',
    i.name,
    CASE WHEN (p.avg_fragmentation_in_percent <= 30)
    THEN ''REORGANIZE''
    ELSE ''REBUILD''
    END,
    CASE WHEN (p.avg_fragmentation_in_percent > 30)
    THEN ''WITH (ONLINE = ON)''
    ELSE ''''
    END,
    p.avg_fragmentation_in_percent
    FROM
    sys.dm_db_index_physical_stats ('+ @dbid+ ', NULL, NULL, NULL, NULL) p
    INNER JOIN
    sys.indexes i
    ON i.object_id = p.object_id AND i.index_id = p.index_id
    INNER JOIN
    sys.objects o
    ON o.object_id = p.object_id
    AND o.type = ''u''
    AND o.name not like ''sys%''
    AND o.name not like ''MS%''
    INNER JOIN
    sys.schemas s
    ON s.schema_id = o.schema_id
    AND i.name IS NOT NULL
    AND p.avg_fragmentation_in_percent >=10
    ORDER BY p.avg_fragmentation_in_percent DESC'CREATE
    TABLE #tmp_table (Counter
    INT IDENTITY(1,1),ObjectID
    INT,IndexID
    INT,Table_Name
    VARCHAR(200),Index_Name
    VARCHAR(200),Perform_Action
    VARCHAR(20),Options
    VARCHAR(50),Avg_frag_percent_before DECIMAL(16,13)
    )INSERT
    INTO #tmp_table(
    ObjectID, IndexID, Table_Name, Index_Name, Perform_Action,Options,Avg_frag_percent_before)EXEC
    (@Query)--If an index contains a colums with data type image, text or xml, rebuild the indexes offlineDECLARE
    @Query1 VARCHAR(5000)SET @Query1 = 'USE ' + @db_name + '
    UPDATE tt
    SET Options = ''''
    FROM #tmp_table tt
    INNER JOIN
    sys.columns c
    ON c.object_id = tt.ObjectID
    INNER JOIN
    sys.types t
    ON t.system_type_id = c.system_type_id
    AND t.user_type_id = c.user_type_id
    AND t.name in (''image'',''text'',''xml'')
    AND tt.Perform_Action = ''REBUILD'''EXEC (@Query1) DECLARE @max_counter INT
    SET
    @max_counter = (SELECT Max(Counter) FROM #tmp_table)DECLARE @counter INT
    SET
    @counter = 1DECLARE
    @String VARCHAR(2000)DECLARE @ObjectID INT
    DECLARE
    @IndexID INTDECLARE
    @Table_Name VARCHAR(200)DECLARE
    @Index_Name VARCHAR(200)DECLARE
    @Action VARCHAR(20)DECLARE @Options VARCHAR(50) WHILE
    @counter > 0 AND @counter <= @max_counterBEGIN
    SELECT @ObjectID = ObjectID, @IndexID = IndexID, @Action = Perform_Action, @Table_Name = Table_Name, @Index_Name = Index_Name, @Options = OptionsFROM #tmp_table WHERE Counter = @counter
    IF @Action = 'REBUILD' AND @Options <> ''
    BEGINSET @String = 'USE ' + @db_name + ' ALTER INDEX '+ @Index_Name + ' ON ' + @Table_Name + ' ' + @Action + ' ' + @Options
    EXEC(@String)
    END
    ELSE IF @Action = 'REBUILD' AND @Options = ''
    BEGINSET @String = 'USE ' + @db_name + ' ALTER INDEX '+ @Index_Name + ' ON ' + @Table_Name + ' ' + @Action
    EXEC(@String)
    END
    ELSE
    BEGINSET @String = 'USE ' + @db_name + ' ALTER INDEX '+ @Index_Name + ' ON ' + @Table_Name + ' ' + @Action
    EXEC(@String)
    EXEC('USE ' + @db_name + ' UPDATE STATISTICS ' + @table_name + ' ' + @index_name)
    END
    SET @counter = @counter + 1END
    DROP TABLE #tmp_table
  2. Luis Martin Moderator

    Thanks.
    Just to remove from unanwered questions.

Share This Page