Tech moves fast! Stay ahead of the curve with Techopedia!
Join nearly 200,000 subscribers who receive actionable tech insights from Techopedia.
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.
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.