• Personally, I think it's a waste to set a FILL FACTOR to anything less than 70.

    Some questions to ask are, is this index actually being used for anything? Is it a UNIQUE index? Is it being used by queries? If the answer to those all those questions is "NO", then you might just want to disable it for a while to see what happens and then, maybe, drop it.

    If it's either UNIQUE, you'll need to keep the index. If it's used a lot by queries, then also ask what kind of queries it's being used for. Are they "single row" or batch/report queries? If they're "single row" queries, fragmentation might be close to not even mattering.

    If the index is necessary and it supports batch/report queries, then see if the fragmentation is actually making a difference in performance. If not, stop worrying. If so, then you might have to setup a special job to REBUILD/REORGIZE that one index more often.

    Also, find out if the index is interfering with the performance of INSERTs. If it is and it's not UNIQUE, then it might still be a candidate for removal.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)