What is fill factor?

The 'fill factor' option describes how full SQL Server will make each index page. When there is no free space to insert a new row on the index page, the SQL Server will create a new index page and transfer few rows from the previous page to the new one. This operation is known as page splits. You can decrease the number of page splits by setting the appropriate fill factor option to reserve free space on every index page. The fill factor ranges its value from 1 through 100 that specifies the percentage of the index page to be left empty. The default value for the fill factor is 0. It is treated similarly to a fill factor value of 100, the difference in that SQL Server leaves some space inside the upper level of the index tree for FILLFACTOR = 0. The fill factor percentage is used only at the time when the index is created. If the table contains only the read-only data (or data that very rarely changed), you can set the 'fill factor' option to 100. When the table's data is changed very often, you can reduce the fill factor to 70% or whatever you think is best.