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

image

  • 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
GO
RECONFIGURE
GO
sp_configure 'fill factor', 90
GO
RECONFIGURE
GO

This can also be done in the SSMS GUI:

image

To Change fillfactor at index level:

USE MyDB;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80);
GO

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:

SELECT OBJECT_NAME(P.OBJECT_ID) AS 'Table'
     , I.name 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; 

image

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

image


This is a good starting point to consider fillfactor.

Follow

Get every new post delivered to your Inbox.

Join 129 other followers