(See this article for the original information; I’ve only reorganized the text to be easy for me to remember).
Fill factor: the amount of space to be used in a leaf page when the index is created.
“Fill factor” appears because of “page split” operations: a page is filled up and a row must be inserted in the page. When this happens, a new page is created, half of data from the original page is moved in this page and then the data for the new row is written to one of these pages. A page split translates to more I/O activity (allocate new page, move data, move pages in the B-Tree structure, write data), which means an insert operation will require a longer time than expected.
The fill factor design must balance the following rules:
- – the read performance of an index is directly proportional with the fill factor (this is because when fill factor is small a larger number of pages must be read from the disk)
- – the write performance of an index is indirectly proportional with the fill factor (this is because when fill factor is small, the probability of a page split occurrence is reduced).
Other factors to consider:
- – index will be mostly used for read or write operations?
- – new data is always allocated at the end of the index (like when using IDENTITY columns) or can be inserted everywhere?
Classic performance improvement example: a clustered index on an identity column will always add new data at the end of the index, this way avoiding costly page split operations. Insert operations will be faster on a table with such an index. Such tables can have a 100% fill factor without incurring performance degradation.
Performance monitoring: monitor page split operations. If a large number of page splits occurs, decrease the index fill factor.
Index fragmentation: if too many page splits have occurred, the index becomes fragmented and read and write operations will slow down. When such a situation occurs, the index must be rebuilt.
Additional (great) information can be found in this article