One problem with any fill factor value is that it is linear and disruptive activity is often skewed.
This means you are inserting an I-O overhead into relatively stable portions of the key range in order to reduce the disruptive impact of insert activity in another portion of the key range.
One innovation I saw in a third-party tool for another RDBMS was the ability to have a higher level of free space where the index was most disrupted. Most RDBMS (SQL Server included) really do not care if free space is distributed in a linear fashion or concentrated in one place, they will use whatever is in the free space map.
This particular routine had parameters to give average free space in the index, and highest allowable concentration of free space within a single MB of data. When an index is rebuilt, the routine reading the old index pages would also review the level of fragmentation, and it would insert additional free space where the fragmentation was worst.
The end result was stable portions of the key range had very little free space, and therefore very efficient IO. Portions with a history of disruption would get more free space, allowing a greater number of inserts before fragmentation chains going outside the current extent would build up.
Non-linear free space would not suit all indexes, but for those that have a predictable pattern of skewed inserts it is a technique that can give useful extra performance.
Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017
: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara