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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

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

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