August 21, 2014 at 7:44 pm
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
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
Change is inevitable... Change for the better is not.
August 22, 2014 at 12:18 am
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