This is not how
FILLFACTOR works. It only applies to pages written during a
REBUILD operations. Pages are always filled as much as possible as they are created or split.
As per the MSDN documentation for CREATE INDEX:
The FILLFACTOR setting applies only when the index is created or rebuilt.
So, even if you change the
FILLFACTOR during a
REBUILD (since your Clustered Index already exists), only the existing pages will make use of that value. As new pages are created, and existing pages are split, they will be filled as if the
FILLFACTOR were set to
Or, stated another way: the rebuild operation is not your problem here as you will never get new and split pages to use a
Also: No, it is not possible to "pre-define" a change to the
FILLFACTOR value that will take affect the next time there is a
REBUILD. It can only be specified in the
WITH clause of a
REBUILD operation. BUT, once you have applied the new value during a rebuild, that new value will be stored in
sys.indexes and will be used again for subsequent rebuilds unless it is changed again by someone specifying
WITH (FILLFACTOR = x).
Or, again stated another way:
FILLFACTOR value can be changed only when actually doing a
REBUILD (I am not considering a
DROP and re-
CREATE of an Index to be a "change").
FILLFACTOR value is used only by
CREATE INDEX and
ALTER INDEX ... REBUILD (and technically also
DBCC DBREINDEX, but that shouldn't be used as it has been deprecated as of SQL Server 2005);
FILLFACTOR is not used as rows are inserted or updated as that would defeat the purpose of having this option in the first place.
FILLFACTOR reserves space on existing data pages so that the space can be used (hence reducing the chances of getting page splits); not allowing pages to fill beyond the
FILLFACTOR value would permanently reserve that space for no purpose, effectively reducing the size of the data pages, which would actually increase, not decrease, the chances of getting page splits.
Page split operations always move approximately 50% to the new page.