• In general, in most cases, worry about the leaf level and no other. The higher levels of the index are generally not large enough for fragmentation to matter, and they're far more likely to be in memory where fragmentation doesn't matter.

    If you have specific cases (huge indexes) that are exceptions, treat them separately.

    SELECT object_id, avg_fragmentation_in_percent

    FROM MyTable

    WHERE avg_fragmentation_in_percent > 30 and page_count > 1000 and index_level = 0 and alloc_unit_type_desc = 'IN_ROW_DATA'

    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