Fill Factor

The fill factor option is provided for tuning index data storage and performance. The fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth. For example, specifying a fill-factor value of 80 means that 20 percent of each leaf-level page will be left empty, providing space for index expansion as data is added to the underlying table. The empty space is reserved between the index rows rather than at the end of the index. The fill-factor value is a percentage from 1 to 100, and the server-wide default is 0 which means that the leaf-level pages are filled to capacity. Fill-factor values 0 and 100 are the same in all respects.

The fill-factor setting applies only when the index is created, or rebuilt. The SQL Server Database Engine does not dynamically keep the specified percentage of empty space in the pages. Trying to maintain the extra space on the data pages would defeat the purpose of fill factor because the Database Engine would have to perform page splits to maintain the percentage of free space specified by the fill factor on each page as data is entered. Here are some information you should take into consideration when you modifying fill factor:

• Depending on how the data is inserted, updated and deleted in the table dictates how full the leaf level pages should be in the table. To fine tune this setting typically takes some testing and analysis. This could be critical for large active tables in your database.
• If data is always inserted at the end of the table, then the fill factor could be between 90 to 100 percent since the data will never be inserted into the middle of a page. UPDATE and DELETE statements may expand (UPDATE) or decrease (DELETE) the space needed for each leaf level page. This should be fine tuned based on testing.
• If the data can be inserted anywhere in the table then a fill factor of 60 to 80 percent could be appropriate based on the INSERT, UPDATE and DELETE activity. However, it is necessary to conduct some testing and analysis to determine the appropriate settings for your environment.
• With all things being equal i.e. table size, SQL Server versions, options, etc., the lower the fill factor percentage the more storage that could be needed as compared to a higher fill factor where the pages are more compact.
• Another aspect to take into consideration is your index rebuild schedule. If you cannot rebuild your indexes on a regular schedule and if you have a high level of INSERT, UPDATE and DELETE activity throughout the table, one consideration may be to have a lower fill factor to limit the fragmentation. The trade-off may be that more storage is needed.