index fragmentation and maintenance paln

Question

WHat is the best maintenance plan for keeping index unfragmented?
I have a rebuilding index plan for all user tables every weekend.. is it  a good plan?

Also, it seems index got frgmented quickly.. should i change fillfactor? how cn i sove this issue?
what number should i put?

Answer 1

Hello

Index maintenance is different for the different styles of data access and modification the table receives.  The fill factor of an index says how much space should a page (8KB) have free when it is reindexed/created.  If a fill factor is high (eg 100%) that means that every page in the index is filled up as much as possible (so the most whole records as possible can be placed on each page).  This saves space cause less pages are used, because of this when reading the data performance is increased because less pages need to be read.  This causes a problem though, if an insert needs to done on a high fill factor index something called a 'page split' can occur, this causes a performance degradation.  There is no space for the record to go on it's appropriate page so SQL needs to move some records around to get the inserted record on the appropriate page.

General rule of thumb: high reads + low updates = high fill factor, low reads + high updates = lower fill factor

If your indexes are often being fragmented you may want to evaluate the fill factor or rebuild indexes more often.

Hope this helps


Rob

Answer 2

It's also worth noting that a shrink database right after rebuilding indexes can fragment them quite severely - make sure your maintenance plan doesn't include a shrink operation!