Fill factor defines how much space on the page should be used to store data during (rowstore) index creation, index rebuilding or index reorganizing. It has traditionally been used to mitigate page splits since page splits introduce overhead. When a page splits half the contents is moved to a new page causing internal fragmentation (measured in page density) and external/logical fragmentation meaning that pages are stored “out of logical order”. Page splits are traditionally considered an “expensive” operation. The actual impact of page split overhead on performance is debatable with modern hardware. Default fill factor for MSSQL is 0, which is the same as 100%.
Once upon a time… there used to be a consensus that fill factor should be used to mitigate external fragmentation, the value 70% was often mentioned. Lets pause for a minute here, just to point out that this means that setting fill factor to less then 100% will introduce internal fragmentation, as it reduces page density. So in an attempt to decrease external fragmentation, fill factor introduces internal fragmentation.
As time has progressed things have changed, and many now believe that setting fill factor to anything other than 0 or 100 causes more problems than it solves. There are some differences of opinion in the community whether to leave this at the default 100% or adjust it. For instance, Brent Ozar now advice to leave it at the default value unless you can prove that you alleviate an issue by changing it. Paul Randal has a somewhat more traditional approach and advice to set it on indexes that suffers from fragmentation problems and then actively adjust it to find the correct setting per index.
If you monitor page splits, make sure they are proper page splits and not an addition of a new page at “the end” of the table as this is also labeled as page splits by SQL server. If you do not have known issues with page splits, I suggest leaving it at the default. In general, this is probably not the first knob to go tune on your server, and if you do, start on limited indexes and with conservative numbers. Some things to consider:
- Fill factor is only honored during index builds/rebuilds and reorgs.
- If the index is a clustered index and your sorting key is an increasing counter, all inserts will ALWAYS be on the last page. Hence, only updates to existing records will benefit from any free space on the page.
- All data pages on an index with fill factor set to anything other than 0 or 100 will have empty space.
- This means that more space is used on disk for storing empty space.
- Buffer pool space in RAM is filled by unused empty page space.
- More pages must be read from disk to memory to read the same information, increasing I/O (reads) and therefore the number of pageiolatches.
To monitor for the correct page splits in the proper manner please see Jonathan Kehayias blog, admittedly fairly old now, but still relevant as this principle has not changed. You will also be happy to know that setting up extended events has become easier as a more GUI friendly approach is now available in the last few SQL/SSMS versions. Go catch Grant Fritchey talking about this, for instance in this free youtube session. If extended events is something that is of furter interest to you have a look at Erik Darling’s sp_HumanEvents.
My thoughts on the matter is that simply having page splits and fragmentation does not mean there is a problem. Page splits do have overhead and can cause performance issues, but in most cases they do not. Assuming you have reasonable index maintenance (what I mean by reasonable is a topic for another post) there is a good chance page splits and fragmentation is not causing you issues.