Preventing Page Splits with Optimal Fill Factor Settings

The SQL Server Fill Factor is a setting that can be applied to indexes, which determines the amount of space to leave empty within the index pages. This setting is crucial for managing how densely SQL Server stores the index data on each page. It’s primarily used to improve performance and reduce page splits during insert or update operations that modify the index. The Fill Factor is specified as a percentage value.

How Fill Factor Works

• 100% Fill Factor: SQL Server attempts to fill the pages completely. While this might be efficient for read-only or rarely modified data, it can lead to increased page splits for frequently updated data, as there’s no free space within the pages to accommodate new or modified records.
• Less than 100% Fill Factor: SQL Server leaves space on the index pages, according to the specified percentage. For example, a 70% fill factor would mean the pages are filled to 70% capacity, leaving 30% of the page empty for future growth. This can reduce the number of page splits because there’s room to insert new rows or expand existing rows without immediately requiring a new page.

The fill factor option is provided for fine-tuning index data storage and performance. When an index is created or rebuilt, the fill factor value determines the percentage of space on each leaf level page to be filled with data, therefore reserving a percentage of free space for future growth. For example, specifying a fill factor value of 80 means that 20 percent of each leaf-level page will be left empty, providing space for index expansion as data is added to the underlying table. The empty space is reserved between the index rows on each page rather than at the end of the page.

The fill factor value is a percentage from 1 to 100, and the server-wide default is 0 which means that the leaf-level pages are filled to capacity.

You can use the CREATE INDEX or ALTER INDEX statements to set the fill factor value for individual indexes. To modify the server-wide default value, use the sp_configure system stored procedure. To view the fill factor value of one or more indexes, use the sys.indexes catalog view.

Important:

The fill factor setting applies only when the index is created, or rebuilt. The SQL Server Database Engine does not dynamically keep the specified percentage of empty space in the pages. Trying to maintain the extra space on the data pages would defeat the purpose of fill factor because the Database Engine would have to perform page splits to maintain the percentage of free space specified by the fill factor on each page as data is entered.

A correctly chosen fill factor value can reduce potential page splits by providing enough space for index expansion as data is added to the underlying table.

When a new row is added to a full index page, the Database Engine moves approximately half the rows to a new page to make room for the new row. This reorganization is known as a page split. A page split makes room for new records, but can take time to perform and is a resource intensive operation. Also, it can cause fragmentation that causes increased I/O operations. When frequent page splits occur, the index can be rebuilt by using a new or existing fill factor value to redistribute the data. For more information, see Reorganizing and Rebuilding Indexes.

Although a low fill factor value, other than 0, may reduce the requirement to split pages as the index grows, the index will require more storage space and can decrease read performance. Even for an application oriented for many insert and update operations, the number of database reads typically outnumber database writes by a factor of 5 to 10. Therefore, specifying a fill factor other than the default can decrease database read performance by an amount inversely proportional to the fill factor setting. For example, a fill factor value of 50 can cause database read performance to decrease by two times. Read performance is decreased because the index contains more pages, therefore increasing the disk IO operations required to retrieve the data.

For the purposes of identifying the mid-page splits, we want to look at the operation column that is output by the event, which contains the specific operation being logged.  In the case of a mid-page split occurring, the operation will be a LOP_DELETE_SPLIT, which marks the delete of rows from a page as a result of the split.  To build our event session, we are going to need the map_key for the LOP_DELETE_SPLIT log_op map.  This can be obtained from the sys.dm_xe_map_values DMV:

SELECT *
FROM sys.dm_xe_map_values
WHERE name = ‘log_op’
  AND map_value = ‘LOP_DELETE_SPLIT’;

(should be 11)

Instead the best target for this type of information is the histogram target which will bucket our results based on how we configure the target and tell us how frequently the event fires based on our bucketing criteria.  If we don’t know anything about the server in question, we can start off with a very general event session that has a predicate on the operation only, and then aggregate the information in the histogram target based on the database_id to find the databases that have the most mid-page splits occurring in them in the instance.

Lets create the extended events

— If the Event Session exists, DROP it
IF EXISTS (SELECT 1
            FROM sys.server_event_sessions
            WHERE name = ‘TrackPageSplits’)
    DROP EVENT SESSION [TrackPageSplits] ON SERVER
— Create the Event Session to track LOP_DELETE_SPLIT transaction_log operations in the server
CREATE EVENT SESSION [TrackPageSplits]
ON    SERVER
ADD EVENT sqlserver.transaction_log(
    WHERE operation = 11  — LOP_DELETE_SPLIT
)
ADD TARGET package0.histogram(
    SET filtering_event_name = ‘sqlserver.transaction_log’,
        source_type = 0, — Event Column
        source = ‘database_id’);

GO

— Start the Event Session
ALTER EVENT SESSION [TrackPageSplits]
ON SERVER
STATE=START;

GO

This event session will allow you to track the worst splitting database on the server, and the event data can be parsed out of the histogram target. 

SELECT
    o.name AS table_name,
    i.name AS index_name,
    tab.split_count,
    i.fill_factor
FROM (    SELECT
            n.value(‘(value)[1]’, ‘bigint’) AS alloc_unit_id,
            n.value(‘(@count)[1]’, ‘bigint’) AS split_count
        FROM
        (SELECT CAST(target_data as XML) target_data
         FROM sys.dm_xe_sessions AS s
         JOIN sys.dm_xe_session_targets t
             ON s.address = t.event_session_address
         WHERE s.name = ‘TrackPageSplits’
          AND t.target_name = ‘histogram’ ) as tab
        CROSS APPLY target_data.nodes(‘HistogramTarget/Slot’) as q(n)
) AS tab
JOIN sys.allocation_units AS au
    ON tab.alloc_unit_id = au.allocation_unit_id
JOIN sys.partitions AS p
    ON au.container_id = p.partition_id
JOIN sys.indexes AS i
    ON p.object_id = i.object_id
        AND p.index_id = i.index_id
JOIN sys.objects AS o
    ON p.object_id = o.object_id
WHERE o.is_ms_shipped = 0;

Remember, while reducing page splits is beneficial, the goal is to balance between too many page splits (leading to fragmentation and performance degradation) and too few (potentially wasting space and not optimizing for read operations). Adjusting fill factors and monitoring the outcomes as described should help find a good balance for your specific workload.