Sql server 2005 needs daily index defrag

Question

Our production database needs daily index defragmentation on one of its tables. The table has several indexes and one of them will get to 90% fragmentation each day.

The index is on two date fields ( a start time and an end time).

We do not get this problem on our development database although this obviously has a much lower throughput.

Each night we have a maintenance task scheduled to do a defrag but this sometimes needs to be run during office hours.

The table currently has 250,000 records and grows by about 500 to 3000 records a day.

Any ideas on why it is getting fragmented so quickly?

Answer 1

You can change the fill factor of the index to reduce fragmentation.

The default fill factor value is 0, which is basically treated as a fill factor of 100%, i.e. there is no extra space reserved for inserting new index records. This is ideal if you only add index records at the end of the index (like an identity field), but not if you insert records in the middle.

You can set the fill factor to 80% for example, that will reserve 20% free space in each index page for inserting new index records.

Answer 2

The number of new rows should not be able to generate that level of fragmentation, but really need some more information to not just be taking guesses, primarly what is the clustered index for the table.

Would also check whether you have a reindex followed by a shrink in your maintenance or auto_shrink set on since that can easily cause high levels of fragmentation.

Answer 3

Thoughts:

• When you rebuild indexes, do you reorganise or rebuild? Rebuild is better above 25% ish fragmentation

• If using DMO/sqlmaint, you have to say "10" for a fillfactor of 90

• Are you actually defragging all indexes eg ALTER INDEX ALL ON Mytable ...

• You can't get 90% fragmentation with that level of writes unless you are updating these columns over 90% of the rows...