Fill Factor, Why Do You Taunt Me? Part 1

Like all the arbitrary numbers we hear in the SQL community such as the 30% fragmentation and 1000 pages. These come more as a best practice, not a rule. There are so many sites talking about this, it must be an interesting or highly misunderstood idea. I don't know how deep I can go, but over the next few weeks I plan on adding as much information to this as I can.

On highly updated tables, a lower fill factor number can be beneficial, but it's a dangerous game to play. If you have your pages at 70% capacity, that's 30% lost disk space, 30% lost memory space, and higher read costs when you retrieve data. SQL retrieves data one page at a time. That wasted space is also retrieved. It does help you update and insert data into those pages cleaner, but there's a point when it's not worth it. How often and how many updates do you do? Is your clustered index on an autoID? How big are your records for that table?

These all all questions you should be asking. There are many more as well. I would say as a rule of thumb, don't change the default fill factor. A server level fill factor impacts system databases, temp tables, and other databases besides the one you're working with.


My goals are to cover a few key points:

How do we find the fill factor of all our tables? (Or at least isolate those that are not on the default setting.)
What fill factor do we truly need? (if we need it at all)
What are some things to consider before working with our fill factor?

Hopefully I can cover this in more detail as well.

This will be updated some and referenced at the end. I'm adding in all things I've learned over the course of tracking down Fill Factors.

A page is 8192 Bytes.
The maximum column size is 8000.
The maximum record size is 8060.
The maximum record size for a table using sparse columns is 8018.
The maximum usable page size is 8096.
96 is reserved for headers.
Row_Overflow_Data -When you fill up a page and it needs to split a record across multiple pages.
Row_Overflow_Data uses a 24 Byte pointer.

Update 11/5/2013 10:43 CDT: Fill Factor only applies to the index's leaf pages.
Update 11/5/2013 13:46 CDT: User_Updates from dm_db_index_usage_stats does not differentiate bulk updates from single updates.

Knowing how the pages work greatly enhances how your fill factors work. There may be points that 94% will actually help just as much as 90%. This can save space and reduce read times.