Fill Factor (%)

When we update an existing row, we need space for the new, wider data. If the page is completely full, SQL Server will split the page, putting some of the data on one page and the rest on another page. The tree pages had to be updated too. In the past, this could have been a performance problem.

(Spoiler alert: it wasn’t.)

Fill factor lets us leave empty space on every page for future updates.

This instantly makes your:

Database larger
Memory smaller (because there’s
less real data per page)
Queries take longer
Backups & DBCCs take longer
Index rebuilds take longer

How Can I tell What the FillFactor Is In Detail?

Our sp_BlitzIndex® script will report back on individual indexes. For a more brute-force attempt:

SELECT * 
FROM sys.indexes 
WHERE fill_factor < 80 AND fill_factor <> 0
AND is_disabled = 0 AND is_hypothetical = 0;

SELECT *

FROM sys.indexes

WHERE fill_factor < 80 AND fill_factor <> 0

AND is_disabled = 0 AND is_hypothetical = 0;

I Don’t Remember Setting This. When Did It Happen?

It could have been a long time ago, and it may even have been set by accident. Once fill factor is changed on an index, SQL Server will maintain that setting when you run index maintenance unless you specifically change the fill factor again.

HOW DO I FIX THE PROBLEM?

Stick with the default fill factor (100%). Only change fill factor at an index-by-index basis, and only when you can prove that page splits are a performance bottleneck for you.

How to Change Fill Factor Settings For An Index

Fill factor settings don’t take effect immediately; they have to be triggered by an index rebuild. This will take the index offline unless you have Enterprise Edition and specify that the rebuild be done online.

Here’s a sample script that rebuilds an index online (Enterprise Edition only!), setting it to fill factor = 100%:

ALTER INDEX ix_ProductName ON dbo.Product
REBUILD WITH (FILLFACTOR = 100, ONLINE = ON);
GO

ALTER INDEX ix_ProductName ON dbo.Product

REBUILD WITH (FILLFACTOR = 100, ONLINE = ON);

If you don’t have Enterprise Edition, you’ll need to leave off the ONLINE = ON part – but also, you’re going to be locking the table while you do it. Check the size of the index first, and maybe try it in development to understand how long it’ll take, how much log file space it’ll require, etc.

To learn more about fill factor, read 5 Things About Fill Factor.