How Fill Factor Can Affect Performance

Fill factor determines the percentage of space to leave open for new data added in an index. Changing the fill factor value from the default of 0 (no space left open) can help in rare cases with performance problems.

In other situations, it can make performance worse by wasting disk space and having a negative effect on queries.

Glass Half Full or Half Empty

Let’s take a look at an example using the StackOverflow 2013 database. In this example, we’ll be looking for users from Ohio. Let’s create an index with a fill factor of 50:

CREATE INDEX IX_DisplayName_Location 
ON Users (DisplayName, Location) 
WITH (FILLFACTOR = 50);

Once we have the index created, we can run the following query to find the index size:

SELECT i.[name] AS IndexName 
,SUM(s.[used_page_count]) * 8 AS SizeKB
FROM sys.dm_db_partition_stats AS s 
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] AND s.[index_id] = i.[index_id]
WHERE i.[name] = 'IX_DisplayName_Location'
GROUP BY i.[name];

Which gives us:

Keep in mind the index now compared to later on when we rebuild it.  Now let’s search for Ohio users:

SELECT DisplayName, Location 
FROM Users 
WHERE Location = 'Ohio';

Note the 24502 logical reads:

Let’s see how that number changes as we alter the index fill factor.  Let’s alter the index and change fill factor to 100:

ALTER INDEX IX_DisplayName_Location
ON Users 
REBUILD WITH (FILLFACTOR = 100);

Now if we run the script to check the index size we find that it’s about half the size:

Running the same select query against the Users table now has 12363 logical reads, about half as many to return the same data:

0 and 100 Are the Same? Almost…

By default, fill factor is set to 0 which Microsoft says is the equivalent of setting fill factor to 100. Don’t bother trying to alter your index to set fill factor to 0 though, as it’s not recognized as a valid percentage:

Not Always a Quick Fix

I would call this a bit of an extreme example since I’ve only come across fill factor being set as low as 75.  I would be surprised to see fill factor set as low as 50 but anything can happen.  Either way, this example demonstrates the effect it can have on performance. If you want to check the fill factor on your indexes, run the following:

SELECT name, fill_factor
FROM sys.indexes;

Be mindful of when and where you’re changing fill factor as the index will be locked while rebuilding. That may not be a quick process depending on index size.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s