patternsqlMinor
How does FILLFACTOR reduce page splits?
Viewed 0 times
splitsreducefillfactorpagedoeshow
Problem
The default FILLFACTOR is 100 and I have been told at times to reduce it to 80 or lower so that page splits will be reduced due to the empty space on each page.
I don't understand this--if a new index is created on a table with a default FILLFACTOR of 80, 20% space of each page is left empty. So assume a row needs to use 10% of a page, so when it's full it can hold 10 records. With a FILLFACTOR of 80, now it can only hold 8 records. So when you add the 9th record, will there be a page split? With 100% FILLFACTOR, a page split will only happen on the 11th record, so it seems that a lower fill factor makes it worse.
I don't understand this--if a new index is created on a table with a default FILLFACTOR of 80, 20% space of each page is left empty. So assume a row needs to use 10% of a page, so when it's full it can hold 10 records. With a FILLFACTOR of 80, now it can only hold 8 records. So when you add the 9th record, will there be a page split? With 100% FILLFACTOR, a page split will only happen on the 11th record, so it seems that a lower fill factor makes it worse.
Solution
The fill factor does not apply to new rows being added to a page. The fill factor only applies to how full a page is filled when the index is created, rebuilt, or reorganized.
Accordingly, if the index is rebuilt or reorganized on a scheduled basis, your index will be able to accommodate new rows with fewer page splits if the fill factor is set appropriately.
Accordingly, if the index is rebuilt or reorganized on a scheduled basis, your index will be able to accommodate new rows with fewer page splits if the fill factor is set appropriately.
Context
StackExchange Database Administrators Q#240981, answer score: 3
Revisions (0)
No revisions yet.