Avoid Page Splits in SQL Server

Let’s 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 Server Level Fill-factor Value

Use Master
Go
 
Exec SP_Configure 'Show Advanced Options',1
Reconfigure
Go
 
Exec SP_Configure 'Fill Factor', 70
Reco

Above server level change will require you to restart SQL server for the setting to take effect.

Configuring Fill-Factor @ Individual Index Level

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

--Create an Item Table
Create Table Item
(
   Col_A Varchar(100),
   Col_b Varchar(200)
)
Go
 
--Creating an Index on Item Table with Fill-Factor Value of 70 
CREATE UNIQUE INDEX SH_Index
  ON Item(Col_A)
  WITH (FillFactor = 70)
Go

Note: Configuring fill factor value of 70 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.

Let’s now query sys.indexes to check the FILL-FACTOR value of ITEM table.

Select 
   Object_Name(Object_ID)as 'Object_Name',
   Name as Index_Name,
   Fill_Factor
from Sys.Indexes
   where Object_id=object_id('item')
   and name is not null
Go

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 to perform thorough testing before making any fill-factor changes to indexes in production environment