• I do know from extensive testing, that reorging an index of less than 1000 pages is pointless, but rebuilding will show reduced fragmentation

    I'm curious.

    From the DMV, what's the value of avg_fragment_size_in_pages ?

    I think what we might have here is that the act of rebuilding the index has caused the mid-tier leaves to fragment... the mid-tier is the index of the leaf level pages (technically) so it makes sense that as the leaf level is "re-inserted" randomly that the mid-tier would get fragmented badly.

    If the leaf-level pages were written sequentially, it would be a different story.

    Just like Elisabeth Rédei noticed, You might also try rebuilding with pad_index in addition to fill factor, since pad_index is the "fill factor" the intermediate leaves... padding your index will reduce the number of page splits and thus extent fragmentation.

    ~BOT