0

At work we have a 250GB database with several very large tables. One of the maintenance plans that runs every 48 hours performs a reindex on all tables. This takes about four hours, and is currently under review.

One of the parameters that is up for discussion is the fill factor we apply to these indexes. When they are created and rebuilt they have FILLFACTOR=90 specified.

We have a 40GB table with 40GB indexes. With FILLFACTOR=90 I am led to believe that we will have 4GB of free space in the indexes, which seems like an awful lot considering the table doesn't grow by more than 100mb a day (so 200mb in 48 hours). Would FILLFACTOR=99 (400mb free index space) reduce INSERT performance?

Assuming the updates to the index are random (ie uniform distribution as specified at the end of the article) and a relatively small delta on the table data (eg 10,000 rows change per day out of 10 million) is there any benefit to FILLFACTOR=90 (4gb free index space), or should FILLFACTOR=99 (giving 400mb free index space) be used?

Improve this question

1 Answer 1

Reset to default
1

I would question the whole reindex to start with - given table size etc. it makes little sense every other day. Once per week / month - ok, but not every second day.

Fillfactor 99 will probably result in minor overead - even with being random, distribution will not be totally uniform (I.e. there will be random clusters). 90% may leave too much free space - 95 to 97 ma be better...

...but in the end it wshould not make a significant difference.

Suggestion: Try it out. Definitely think of reindexing less often.

Improve this answer
2
  • Yeah I'm aware that reindexing is overkill. We used sys.dm_db_index_physical_stats to see what the fragmentation was like after 24 hours and it was negligible except for highly trafficked tables (like a session table that essentially gets recreated every few hours).
    – ta.speot.is
    Jun 27, 2010 at 8:22
  • Thank you for the answer though.
    – ta.speot.is
    Jun 27, 2010 at 8:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .