when an index (not table) is being created, rebuilt or reorganized, data is moved around on data pages. If the FillFactor is less than 100%, space is reserved on each leaf page for future changes. this means that the index grows in size and fewer indexes can be in memory – an index scan will take longer, both of which can impact performance. BOL says a FF of 50% can cause db read performance to decrease by 2x, but the MS default is 100% or 0. Best for read only tables or NCIs with data that is not going to change.
After creation or a rebuild, the FF is not maintained if the page is written to. Only on another rebuild will the table be reset.
FF can be set database wide with sp_configure. You can view an individual index with sys.indexes. Change it with Alter index create index.
However if the tables are being updated with “random” insertions, many of the index pages will need to be updated, by hard page splits (create a new page, move 1/2 the data). This will be fixed by a rebuild later.
FF does not apply to NCI on a heap table.
If a table is updated with a CI sequentially increasing key, all the writing is at the end of the table. The index will not encur page splits on a rebuild (should it need any?) and the FF is not relevant.
Bad performance if there are index page splits (writes to logs and more IO to create and move data), but bad performance if we have too much FF due to I/O. If an index is frequently fragmented, we should consider adding ff on an index rebuild.
low update tables wtih 100 reads per write): FF 100%
high update table with writes>reads: FF 50-70%
others : FF 80-90% (from Brad McGehee). So he recommends something other than 100% (0) for many tables.