Database SQL Server-> index-> Fill Factor

Fill Factor

The fill factor option is provided to optimize index data storage and performance. When an index is created or re-generated, the fill factor value determines the percentage of data space to be filled on each leaf page, in this way, the remaining space on each page can be reserved as the available space for expanding the index in the future. For example, if the value of the specified fill factor is 80, 20% of the space on each leaf page is reserved as null, so that the index can be expanded as data is added to the base table. Reserve the available space between index rows rather than at the end of the index.

The fill factor value is the percentage between 1 and 100. The default value of the server range is 0, which indicates that the page is fully filled.

Note:
The fill factor values 0 and 100 are the same in all respects.

You can use the create index or alter index statement to set the fill factor values of each index. To modify the default server range value, use the sp_configure system stored procedure. To view the fill factor values of one or more indexes, use SYS. indexes.

Important:

The fill factor setting is applied only after the index is created or re-generated. The SQL Server database engine does not dynamically retain the specified percentage of available space on the page. If you try to keep extra space on the data page, it will be back to the intention of using the fill factor, because with the data input, the database engine will have to split pages on each page, to keep the percentage of available space specified by the fill factor.

Performance Considerations

Page splitting

Correct Selection of fill factor values can provide enough space to expand the index as data is added to the base table, thus reducing the possibility of page splitting. If a new row is added to the full index page, the database engine moves about half of the rows to the new page to free up space for the new row. This reorganization is called page splitting. Page splitting can free up space for new records, but it may take some time to split pages. This operation consumes a lot of resources. In addition, it may cause fragmentation, resulting in an increase in I/O operations. If page splitting occurs frequently, you can use new or existing fill factor values to re-generate the index and resend the data. For more information, see reorganizing and re-indexing.

Although a small non-zero fill factor value can reduce the need to split pages as the index grows, the index requires more storage space and reduces the reading performance. Even for many insert and update operations, the database reads more than 5 to 10 times the number of database writes. Therefore, specifying a fill factor different from the default value reduces the read performance of the database, and the decrease is inversely proportional to the value set by the fill factor. For example, when the fill factor value is 50, the read performance of the database will be doubled. The lower reading performance is because the index contains many pages, so the disk I/O operations required to retrieve data are added.

Add data to the end of the table

If the new data is evenly distributed in the table, non-zero fill factors of 0 or 100 are advantageous for performance. However, if all data is added to the end of the table, the available space on the index page is not filled. For example, if the index key column is the identity column, the key of the new row will always increase, and the index row will be added to the end of the index logically. If you want to update an existing row with the data of the extended row size, use a fill factor less than 100. The extra bytes on each page will help minimize the page Split Caused by the extra length in the row.