Home Forums SQL Server 2005 SQL Server 2005 Performance Tuning Percentage of Fragmentation increased after 6 hours RE: Percentage of Fragmentation increased after 6 hours

  • August 29, 2015 at 3:34 pm

    #1823228

    coolchaitu (8/29/2015)

    Good morning Gail Madam and everyone,

    I reduced the fill factor value, set it to 90 and then rebuilded the indexes. It worked for majority of indexes. However, for some indexes it did not work. Fragmentation for those indexes went up to 90% when i checked the next day. Shall i try setting fill factor to 80 or 70 and rebuild these indexes. Please advise.

    Gosh, I hope you didn't rebuild ALL indexes with a FILL FACTOR of 90. For indexs that have ever increasing values (on IDENTITY columns, for example), page splits on inserts or updates will be a fairly rare thing even with a FILL FACTOR of 100. What that means is that you're wasting 10% of the size of such tables.

    --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)