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

up vote 9 down vote accepted

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.

  • Thank you for your answer. I guess there could be situations in real life where the split is nowhere near 50/50. One clarification please: does that really apply to the leaf pages only? I thought page splitting also concerns intermediate levels and fillfactor applies to them as well. – Mashchax Nov 16 '16 at 9:55
  • @Mashchax Page splits may not always be 50/50, they can be jagged. Fill factor only applies to leaf pages as Michael has said. – Sean Gallardy Nov 17 '16 at 22:13
  • Great explanation, particularly the clarification that it applies only on index creation / rebuilding; that was something easy to miss – Bruno Guardia Nov 18 '16 at 14:25
  • @SeanGallardy In what circumstances would the split not be even? Any external links you're able to share? – Michael Green Nov 18 '16 at 21:04
  • @MichaelGreen "It depends" :) and no, not publically documented. – Sean Gallardy Nov 19 '16 at 1:51

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.