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?