Post reply

After Index Rebuild, the fragmentation rate is still very high

  • June 24, 2009 at 2:58 am

    #211197

    After having index rebuild, the indexes fragmentation is still very high, why ?

    I used Maintenance Plan and SQL Agent Job to do index rebuild and used the following SQL statement to find out the indexes fragmentation :

    SELECT TOP 10 a.index_id, name, avg_fragmentation_in_percent

    FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'), NULL, NULL, NULL) AS a

    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id AND (name IS NOT NULL)

    ORDER BY avg_fragmentation_in_percent DESC

    Any ideal ?

  • June 24, 2009 at 3:07 am

    #1014485

    Check the size of the table as well. For smaller tables, rebuiding may not remove fragmentation.

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • June 24, 2009 at 10:35 pm

    #1015079

    Joe is correct...

    Additionally, if the table is HEAP (no cluster index) then ur fragmentation not remove.

    The above statements is true put the cluster index and drop it then ur fragmentation removed.

    (As per MS a table must have cluster index)

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

Viewing 3 posts - 1 through 2 (of 2 total)

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