Will Fill Factor solves index fragmentation in SQL Server?
Fragmentation refers to the empty spaces within the Index pages and the differences in the logical and physical order of the index pages. Please read my previous Post to understand Index Fragmentation and how it happens.
Fill factor is one of the recommendations to solve the Index fragmentation. I would like to share my experience in using the fill factor in the attempt of solving index fragmentation.
Fill factor is one of the index properties which we can define at the database level or at each index level. Basically, it is a percentage that tells SQL Server how much percentage of a leaf level page can be filled with data.
The database-level default value is 0 which means SQL Server can fill to the maximum of page size which is 8KB. Let's say if we set its value to 90% then SQL Server will only 90% of the page size of each page in the index leaf level.
Let's take the same example we used for the Index Fragmentation to understand the Fill factor. There is a phone directory table as given in Image 1. Ashlee Wilson newly moved to the city and her details have to be inserted on Page 1 between Arthi Rajan and Asley Bernick. But there is no space on Page 1, except the last page (Page 9) all the pages in the index are full. SQL Server will add a new page and move 50% of Page 1 to Page 10, this is called Page Splits. Ashlee Wilson will then be added to Page 1. This causes fragmentation on the phone directory index.
Similarly, when Arthi Rajan gets an additional phone SQL Server has to add another page to move 50% of Page 1 to Page 10 and accommodate the change in Page 1. This also causes Index Fragmentation.
Let’s change the fill factor to 75% for this index, the phone directory will now look like Image 2. All the pages are only 75% filled and the data is spread across 12 pages. Either Arthi Rajan gets an additional phone or Ashlee Wilson moves to the City, Page 1 has space and hence it is not required to split the page 1 data. This avoids fragmentation on the index.
Previously, to read all the data from this index SQL Server has to read 9 but with a 75% fill factor SQL Server has to read additional 3 pages which adds the IO cost of the Query. As well, it occupies more memory.
With a 100% fill factor, Page Splits happen when the unordered insert happens or data updates extend the existing row data, this degrades the performance of data inserts or updates. The external fragmentation might also degrade the read performance when the data is read from the disk.
On the other hand, the lesser fill factor adds the page space wastage and makes the internal fragmentation permanent. The total number of pages of an index increase due to the extra free space left on each page, which takes up more space in disk and memory. This affects the read performance when the data is read from disk and memory. The maintenance tasks like stats update, backup, and index rebuild \ index reorganize need to handle more pages, which increases the maintenance time.
Let's discuss few index types to see how much we should worry about fragmentation and whether we need to consider the fill factor as an option. When the clustered index key is an identity, the unordered inserts cannot happen and the external fragmentation won’t occur due to inserts. If the table didn’t have many variable-length columns, in other words, there is no much possibility for row expansion due to data updates, the fragmentation due to updates is also going to be minimal and this is not going to kill the read performance if we have a regular index maintenance strategies like index reorganize and rebuild.
When the clustered index key is a random value like GUID, the unordered inserts will happen which causes fragmentation when the pages are filled in full. If the table has many variable-length columns and data updates on the existing row are usual then it expands the row size which causes the page splits\index fragmentation, in this case, it is better to leave some space for future expansion.
When choosing the fill factor, we should be careful about the percentage. There is no harden rule for the right percentage. It depends on the index type and how much the data updates will cause fragmentation, so it should be determined with proper testing by adjusting between different percentages.
Let's conclude this post in this way, the fill factor will solve the index fragmentation to some extends but it has its list of side effects. The external Fragmentation degrades read performance only when the data is read from the disk. The additional free space in the index pages affects the performance when the data is read from disk or memory. If we try to answer “Will fill factor solve index fragmentation and improve performance?” the best answer could be, “It depends!”.
