In certain instances, we may earn revenue when a reader makes a purchase or completes a form through our pages or on a partner’s website. We adhere to a strict What Does Fill Factor Mean?
In SQL Server, the basic
unit of storage is a leaf-level page. The fill factor determines what percentage of space on each page to fill with data. The fill
factor value can be defined as a percentage from 1 to 100. Values of 0 and 100 are
the same in all respects; both values indicate that the page is completely
filled with data, and the default value is 0. If some other value is specified as the fill factor, for example 70, then it means that 70 percent of the leaf-level
page is filled and 30 percent is left for future data. Therefore, if the fill factor is less than 100, then the remaining space is utilized for future data
growth.
Techopedia Explains Fill Factor
Fill factor is very important for database performance. It determines how the leaf-level data is stored and
organized. A proper fill factor setting can improve database performance. Fill
factor can be set in two ways: one is a generic fill factor set at server level,
applied to all tables and indexes. It can also be set at an individual table/index
level. For example, if the fill factor is set to 60 percent, then 60 percent of the page is
filled with data and the remaining 40 percent will be saved for future data growth. Then, if new relevant data comes in which belongs to the same page, it is
first saved within that 40 percent space.
However, if the page is completely filled, then the new data is accommodated
by a page-split process. In a page split, the data is divided into two
pages to accommodate the new data. Page splitting reduces database performance, as
the retrieval of scattered data is expensive. There is no perfect formula to
set a correct fill factor, but it varies for different applications and should
be decided based on requirements.
