Can Fill Factor be changed on specific tables without index rebuild?

Question

I've been digging around without any luck.

SQL Server 2008: Is it possible to adjust the fill factor on a specific clustered index/table without having to rebuild the entire table?

For example, if it has 400 million records, can we adjust the fill factor and have SQL Server use the new fill factor on all pages that haven't already exceeded that limit (as well as new splits), and then adjust pages that are over limit during index maintenance?

Is it possible to pre-define the fill-factor that an index should be built to during the next index maintenance?

Answer 1

This is not how FILLFACTOR works. It only applies to pages written during a CREATE or 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 100.

Or, stated another way: the rebuild operation is not your problem here as you will never get new and split pages to use a FILLFACTOR below 100.

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:

1. The 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").

2. The 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.

Answer 2

No it is not possible. FILLFACTOR is an option that can only be used with REBUILD or CREATE/DROP. Reference ALTER INDEX (Transact-SQL).

<rebuild_index_option > ::=
{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor 
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | ONLINE = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE } 
     [ ON PARTITIONS ( { <partition_number_expression> | <range> } 
     [ , ...n ] ) ]
}
<range> ::= 
<partition_number_expression> TO <partition_number_expression>
}

Answer 3

Is it possible to pre-define the fill-factor that an index should be built to during the next index maintenance?

This will be possible in MS SQL Server 2017. SQL Server 2017 is supposed to be coming with the ability to start and stop index rebuilds, which will also create the ability to execute a rebuild command with the newly specified fill factor, but then stop the rebuild, then using the new fill factor on the next scheduled index maintenance.

Answer 4

No. The moment you change the fill factor, it will rebuild the table. There is no selective pages that you can change the fill factor to.