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:
1 2 3 4 5 | 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:
1 2 3 4 5 6 7 8 | 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:
1 2 3 4 5 | 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:
1 2 3 4 5 | 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:
1 2 3 4 | 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.
