I am a bit confused about page splits in MS SQL and I'm looking for a definitive answer. There seem to be two versions of the story:
1 - Fillfactor only affects how full pages are at the time the index is created/rebuilt. Page splits are always 50/50
2 - Fillfactor also affects how pages are split. So if there is 70% fillfactor and a page overflows, it will split 70/30
Thanks a lot
From Books Online (emphasis mine):
FILLFACTOR =fillfactor Applies to: SQL Server 2008 through SQL Server 2016, SQL Database V12.
Specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or rebuild. fillfactor must be an integer value from 1 to 100. If fillfactor is 100, the Database Engine creates indexes with leaf pages filled to capacity.
The FILLFACTOR setting applies only when the index is created or rebuilt. The Database Engine does not dynamically keep the specified percentage of empty space in the pages. To view the fill factor setting, use the sys.indexes catalog view.
Fill factor applies to the leaf pages. PAD_INDEX will determine what happens to non-leaf pages. From the same BoL page:
PAD_INDEX = { ON | OFF }
ON The percentage of free space that is specified by fillfactor is applied to the intermediate-level pages of the index.
OFF or fillfactor is not specified The intermediate-level pages are filled to near capacity
Page splitting can occur at all levels of the BTree, including the root level. Due to fan-out, however, the frequency at which intermediate pages split is (typically) an order of magnitude less than that at which leaves split. Also there are many fewer non-leaf pages than leaf pages. Consequently PAD_INDEX is less concerning than FILLFACTOR. Still worth thinking about, especially on randomly-inserted indexes.
During writes, pages will be filled up to 100%. Then they will split 50/50, or as close as possible to ensure each row is contained entirely on a single page. I believe the split is always 50/50. Subsequent writes may be skewed, however, so one of the new pages fills much quicker than the other.
