What is the best fill factor for SQL indexes?

The fill factor is most relevant to index reconstruction. When creating a new index or recreating an existing index, you can specify a fill factor, which is the number of data pages filled in the index when the index is created. When the fill factor is set to 100, each index page is 100% full, and 50% means that each index page is 50% full.

If you create a clustered index with a fill factor of 100 (in a non-monotonic increasing column), it means that every time a record is inserted (or modified), the page split will occur, because there is no space for the data on the existing page. Many page splits reduce the performance of sqlserver.

For example, suppose you have just created an index with the default fill factor. When sqlserver creates it, it places the index on the adjacent physical page, because the data can be read sequentially, so there will be optimal I/O access. However, when a table is split with, increased, or changed, the page is split. When page splitting occurs, sqlserver must allocate a new page somewhere on the disk. These new pages and the original physical pages are not consecutive. Therefore, the access uses random I/O instead of sequential I/O, which slows down the access to the index page.

What is the ideal fill factor? It depends on Applications Program Read/write ratio of SQL Server tables. First, follow the instructions below:

Low-changed table (read/write ratio:): 100% fill factor

High-changed table (write exceeds read): fill factor of 50-70%

Half of read/write: 80-90% fill factor

Experiments were also required before finding the optimal fill factor for the application. Do not assume that the total ratio of A Low fill factor is better. A low fill factor reduces page splitting and increases the number of pages read during SQL Server queries, reducing performance. A low fill factor not only increases I/O overhead, but also affects cache. When the data page is moved from the disk to the cache, the entire page (including empty space) is moved to the cache. Therefore, the lower the fill factor, the more pages that have to be moved to the SQL Server cache, which means that there is less space for other important data pages to reside, thus reducing performance.

If you do not specify a fill factor, the default fill factor is 0, which means the fill factor of 100% (the leaf page of the index is 100% filled, but the middle page of the index has reserved space ).

As part of monitoring, You need to determine the fill factor when creating or recreating an index. In fact, except for read-only databases, the default value 0 is not suitable in all cases. Instead, you want a fill factor to keep the appropriate free space, as discussed above.