• jasona.work (1/22/2013)


    Fill factor 0 = 100% full on each page (no free space) so adding ANY new rows to the table will cause a page split, correct?

    Not exactly. SQL Server can't realistically cram the pages 100% full because your records don't add up to exactly the page size. For example, say there's 8060 bytes available per page for data, but your average row size is 1300 bytes. That means around 6 rows per page, which is 7800 bytes. On any given page, you'll have some space to play with there for updates.

    Now for inserts, think about the first field in each index. Sometimes it's an identity field, and inserts will always happen at the end. Sometimes it's a sales date, and same thing there - inserts will hit at the end. However, sometimes you've got a phone book type index where it's based on last name, first name. As you add new customers, you're going to add them throughout the index. You could theoretically lower fill factor there, but by lowering it throughout the index, you're making the object size larger and using more memory to cache the same data. Make sure you're willing to sacrifice RAM in exchange for less page splits - often I see people monkeying with that number, and the cure is worse than the disease.