Take the 2-minute tour ×

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?

share|improve this question
Do existing records get updated frequently with new start/end dates/times? Otherwise it makes no sense if you get that much fragmentation in just a day, with that many existing records and so few inserts... What's the fill factor of the index in question? –  KristoferA Nov 10 '09 at 9:54
+1 to KristoferA. That's not a large number of records a day - hitting 90% fragmentation each day seems excessive –  AdaTheDev Nov 10 '09 at 9:59
The fill factor is set at system default, its not ticked on the index properties. Many more inserts occur than updates on the datetime columns. The data in the datetime columns could be in any order it is not inserted sequentially. The table in question is the main table in the database and is read from constantly. –  Matt Nov 10 '09 at 10:11
AdaTheDev - I agree, we do not have this issue with any of our development or testing databases, some of which have similar levels of load. Are there any server wide settings i should check, as the production database is on a different server. –  Matt Nov 10 '09 at 10:13
During the office hours, are you doing an ALTER INDEX myIndex ON myTable REBUILD WITH(ONLINE = ON)? This will keep you table online while rebuilding the index. –  Dan S Nov 10 '09 at 13:07

3 Answers 3

up vote 3 down vote accepted

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.

share|improve this answer
I have set the fill factor to 90% and so far it seems to be helping. –  Matt Nov 12 '09 at 10:00


  • 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...

share|improve this answer

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.

share|improve this answer
The clustered index is on the primary key which is an int –  Matt Nov 10 '09 at 10:16

Your Answer


