• sqldba.jagan (12/2/2009)


    The tables in the database has varied page count. I think I have very few pages >=10 - <=25 and their frag ranges from 87.5 - 33.33.

    Don't even bother rebuilding indexes so small. Firstly, under 24 pages the index won't show much, if any change at all when rebuilt. This is due to the way SQL allocates pages in smaller indexes. The other point is that fragmentation is only a performance issue when doing large scans of an index. Emphasis large. The rough rule of thumb is that you should start worrying about fragmentation when an index reaches around 1000 pages as, at that point, the performance impact of the fragmentation may become noticeable.

    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