Index fill factor refers to the percentage of free space left on the page when an index is initially created. If this value is not specified when an index is created, it will be left at the default value of 100%. SQL Server will completely fill each page with index entries. While this approach saves on disk space and may help reduce page-read overhead, it can force new entries to the index to cause page splits. Page splits increase fragmentation and can significantly reduce I/O performance over time.
Experienced DBAs are encouraged to adjust this value as they see fit, usually on a table-by-table basis. For highly transactional tables, which experience large volumes of inserts, this value may be set lower (possibly in the 80-90% range). For more static tables, it can be left at a default of 100%. It is at the discretion of the DBA to do performance monitoring (i.e., monitoring page split and overall I/O counters) to determine whether the fill factor should be further adjusted.
For smaller databases, or those that are not managed by a designated DBA, the index fill factor can be left at its default value.