A quick way to improve I/O performance is to set a good fill factor. This installment of the series on efficient data discusses fill factors and page splits, and how they affect physical I/O performance.

Fill it

As we discussed in a previous post, you may recall that data in a table is stored sorted by its clustered index. Just like a filing cabinet or a bookshelf, SQL Server stores data in “pages” (8 kB) and “extents” (8 pages, 64 kB). If you insert data between two existing rows in a page, there has to be space enough left in that page (in the “filing cabinet”) for the new row to fit. If there isn’t enough space, SQL Server moves data between pages in order to make space in an operation known as a “page split”.

A page split operation moves a lot of data, changes a few pointers and updates a lot of cross-references, so it it’s very expensive and can hold up other processes while it happens, which quickly degrades performance.

In indexes (both clustered and non-clustered), the amount of free space that is reserved in each page is defined by the “fill factor”. A fill factor of 80% means that for every 80 bytes, another 20 bytes are saved for future rows. So, the lower the fill factor, the larger your tables will grow and the more disk space they’ll take up, but on the other hand, you will have a much lower frequency of page splits.

• A high fill factor means better read performance, because the data is tightly packed in few pages.
• A low fill factor means better write performance, because you need to perform fewer page splits.

Estimating the ideal fill factor is a balance between storage – the “padding” is applied both on disk and in memory – and how many rows are inserted/moved in the index. The SQL Server default fill factor is 100%, unless you change it.

Considering this, if you populate a table in exactly the order that the index is defined on it (i.e. if your only ever adding rows to the end of the clustered index, not inserting anything between existing rows), you can benefit from a higher fill factor. If the table is just populated once, you may even want to set the fillfactor to 100%. Whenever I use temp tables that are populated only once, and where the index keys in the table aren’t changed, I always set the fill factor to 100%.

The T-SQL

Setting the fill factor is always done on indexes – clustered or non-clustered – and never on heaps (tables without clustered indexes). So in T-SQL, you use the WITH (FILLFACTOR=n) hint when creating a primary key or index:

--- Creating an index
CREATE INDEX IX_tbl
    ON dbo.tbl (a, b, c)
    WITH (FILLFACTOR=80);

--- Creating a primary key
ALTER TABLE dbo.tbl ADD CONSTRAINT PK_tbl
    PRIMARY KEY CLUSTERED (a, b, c)
    WITH (FILLFACTOR=80);

PAD_INDEX

By default, the fill factor is only applied to the leaf level of an index. If you want to set the same fill factor for the intermediate levels of an index, use PAD_INDEX=ON next to the FILLFACTOR hint.

Setting the server default fill factor

You may want to set a server-default fill factor, and then manually tune a few select tables. This can be done on the property page of the server in SQL Server Management Studio, or as an advanced option in sp_configure:

--- Enable the "advanced options"
EXECUTE sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
--- Set the new server-default fill factor:
EXECUTE sp_configure 'fill factor', 85;
GO
RECONFIGURE;
GO

You’ll need to restart the server for this change to take effect.

Next week, we’ll take a look at datatypes, and how they affect storage space and I/O performance!