The fillfactor controls how full the index leaf pages are when an index is created or rebuilt:

• Tables with static data should have high fillfactor
• Tables with volatile data should have low fillfactor. It leaves more room on index pages and allows new data to be inserted into the index pages which prevents page splits

The higher fillfactor the less space an index takes.

A value of 100 means the pages will be full and will take the least amount of storage space. This setting should be used only when there will be no changes to the data, for example, on a read-only table. A lower value leaves more empty space on the data pages, which reduces the need to split data pages as indexes grow but requires more storage space. This setting is more appropriate when there will be changes to the data in the table.

Change the default value of the fillfactor:

EXEC sp_configure   -- show current settings
EXEC sp_configure 'fill factor (%)', new_value  -- set a new default value
RECONFIGURE

Impact of a small fillfactor:

• leaves more free space on the leaf pages (i.e. causes an index to take more physical space)
• causes less page splitting to occur
• tends to slow database reads because more pages have to be read
• a very low fillfactor should be used only on tables where a lot of rows will be inserted