Quick Scan Report – Fill Factor

What this check looks for

Fill factor settings that are low enough to be wasting significant space. The check looks both at the instance level default from sys.configurations and at the fill factor recorded on individual indexes.

Why it matters

Fill factor is the percentage of each index page that gets filled when the index is built or rebuilt. A fill factor of 70 means 30 percent of every page in that index is deliberately left empty.

That empty space is not free. It is paid for on every single read, forever:

• Memory. A 10 GB index at fill factor 70 occupies about 14 GB of buffer pool for the same data. That is 4 GB of your memory holding nothing.
• I/O. Reading the same rows means reading more pages, so every scan and every range seek does more physical and logical reads.
• Backup size and backup time, because the empty space is backed up too.
• Restore time, for the same reason.
• Scan time, proportionally.

And the benefit it buys is narrower than people assume. Free space on a page avoids a page split when a row is inserted in the middle of the index or updated to a larger size. Page splits do cost: they generate log, they fragment the index, and they take a moment. But:

• An index on an ever increasing key does not split in the middle at all. An identity column or a datetime insert always appends at the end, so free space in the existing pages is never used. A low fill factor on those indexes is pure waste with no upside whatsoever.
• A read only or rarely modified table never splits, so the same applies.
• The free space is consumed quickly anyway. After a few weeks of inserts the pages have filled up and are splitting again, so the fill factor bought you a quiet period between rebuilds, not a permanent absence of splits.

Where the low values come from is almost always one of three places: a blanket recommendation applied to every index because it was easier than thinking about them individually; a maintenance script with a default; or somebody who changed the instance level default, which is the worst version because it applies to every index rebuilt afterwards across every database.

The instance default of 0 is the correct setting, and it means the same as 100: fill pages completely. If sys.configurations shows anything else, that is the first thing to fix.

How to confirm it yourself

The instance level default:

SELECT [name], [value], [value_in_use], [description]
  FROM sys.configurations WITH (NOLOCK)
 WHERE [name] = 'fill factor (%)';

0 and 100 both mean full. Anything else applies to every index rebuilt without an explicit fill factor.

Every index that has a non default fill factor, run in each database:

SELECT SCHEMA_NAME(o.[schema_id]) AS [schema_name],
       o.[name]                   AS [table_name],
       i.[name]                   AS [index_name],
       i.[type_desc],
       i.[fill_factor],
       SUM(p.[rows])              AS [rows],
       CAST(SUM(a.[total_pages]) * 8.0 / 1024 AS DECIMAL(12,1)) AS [size_mb]
  FROM sys.indexes           AS i WITH (NOLOCK)
 INNER JOIN sys.objects      AS o WITH (NOLOCK) ON o.[object_id] = i.[object_id]
 INNER JOIN sys.partitions   AS p WITH (NOLOCK) ON p.[object_id] = i.[object_id]
                                               AND p.[index_id]  = i.[index_id]
 INNER JOIN sys.allocation_units AS a WITH (NOLOCK) ON a.[container_id] = p.[partition_id]
 WHERE o.[is_ms_shipped] = 0
   AND i.[fill_factor] NOT IN (0, 100)
 GROUP BY o.[schema_id], o.[name], i.[name], i.[type_desc], i.[fill_factor]
 ORDER BY [size_mb] DESC;

Sort by size, because that column is the cost. A 200 MB index at fill factor 70 is wasting 60 MB and is not worth an outage; a 200 GB one is wasting 60 GB and is.

Whether the index actually splits, which is the whole question:

SELECT OBJECT_NAME(ios.[object_id]) AS [table_name],
       i.[name]                     AS [index_name],
       i.[fill_factor],
       ios.[leaf_insert_count],
       ios.[leaf_update_count],
       ios.[leaf_delete_count],
       ios.[leaf_allocation_count]  AS [page_splits_and_allocations]
  FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ios
 INNER JOIN sys.indexes AS i WITH (NOLOCK) ON i.[object_id] = ios.[object_id]
                                          AND i.[index_id]  = ios.[index_id]
 WHERE i.[fill_factor] NOT IN (0, 100)
 ORDER BY ios.[leaf_allocation_count] DESC;

A low fill factor on an index with almost no inserts or updates is unambiguous waste.

And the leading column, which tells you whether inserts land in the middle or at the end:

SELECT i.[name] AS [index_name],
       c.[name] AS [leading_column],
       t.[name] AS [data_type],
       c.[is_identity]
  FROM sys.indexes            AS i WITH (NOLOCK)
 INNER JOIN sys.index_columns AS ic WITH (NOLOCK) ON ic.[object_id] = i.[object_id]
                                                 AND ic.[index_id]  = i.[index_id]
                                                 AND ic.[key_ordinal] = 1
 INNER JOIN sys.columns       AS c WITH (NOLOCK) ON c.[object_id] = ic.[object_id]
                                                AND c.[column_id] = ic.[column_id]
 INNER JOIN sys.types         AS t WITH (NOLOCK) ON t.[user_type_id] = c.[user_type_id]
 WHERE i.[fill_factor] NOT IN (0, 100);

An identity or a datetime leading column means appends, which means the free space is never used.

How to fix it

Set the instance default back to 0, then raise fill factor on the indexes that do not need the free space.

The instance level, which needs no restart:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'fill factor (%)', 0;
RECONFIGURE;

That changes what future rebuilds default to; it does not change existing indexes.

Then the indexes themselves, which takes effect at the next rebuild:

ALTER INDEX [IX_Orders_OrderDate] ON [dbo].[Orders]
  REBUILD WITH (FILLFACTOR = 100, ONLINE = ON, SORT_IN_TEMPDB = ON);

Start with the largest indexes on append-only keys. That is where the space comes back with no downside at all.

Where a lower fill factor is genuinely justified:

• A random leading key, such as a non sequential GUID, where inserts land throughout the index. 90 is usually enough; 70 is rarely needed.
• A table with heavy updates that increase row size, for example a variable length column being filled in later.
• An index with a demonstrated page split problem, from the operational stats above.

And 99 rather than 100 is a reasonable default for an index that does split. It leaves a small amount of room at a cost of one percent rather than thirty, and it is the value this check recommends.

Do this incrementally and measure. Change the largest few, rebuild, and watch leaf_allocation_count and fragmentation over the following weeks. If splits increase materially on a particular index, that is the one that needed the space, and you can set it back individually.

Watch out for the maintenance script. Many index maintenance scripts apply a fill factor of their own on every rebuild, which will silently undo this. Check the script before changing anything, or the setting will revert on the next maintenance night.

How long it takes

About two hours to review and change the settings. The rebuilds themselves happen in the normal maintenance window.

Related reports

Related checks

Frequently asked questions

Is 100 percent fill factor really safe? For an index on an ever increasing key, yes, and it is the correct setting. Inserts append to the end and never split an existing page. For an index with random inserts, 99 or 90 is a sensible compromise.

What is the difference between 0 and 100? Nothing functionally. 0 is the default value meaning “fill pages”, and 100 means the same. The distinction only matters when reading configuration.

Will raising fill factor increase fragmentation? On an index that splits, somewhat, between rebuilds. On an append-only index, not at all. The operational stats query above tells you which you have before you change anything.

The setting reverted after our maintenance window. The maintenance script is applying its own fill factor on rebuild. Fix the script, not the index.