2008 Index Fragmentation Maintenance

  • Jerry - that is definitely interesting. Did not know that it was introduced sometime after SP1.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Grant, just wanted to let you know that I have used this particular script (or a slight variation of it) at 3 different clients/employers now, and it has GREATLY improved the performance of their SQL servers.

    Thanks for you wonderful contributions to the SQL Server community!

    Dustin Mueller
    @sqlcheesecake

  • dustin.mueller (9/3/2010)


    Grant, just wanted to let you know that I have used this particular script (or a slight variation of it) at 3 different clients/employers now, and it has GREATLY improved the performance of their SQL servers.

    Thanks for you wonderful contributions to the SQL Server community!

    Thank you! Glad it's useful.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Hi,

    Very nice script. I've been searching ages for this. I am not so comfortable with cursors, they tend to always crash for me.

    However,

    Do you really want to reorganize or rebuild indexes that has no pages?

    I my self only look for defragmentation when page_count are above 1000 for small databases and 500 for bigger ones.

    Your script is easily changed, as it is a very good script, to perform this.

    I have done this

    Added a variable ,@PctCount INT in DECLARE

    Added av Column and a Value in #Frag table (pCount INT having pCount = s.page_count + Added AND s.page_count > 500 in Where clause)

    Added ,@PctCount to the CURSOR AND FETCH

    Inserted IF @PctCount > 499 WITH a BEGIN and END

    -- CHOOSE DB TO CHECK FIRST (or master will be checked as default)

    SET NOCOUNT ON

    DECLARE @DBName NVARCHAR(255)

    ,@TableName NVARCHAR(255)

    ,@SchemaName NVARCHAR(255)

    ,@IndexName NVARCHAR(255)

    ,@PctFrag DECIMAL

    ,@PctCount INT

    DECLARE @Defrag NVARCHAR(MAX)

    CREATE TABLE #Frag

    (DBName NVARCHAR(255)

    ,TableName NVARCHAR(255)

    ,SchemaName NVARCHAR(255)

    ,IndexName NVARCHAR(255)

    ,AvgFragment DECIMAL

    ,pCount INT)

    EXEC sp_msforeachdb 'INSERT INTO #Frag (

    DBName,

    TableName,

    SchemaName,

    IndexName,

    AvgFragment,

    pCount

    ) SELECT ''?'' AS DBName

    ,t.Name AS TableName

    ,sc.Name AS SchemaName

    ,i.name AS IndexName

    ,s.avg_fragmentation_in_percent

    ,s.page_count

    --,s.*

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

    AND s.page_count > 500

    AND t.TYPE = ''U''

    ORDER BY TableName,IndexName'

    SELECT * FROM #Frag

    DECLARE cList CURSOR

    FOR SELECT * FROM #Frag

    OPEN cList

    FETCH NEXT FROM cList

    INTO @DBName, @TableName,@SchemaName,@IndexName,@PctFrag,@PctCount

    WHILE @@FETCH_STATUS = 0

    BEGIN

    IF @PctCount > 499

    BEGIN

    IF @PctFrag BETWEEN 10.0 AND 30.0

    BEGIN

    SET @Defrag = N'ALTER INDEX [' + @IndexName + '] ON [' + @DBName + '].[' + @SchemaName + '].[' + @TableName + '] REORGANIZE'

    EXEC sp_executesql @Defrag

    PRINT @Defrag

    PRINT ''

    END

    ELSE IF @PctFrag > 30.0

    BEGIN

    SET @Defrag = N'ALTER INDEX [' + @IndexName + '] ON [' + @DBName + '].[' + @SchemaName + '].[' + @TableName + '] REBUILD'

    EXEC sp_executesql @Defrag

    PRINT @Defrag

    PRINT ''

    END

    END

    FETCH NEXT FROM cList

    INTO @DBName, @TableName,@SchemaName,@IndexName,@PctFrag,@PctCount

    END

    CLOSE cList

    DEALLOCATE cList

    DROP TABLE #Frag

  • Not just zero pages. It should have a page limit so that the defrag is across an index that's greater than 1 extent or the defrag just won't work. I haven't touched this since it was published 5 years ago. I may have to update it at some point.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply