Post reply

Index drives me crazy

  • August 21, 2014 at 7:44 pm

    #289362

    Hi All ,

    I have 1 index that highly fragmented very quickly ... I have set from fill factor 90 until 40 now ( I decrease 5 point every time I test the rebuild and now at 40 but it still get fragmented easily ..even though it is not as quick as fill factor 90 or 80 )

    The profile of that index is NONCLUSTERED INDEX , datatype : Bigint , size : 8

    Fragmentation : 82.69 %

    Page count : 2802

    Fill Factor : 40

    Table storage : 816790 rows

    I check the statistic of that index and it is used often so I can't drop that index ...

    Any idea what to do ? Really appreciate for any response

    Thanks a lot

    Cheers

  • August 21, 2014 at 10:02 pm

    #1740065

    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)

  • August 22, 2014 at 12:18 am

    #1740087

    Jeff Moden (8/21/2014)

    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.

    Thanks for your response Jeff..

    I think I will rebuild more often on that index only ! it is non unique but is used in report query ..

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply