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?

up vote 4 down vote accepted

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.

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>
}

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.

  • 1
    Dave, some comments were moved to: chat.stackexchange.com/rooms/58039/… . Back then I said that I believe you misread the doc. I see no indication that you will be able to change FILLFACTOR, or most properties, while paused. You can only resume by issuing RESUME or reissuing the exact same REBUILD command, with same properties. Unless you have tested and proven otherwise, or seen it done, I highly doubt you will be able to change the FILLFACTOR while it is paused. And you don't want it paused for too long. – Solomon Rutzky Oct 10 '17 at 21:11
  • Thanks for clearing that up. My impression is not that you can change fill factor while it is paused, but that you can change fill factor with a rebuild command, and then pause. This would make it so that your index rebuild job could also issue RESUME, this giving you the ability to adjust fill factor during the work day, but not have it actually applied until the job kicks off at night. Why do you say you don't want them paused for long? – Dave Goldsmith Oct 19 '17 at 0:23
  • Dave, regarding not pausing for a long time, the documentation states: "Having a long pause for an index operation may impact the DML performance on a specific table as well as the database disk capacity since both indexes the original one and the newly created one require disk space and need to be updated during DML operations.". I am also guessing that certain operations, such as anything requiring a Schema-Lock, will be block, but I'm not sure about that as I haven't tested it. – Solomon Rutzky Oct 19 '17 at 2:36
  • Regarding the plan to start the rebuild with a different FILLFACTOR, I suppose that would work IF your index rebuild job knew to issue a RESUME rather than a REBUILD, but so far I am not sure how one would determine if an operation is paused. – Solomon Rutzky Oct 19 '17 at 2:47
  • Good point. I assumed that the resume command would resume all paused rebuild operations, which is not a great assumption in hind sight. Speaking to space consumption and impact on DML, as long as you pause it in the same batch that you start it, it seems possible that there would be no DML issues. – Dave Goldsmith Oct 20 '17 at 3:37

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.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.