Fragementation at index_level 1 and beyond

  • Recently, I inherited a server where an existing maintenance process only looks at index_level 0 to determine if a reorg\reindex is required. The database has dozens of tables where index_level zero doesn't meet the threshold (10%) but index_level 1 does and its page_count > 1000 (see attached graphic).

    I've done some reading but haven't found a definitive answer if fragmenation at index_level 1 and beyond impacts performance.

    I'd appreciate any input or links on the topic.

  • Maybe. Not likely, but it is possible.

    Requires that the table get large enough range scans that the read-ahead has to drive the scan from two levels up the index (probably a full table scan) and that the upper levels of the index not be in memory.

    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

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

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