The most relevant to index rebuilds is the fill factor. When creating a new index, or rebuilding an existing index, you can specify a fill factor, which is the number of data pages in the index that are populated when the index is created. A fill factor of 100 means that each index page 100% fills up, and 50% means that each index page 50% fills up.
If you create a clustered index with a fill factor of 100 (on a non-monotonically incrementing column), it means that page splits occur whenever a record is inserted (or modified) because there is no space on the existing page for that data. Many page splits reduce the performance of SQL Server.
For example, suppose you have just created a new index with the default fill factor. When SQL Server creates it, it places the index on an adjacent physical page, because the data can be read sequentially so that there is optimal I/O access. But when the table with,, increases, and changes, a page split occurs. When a page split occurs, SQL Server must allocate a new page somewhere on the disk, and the new pages and the original physical pages are not contiguous. As a result, Access uses random I/O instead of sequential I/O, which makes accessing index pages slower.
So what is the ideal filling factor? It relies on the application's ratio of read and write to SQL Server tables. The first principle, follow the guidance below:
Table with low change (read-write ratio 100:1): 100% fill factor
Table with high change (write over Read): 50-70% fill factor
Read and write each of the half: 80-90% filling factor
Testing is also done before the optimal fill factor is found for the application. Do not assume that a low fill factor is better than the total ratio. A low fill factor reduces page splits, and it also increases the number of pages read during SQL Server queries, thereby reducing performance. Too low a fill factor not only increases I/O overhead, it also affects caching. When a data page is moved from disk to cache, the entire page (including empty space) is moved to the cache. So the lower the fill factor, the more pages that have to be moved to the SQL Server cache, which means that there is less room for other important data pages to reside in, thereby reducing performance.
If you do not specify a fill factor, the default fill factor is 0, which means that 100% of the fill factor (the index of leaf page 100% fills up, but the index of the middle page has reserved space).
As part of monitoring, you decide what the fill factor is when you create a new index or rebuild an index. In fact, in addition to the read-only database, all cases, the default value of 0 is not appropriate. Instead, you want a fill factor to keep the appropriate free space, as discussed above.
What is the best fill factor for SQL index and what is the fill factor?
