Post reply

Two questions about Index de/fragmentation

  • December 22, 2011 at 8:10 am

    #248709

    Hi there,

    I used the script on this site (Example D. Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes)

    http://msdn.microsoft.com/en-us/library/ms188917.aspx

    It defrags the indexes if their fragmentation is > 10%, if > 30% with REBUILD and < 30% with REORGANIZE.

    Now 2 questions pop up for me:

    1.

    I know more or less the difference between REORGANIZE/REBUILD: but what exactly happens after you "only" reorganize a heavily defragmented table?

    Is the value of avg_fragmentation_in_percent of sys.dm_db_index_physical_stats

    higher than after a rebuild?

    2.

    after I execute the script there are still indexes left with an avg_fragmentation_in_percent > 30%:

    SELECT

    (SELECT TOP 1 name FROM sys.objects a WHERE a.object_id = b.object_id),

    (SELECT TOP 1 name FROM sys.indexes a WHERE a.object_id = b.object_id and a.index_id = b.index_id),

    avg_fragmentation_in_percent,

    *

    FROM

    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') b

    -- 0=HEAP

    WHERE index_id > 0

    ORDER BY b.avg_fragmentation_in_percent DESC;

    Can someone tell me why?

    Thank you very much in advance,

    Kind Regards

    Arthur

  • December 22, 2011 at 8:13 am

    #1424815

    Small index can't be defraged.

    http://sqlfool.com/2011/06/index-defrag-script-v4-1

  • December 22, 2011 at 8:26 am

    #1424817

    Thanks a lot for your quick reply!

    I will copy the copied Microsoft statement about this small indexes from that link:

    "In general, fragmentation on small indexes is often not controllable. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding the index. For more information about mixed extents, see Understanding Pages and Extents."

    Sorry for no source checking, but I'm no journalist;)

  • December 22, 2011 at 8:51 am

    #1424834

    NP it's a common question so it must be hard-ish to find the answer.

    Have you intalled he script I sent you?

  • December 22, 2011 at 9:28 am

    #1424853

    I will have a close look at it,

    Thanks a lot!

Viewing 5 posts - 1 through 4 (of 4 total)

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