locked
Reorganizing/Rebuilding Index Results in more Fragmentation? RRS feed

  • Question

  • I have been reworking my index maintenance jobs from my old SQL 2000 table and view references to the DMV's and System Tables in SQL 2005, and I noted that some of my indexes end up being more fragmented after a reorganization and or rebuild.  That doesn't make much sense to me at all.  The code I am executing is:

     

    Code Block

    print ' '

    print '************* Beginning Index Updates for '+db_name()+' *************'

    print ' '

     

    DECLARE    @tablename varchar(250),

    @indexname varchar(250),

    @fragpcnt  decimal(18,1),

    @indexid   int,

    @dbID      int

     

    -- Determine DB ID

    SELECT @dbID = DB_ID()

     

    DECLARE tnames_cursor CURSOR FOR

     

    SELECT b.name, c.name, a.avg_fragmentation_in_percent, a.index_id

    FROM sys.dm_db_index_physical_stats (@dbID, NULL, NULL, NULL, NULL) a

    JOIN sys.indexes b ON a.object_id = b.object_id

    AND a.index_id = b.index_id

    JOIN Sys.objects c ON b.object_id = c.object_id

    WHERE a.index_id > 0

    ORDER by a.page_count DESC

     

    OPEN tnames_cursor

    FETCH NEXT FROM tnames_cursor INTO @indexname, @tablename, @fragpcnt, @indexid

    WHILE (@@fetch_status = 0)

    BEGIN

    -- Declare and determine the tablename ID

    declare @tablenameID int

    select @tablenameID = object_id(@tablename)

     

    IF @fragpcnt > 30

    BEGIN

    EXEC('ALTER INDEX ['+@indexname+'] ON ['+@tablename+'] REBUILD')

    PRINT '***************************************************'

    PRINT 'Index '+@indexname+' was rebuilt.'

    PRINT 'Original framentation Percent: ' + convert(varchar, @fragpcnt) + '%'

     

    SELECT @fragpcnt = avg_fragmentation_in_percent

    FROM sys.dm_db_index_physical_stats (@dbID, @tablenameID, @indexid, NULL, NULL) a

    JOIN sys.indexes b ON a.object_id = b.object_id

    AND a.index_id = b.index_id

    JOIN Sys.objects c ON b.object_id = c.object_id

     

    PRINT 'Post Rebuild fragmentation Percent: ' + convert(varchar, @fragpcnt) + '%'

    PRINT ''

    END

    ELSE IF @fragpcnt BETWEEN 5 AND 30

    BEGIN

    EXEC('ALTER INDEX ['+@indexname+'] ON ['+@tablename+'] REORGANIZE')

    PRINT '***************************************************'

    PRINT 'Index '+@indexname+' was Reorganized.'

    PRINT 'Original framentation Percent: ' + convert(varchar, @fragpcnt) + '%'

     

    SELECT @fragpcnt = avg_fragmentation_in_percent

    FROM sys.dm_db_index_physical_stats (@dbID, @tablenameID, @indexid, NULL, NULL) a

    JOIN sys.indexes b ON a.object_id = b.object_id

    AND a.index_id = b.index_id

    JOIN Sys.objects c ON b.object_id = c.object_id

     

    PRINT 'Post Reorganization fragmentation Percent: ' + convert(varchar, @fragpcnt) + '%'

    PRINT ''

    END

    ELSE

    BEGIN

    PRINT '***************************************************'

    PRINT 'Index '+@indexname+' was left alone.'

    PRINT 'Original framentation Percent: ' + convert(varchar, @fragpcnt) + '%'

    PRINT ''

    END

    FETCH NEXT FROM tnames_cursor INTO @indexname, @tablename, @fragpcnt, @indexid

    END

    print ' '

    print '************* NO MORE TABLES TO INDEX *************'

    PRINT 'All indexes for the '+db_name()+' database have been updated.'

    print ' '

    DEALLOCATE tnames_cursor

     

     

     

    Below are some snipits of the output:

     

     

    ***************************************************
    Index _dta_index_wuci_history_8_1123587141__K2_K5 was rebuilt.
    Original framentation Percent: 58.3%
    Post Rebuild fragmentation Percent: 58.3%
     
    ***************************************************
    Index PK__batchjob__776C5C84 was left alone.
    Original framentation Percent: 0.0%
     
    ***************************************************
    Index PK__ContactWebDetail__116A8EFB was rebuilt.
    Original framentation Percent: 44.4%
    Post Rebuild fragmentation Percent: 77.8%
     
    ***************************************************
    Index PK__managed_object_s__5DCAEF64 was left alone.
    Original framentation Percent: 0.0%
     
    ***************************************************
    Index kb_IX_kb_scope_scope_role was rebuilt.
    Original framentation Percent: 75.0%
    Post Rebuild fragmentation Percent: 87.5%
     
    ***************************************************
    Index PK__query__09A971A2 was left alone.
    Original framentation Percent: 0.0%
     
    ***************************************************
    Index PK__email_message__38996AB5 was rebuilt.
    Original framentation Percent: 85.7%
    Post Rebuild fragmentation Percent: 0.0%
     
    ***************************************************

     

     

    If the index begins with PK, then it is the primary key index which is generally the clustered index on the table, but not always.  If it has an IX on it, it is generally a non-clustered index on the table, but again not always.  In the case of the above, the PK is a clustered index, and the IX is a non-clustered index.

     

    Anyone have any ideas why this is functioning in this manner?

     

    Thanks,

     

    Jon

    Tuesday, December 11, 2007 3:49 PM

All replies