• Hmm, this is a difficult one as there are already some very good scripts "out there".

    I'm sure others will jump in here. 1000 pages is generally the perceived point at which fragmentation has more effect. However, the magical number at which an indexes disk layout changes is 8 pages (or 1 extent). To save and reuse space an index with less than 8 pages would be allocated from mixed extents and this means the pages are not necessarily in an ordered chain as the pages could be in different extents. 8 pages or greater and the index is allocated to uniform extents. Check this link for more info on this.

    I have seen cases where constantly trying to rebuild an index does not reduce fragmentation. However, start messing with fill factors and the index will need to be rebuilt to achieve the specified free space in each page.

    Base your fill factors on the number of page splits that the index incurs as this is what you are trying to negate.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉