May 20, 2011 1 Comment
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.
- 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.
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:
To Change fillfactor at index level:
USE MyDB; GO ALTER INDEX ALL ON Production.Product REBUILD WITH (FILLFACTOR = 80); GO
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;
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.