Post reply

Index Fragmentation

  • March 20, 2014 at 6:59 am

    #305253

    Hi Experts,

    For the below query i am getting four fragmentation levels why??

    SELECT

    avg_fragmentation_in_percent,page_count,record_count , *

    FROM sys.dm_db_index_physical_stats(

    db_id(), object_id('dbo.[abc]'), 116, NULL, 'DETAILED') AS s

    avg_fragmentation_percent Page_count Record_count

    0.0936071802213535908055445053

    15.519399249061379990805

    95.454545454545544799

    0144

  • March 20, 2014 at 7:05 am

    #1698669

    Because the index is 4 levels deep.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 20, 2014 at 7:17 am

    #1698672

    Thanks a lot Gail.

    And when we rebuild the index it will defrag all the level and reorg on the leaf level right ie the last one..

  • March 20, 2014 at 4:13 pm

    #1698902

    Rebuilding the index defragments the index, yes.

    "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 4 posts - 1 through 3 (of 3 total)

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