|
Describe FillFactor concept in indexes- The value that determines the percentage of space on each leaf-level page to be filled with data is known as Fill Factor.
- In SQL Server, the smallest unit is a page, which is size 8K.
- This page can store one or more rows based on the size of the row.
- The default value of Fill Factor is 100, which has the same value as 0.
- The SQL Server allows to fill the leaf-level pages of an index with the maximum numbers of the rows it can fit by the default Fill Factor(100 or 0).
- When the fill factor is 100 there will be no or very little empty space left in the page.
- The "page split" event happens to accommodate new data if the page is completely filled and new data is inserted in the table which belongs to the completely filled page.
- SQL server splits the page in two pages by dividing the data in half and when new data arrives it accommodates the new data, and belongs to the page which is filled up completely.
- It tells us that a completely filled page is now two half-filled pages.
- As soon as the page split process is over, the new data is inserted at its logical place.
- The process of page split is expensive in terms of the resources.
- More amount of storage space is required to accommodate the small amount of data as there is a lock on the page for a brief period.
|
