Fill Factor
The fill factor option is provided for fine-tuning index data storage and performance. When an index is created or rebuilt, the fill factor value determines the percentage of space on each leaf level page to be filled with data, therefore reserving a percentage of 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 on each page rather than at the end of the page.
The fill factor value is a percentage from 1 to 100. The server-wide default of 0 is the optimal choice in the majority of situations. When fill factor is set to 0, the leaf level is filled to capacity.
Note
Fill factor values 0 and 100 are the same in all respects.
You can use the CREATE INDEX or ALTER INDEX statements to set the fill factor value for individual indexes. To modify the server-wide default value, use the sp_configure system stored procedure. To view the fill factor value of one or more indexes, use the sys.indexes catalog view.
Important
The fill factor setting applies only when the index is created, or rebuilt. The SQL Server 2005 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.
Performance Considerations
Page Splits
A correctly chosen fill factor value can reduce the potential for page splits by providing enough space for index expansion as data is added to the underlying table.
When a new row is added to a full index page, the Database Engine moves approximately half the rows to a new page to make room for the new row. This reorganization is known as a page split. A page split makes room for new records, but can take time to perform and is a resource intensive operation. Also, it can cause fragmentation that causes increased I/O operations. When frequent page splits occur, the index can be rebuilt by using a new or existing fill factor value to redistribute the data. For more information, see Reorganizing and Rebuilding Indexes.
Although a low fill factor value, other than 0, may reduce the requirement to split pages as the index grows, the index will require more storage space and can decrease read performance. Even for an application oriented for many insert and update operations, the number of database reads typically outnumber database writes by a factor of 5 to 10. Therefore, specifying a fill factor other than the default can decrease database read performance by an amount inversely proportional to the fill factor setting. For example, a fill factor value of 50 can cause database read performance to decrease by two times. Read performance is decreased because the index contains more pages, therefore increasing the disk IO operations required to retrieve the data.
Adding Data to the End of the Table
A nonzero fill factor can be good for performance if the new data is evenly distributed throughout the table. However, if all the data is added to the end of the table, the empty space will not be filled. For example, if the index key column is an IDENTITY column, the key for new rows is always increasing and the rows are logically added to the end of the table. In this case, page splits will not cause performance degradation. You should use the default fill factor of 0 or specify a fill factor of 100 so that the leaf level is filled up.
See Also
Concepts
Creating Indexes (Database Engine)
fill factor Option
Other Resources
ALTER INDEX (Transact-SQL)
CREATE INDEX (Transact-SQL)
sys.indexes (Transact-SQL)
sp_configure (Transact-SQL)