SQL Server – Changing Fill Factor on Specific Tables Without Index Rebuild

clustered-indexfill-factorindex-maintenancesql serversql-server-2008

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?

Best Answer

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:

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

Related Solutions

Sql-server – index maintenance strategy if few insertion points relative to # of rows

The answer by ThomasStringer is very good and the usage of partitioning here would likely help overall performance and decrease your maintenance cost - however, it won't do anything for your page split scenario.

Can you verify that your FK's are trusted? This won't decrease or impact your page splits but it will help your query execution time.

select 'ALTER TABLE ' + s.name + '.' + OBJECT_NAME(o.object_id) + ' WITH CHECK CHECK     CONSTRAINT ' + i.name + '
GO'
FROM sys.foreign_keys i  INNER JOIN sys.objects o ON i.parent_object_id = o.object_id     INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE     i.is_not_trusted = 1 AND i.is_not_for_replication = 0
-- Foreign Key Check... Will build the statement below to CHECK (validate) the FK for     those FK's that are untrusted.
UNION
select 'ALTER TABLE ' +  s.name + '.' + OBJECT_NAME(o.object_id) + ' WITH CHECK CHECK     CONSTRAINT ' + i.name + '
GO'
from sys.check_constraints i join sys.objects o on i.parent_object_id = o.object_id     join sys.schemas s on o.schema_id = s.schema_id where i.is_not_trusted =     1 and i.is_not_for_replication = 0

-- Check Constraint Check... Will build the statement below to CHECK (validate) the       check constraints for those check constraints that are untrusted.

-- !!!! -- The output (in TEXT) will look similar to what you see below.  once you     have the output, put it into the query window and execute it.

Another idea you already hit on, using fill factor - if you use a low fill factor and combine that with either row or page level compression, your data density, per page will increase. Again, this won't help your page split problem, BUT it can decrease the quantity of page splits (due to having a higher data density per page).

Lastly, if you can, look at changing your index and lead with the log_time column instead of the foreign key column. This change, depending on how the log_time data comes in (hopefully it's more "in order" than the FK column). This could decrease your page splits significantly. If you pair this with both compression (ROW) and a good partition scheme, you might see some significant improvements.

Sql-server – Fill Factor Setting and Performance

Have you considered updates? If you change a row and the new data does not fit in the same space the row would have to be moved to a new page. If you can rule this out, filling the pages to 100 percent will reduce the space the table uses and consequentially give you a slight performance improvement.

Best Answer

Related Solutions

Sql-server – index maintenance strategy if few insertion points relative to # of rows

Sql-server – Fill Factor Setting and Performance

Related Question