Saturday, January 20, 2018
Term: Pagesplit
1 post(s)

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.

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