One of the various options we have when creating or rebuilding indexes in SQL Server is specifying a fillfactor. If we create or rebuild an index without specifying a fillfactor, then the default fillfactor is applied.

In some cases, using the default fillfactor may be fine, even ideal. In other cases it could be less than ideal, even terrible.

Let’s look at what fillfactor is, how it works, and how we can use it to enhance database performance.

What is Fillfactor?

Fillfactor, also written as “fill factor” or “fill-factor”, is a property that we can set on indexes in SQL Server. We can use it to fine tune index data storage and performance.

Fillfactor determines how full SQL Server will make each page when it creates or rebuilds an index. The fillfactor is expressed as a percentage, ranging from 0 to 100.

Actually, we can only specify a value between 1 and 100. The default value is 0, which is the same as 100 (the leaf-level pages are filled to capacity).

How Fillfactor Works

When we create or rebuild an index with a specific fillfactor, SQL Server will fill each page of the index up to that percentage, leaving the remaining space empty. For example, if we set a fillfactor of 90, each page will be 90% full, with 10% free space.

The empty space is reserved between the index rows rather than at the end of the index.

This free space can be important on some indexes, because it allows for future inserts or updates without immediately causing page splits. Page splits occur when there’s not enough room on a page to accommodate new data, forcing SQL Server to move some existing data to a new page. This process can be time-consuming and lead to index fragmentation.

The Impact of Different Fillfactor Values

• Fillfactor of 100 (or 0, which is equivalent to 100):
  • Pages are completely filled.
  • Maximizes storage efficiency.
  • Increases the likelihood of page splits for tables with frequent inserts or updates.
• Fillfactor between 1 and 99:
  • Leaves free space on each page.
  • Reduces the frequency of page splits.
  • May require more storage space.
• Default fillfactor:
  • If not specified, SQL Server uses the default fillfactor of 0 (based on the default fill factor configuration option, which can be changed).

When to Use Fillfactor

We should consider adjusting the fillfactor when:

• The table experiences frequent inserts or updates.
• We notice high levels of index fragmentation.
• We’re dealing with larger indexes where page splits could significantly impact performance.

How to Set Fillfactor

We can set the fillfactor when creating or rebuilding an index. Here’s an example:

CREATE NONCLUSTERED INDEX IX_CustomerLastName
ON Customers (LastName)
WITH (FILLFACTOR = 80);

In this example, we’re creating a nonclustered index on the LastName column of the Customers table with a fillfactor of 80%.

To rebuild an existing index with a new fillfactor:

ALTER INDEX IX_CustomerLastName
ON Customers
REBUILD WITH (FILLFACTOR = 85);

Change the Default Fillfactor

We can also change the server-wide default fillfactor to a value other than 0. This is an advanced configuration option, and so it’s something that you should only do after careful consideration. See How to Change SQL Server’s Default Fillfactor for an example of how to do this.

Monitoring and Adjusting Fillfactor

It’s important to monitor our indexes’ performance and fragmentation levels to determine if our chosen fillfactor is effective.

We can check the fillfactor of all indexes on a given table like this:

SELECT
    name,
    type_desc,
    fill_factor
FROM sys.indexes
WHERE OBJECT_NAME(object_id) = 'Customers';

Replacing Customers with the name of the table.

We can use Dynamic Management Views (DMVs) like sys.dm_db_index_physical_stats to check fragmentation levels:

SELECT 
    object_name(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
JOIN 
    sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE 
    ips.avg_fragmentation_in_percent > 30
ORDER BY ips.avg_fragmentation_in_percent DESC;

This query will show us indexes with over 30% fragmentation. This could provide a clue as to which indexes need to have their fillfactor adjusted.

If we consistently see high fragmentation on an index despite having set a fillfactor, we might need to adjust it or consider more frequent index maintenance.

Best Practices

• Don’t set fillfactor arbitrarily. Base it on the specific characteristics of your data and workload.
• Start with small adjustments (e.g., 90 or 95) and monitor the impact before making larger changes.
• Remember that a lower fillfactor means more disk space usage. Balance this against the performance benefits.
• Regularly monitor index fragmentation and performance to ensure your fillfactor settings remain optimal.

More About Indexes

Here are some more articles I’ve written about indexes:

• Introduction to Indexing in SQL
• What is a Composite Index?
• What is a Filtered Index?