FillFactor of an Index – Slow down fragmentation

What is FillFactor?

The FILLFACTOR option for an index determines the percentage of free space that is reserved on each leaf level page of an index when it is created or rebuilt. This free space leaves room on the page for values to be added – and so is a must on volatile tables. Creating an appropriate fillfactor reduces the rate at which a page splits and therefore the rate at which a table fragments.

The server wide default FILLFACTOR is 0, which means that the leaf level pages are filled to capacity.

Index Structure


  • The Leaf level of a clustered index is the data itself, each leaf level entry being a row on the table.
  • For a non clustered index, it is the leaf level which contains one entry per row. The entry consists of the index key columns, optional included columns, and the bookmark/pointer.

Changing Fillfactor

To change fillfactor at the server level, so that all future indexes are created with the same fillfactor:

sp_configure 'show advanced options', 1
sp_configure 'fill factor', 90

This can also be done in the SSMS GUI:


To Change fillfactor at index level:

ALTER INDEX ALL ON Production.Product

PAD_INDEX option

This option specifies index padding. When turned ON, it uses the percentage specified by FILLFACTOR is applied to the intermediate-level and root level pages of an index.

Obviously this should only be considered if there are a lot of root and intermediate level pages:

     , AS 'Index'
     , P.index_id AS 'IndexID'
     , P.index_type_desc 
     , P.index_level  
     , P.page_count 
  FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('order_record'), 2, NULL, 'DETAILED') P
  JOIN sys.indexes I ON I.OBJECT_ID = P.OBJECT_ID 
                    AND I.index_id = P.index_id; 


Things to be aware of

Creating or altering a clustered index with a FILLFACTOR value affects the amount of storage space the data occupies, because the Database Engine redistributes the data when it creates the clustered index.

Identifying Page Splits

Ok – so you find that your database has no fillfactor and the default is set to 0 (or 100%). The next logical step is to find out if your database is suffering from page splits. you can either run Performance Monitor and check for “SQL Server:Access Methods\Page Splits/sec” but this wont return granular data as to which indexes are most affected or do some clever reading of the transaction log and look for ‘LOP_DELETE_SPLIT’ events and aggregate.

select Operation, AllocUnitName, COUNT(*) as NumberofIncidents
from   ::fn_dblog(null, null)
where Operation = N'LOP_DELETE_SPLIT'
group by Operation, AllocUnitName


This is a good starting point to consider fillfactor.


One Response to FillFactor of an Index – Slow down fragmentation

  1. Pingback: Intelligent Reindexing on Filer based volumes « Mohsin's DBA Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: