Saturday, January 20, 2018

SQL Server - Prevent Page Splits

By Saleem Hakani on 9/21/2014

Say you have a glass filled with water and you try to put more water in that glass, what happens? Water in the glass will overflow. Exactly the same way, whenever a new row is added to a full index page, SQL Server moves around half of the rows to a new page to make room for the new row. This is known as PAGE SPLIT. Page splits can make room for new records but can be very resource intensive. Page-Splits can also incur fragmentation which may adversely affect I/O operations.


Preventing Page-Splits:

 In order to avoid PAGE-SPLITS, you must proactively determine the FILL FACTOR value. 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: Configuring a fill factor value of 60 means that 40 percent of each leaf-level page will be left empty providing space for index expansion as data is added to the underlying table.


The default fill factor value is always 0 which is mostly good for majority of situations. Fill factor of 0 means that the leaf level page is filled almost to capacity, but some space is left for at least one additional index row. (Note: fill factor of 0 and 100 are similar)


Fill factor value can be specified during CREATE INDEX or ALTER INDEX statement to set the fill factor value for individual indexes or you can directly configure this value at the server level so that any new indexes created will use the default value.


Configuring Fill Factor at the server level (Below example sets the fill factor value to 70 percent meaning you will have 30% of space for future expansion. You must carefully test any fill-factor value in the test environment based on your DML activity before implementing this option in the production environment)


Changing the Server Level Fill Factor Value to 70

Use Master;



Exec SP_Configure 'Show Advanced Options',1;



Exec SP_Configure 'Fill Factor', 70;



*SQL Server will need to be restarted for this setting to take effect


Configuring Fill-Factor @ Individual Index Level:

You can also configure fill-factor during index creation for individual indexes: 


Changing the Server Level Fill Factor Value to 70


--Create an Item Table

Create Table Item


Col_A Varchar(100),

Col_b Varchar(200)


--Creating an Index on Item Table with Fil-Factor Value of 70 (Configuring fill factor value of 70 for below Index means that 30 % of each leaf-level page will be left empty providing space for index expansion as data is added to the underlying table in the future).



ON Item (Col_A)

WITH (FillFactor = 70);



Find out Fill-Factor Value for Indexes


You can query “sys.Indexes” to query the Fill-Factor value for individual or all indexes in a database.


Example: Let’s find out the Fill-Factor Value of Item Table:


Select Object_Name(Object_ID) as 'Object_Name',

Name as Index_Name,


from Sys.Indexes

where Object_id=object_id('item') and

name is not null;



Things to keep in mind:

1.       SQL Server will need to be restarted when changing Fill-Factor value at the server level

2.       Make sure you perform thorough tests in the test environment before making any changes to the production environment.

Fill Factor
Page Fullness
Page Split
SQL Article Tags
Copyright [2014] by SQLCOMMUNITY.COM