If you use fillfactor (especially with a very low value), you may notice these effects on queries and maintenance activities:
More pages must be read for each query that performs a table scan or leaf-level scan on a nonclustered index.
In some cases, a level may be added to an index’s B-tree structure, since there will be more pages at the data level and possibly more pages at each index level.
Increased index size, reducing the index’s space efficiency. Because you cannot tune the fillfactor value at the page level, page splits with skewed data distribution occur frequently, even when there is available reserved space.
dbcc commands take more time because they must check more pages.
The time required to run dump database increases because more pages must be dumped. dump database copies all pages that store data, but does not dump pages that are not yet in use. Dumps and loads may also use more tapes.
Fillfactor values fade over time. If you use fillfactor to reduce the performance impact of page splits, monitor your system and recreate indexes when page splitting begins to hurt performance.
