If you've been working with SQL Server for a while, you probably have come across the term "fill factor" and wondered what exactly it is. Fill factor is the act of putting "holes" in the page when the data is written so that the page isn't packed full of data. Fill factor is the amount of space that SQL Server will fill on each leaf-level page during an index creation or rebuild process. The fill factor option allows you to fine tune the storage on index pages. By default, without using a fill factor, SQL Server will fill the leaf-level pages of an index with the maximum number of rows that can fit on the page—completely filling the whole page.

Tastes Great or Less Filling?

The issue with completely filling the leaf-level pages is that when you need to add additional rows to the page, you cannot add them. Full pages degrade performance when you are adding or updating data. SQL Server doesn't just add the data to the end of the index; it inserts the data into the right spot in the index, based on the ordering of the index keys. This is the basic definition of an index: data ordered for quick paging based on a predefined order that makes sense for the queries that are going to be run on the data.

When data needs to be inserted into a completely full page, SQL Server has to move some data around to create a "hole" for the data to be inserted into. It does this by creating a new index leaf page and then by putting the bottom half of the rows on the original page onto the new page. After SQL Server adds the new page and moves the data, SQL Server can finish the original insert statement and put the new rows of data onto the required leaf page.

All of this page splitting and data movement can cause large performance delays during inserts or updates. (An update statement may have to move a row of data from one page to another. Or, it may need to make a row larger and perform a page split to get the additional space needed for the row of data.) Along comes the fill factor.

If you set the fill factor to 75, only 75 percent of the page will be filled when it is created. If you set the fill factor to 25, only 25 percent of the page will be filled when it is created.

Issues

There are two issues with fill factor. One is that the lower the fill factor, the more pages it takes to create or rebuild the index on—in other words, you may have wasted hard disk space.

The other issue is that the fill factor is only set during the creation of the page and is not maintained over the life of the page. What does that mean? If we set the fill factor of an index to 75, the initial creation of the page will keep 25 percent of the page free. Over time, as we add new rows of data to the leaf levels, SQL Server will fill the page back to 100 percent. It is only during index maintenance that SQL Server will reset the page back to the desired 75 percent. This means that the longer the time between index defragmentation processing, the more the leaf-level pages can be filled and the more page splits we'll start seeing (which causes slowdowns in insert and update performance).

ENOVIA V6 creates the appropriate fill factors for your tables during installation. However, as the tables are filled with data, the indexes will have to be defragmented—sometimes on a weekly basis.

Now, on to the Queries

You can see the current fill factor settings for your indexes by running the following script. Remember that a setting of 0 is the same as a setting of 100.

USE AdventureWorks2008
GO

SELECT OBJECT_NAME(object_id) AS 'Table/View'
,name AS 'Index Name'
,index_id AS 'ID of Index'
,fill_factor AS 'Current fill factor setting' 
FROM sys.indexes

If you execute this script against the AdventureWorks2008 database, you will probably see a value of 0 for all indexes. This is because the default fill factor for SQL Server is 0. SQL Server wants to fill each row as much as possible.

You can change the fill factor for an index either when you create the index or when you rebuild the index.

USE AdventureWorks2008
GO

CREATE INDEX ncl_new ON person.address(City) WITH (fillfactor = 75)
GO

--Review the fill factor setting
SELECT OBJECT_NAME(object_id) AS 'Table/View'
,name AS 'Index Name'
,index_id AS 'ID of Index'
,fill_factor AS 'Current fill factor setting' 
FROM sys.indexes
WHERE name = 'ncl_new'
GO

USE AdventureWorks2008
GO

ALTER INDEX ncl_new ON person.address REBUILD WITH (fillfactor = 90)
GO

--Review the fill factor setting
SELECT OBJECT_NAME(object_id) AS 'Table/View'
,name AS 'Index Name'
,index_id AS 'ID of Index'
,fill_factor AS 'Current fill factor setting' 
FROM sys.indexes
WHERE name = 'ncl_new'
GO

To set a new default fill factor for the whole instance of SQL Server—and for all the indexes that you create in the future—you can use the sp_configure system stored procedure. You will have to turn on advanced options with sp_configure to change the default fill factor.

sp_configure 'show advanced',1
GO
reconfigure
go

sp_configure 'fill factor (%)',95
go

reconfigure
go

Now, when you create a new index or rebuild an existing index and you do not specify a fill factor, the creation or rebuild process will pick up the new default fill factor. It will not adjust existing indexes automatically. You will have to rebuild existing indexes in order for them to pick up the new fill factor.

In a follow-up post, we will discuss how to choose the right fill factor for your database.