SQL Server has the concept of fill factor for leaving extra space in pages to reduce the number of page splits. If you picture library shelves, it’s like leaving space for a couple new books on each shelf. It’s a trade-off though, putting in that space may reduce page splits but it increases the number of pages, which makes any scan of the index more expensive and uses more storage space too. It’s often mentioned as a way to reduce fragmentation which is the result of the page splits.
It’s useful on a table/index that has essentially random inserts (think new customers with the varying last names) or one that has a lot of updates that change the row size.
It’s not a good idea on insert only/never update tables – the typical logging table. All the activity is at the end of the table, all the other pages will never change. It’s wasted space to no gain at all. It’s an easy mistake if the default fill factor is set to less than 100%.
