Use of Fill Factor in Index

Fill Factor

While createting the indexes on the table, you can set the fill factor for the index.(Default value of Fill factor is 0))

Fill factor is use to reserve the space on an index page to avoid the page spliting.

[Page Spliting : Page Spliting is internal machanism that server use to make the room for new element in a file. If an index page splits then system consume more disk I/O to read the index alloction map (IAM). And new pages are not created sequentially, so fill factor leave a little room
to grow.]

When creating an index, you can specify a Fill factor to leave the extra gap & reserve a percentage of free space on each leaf level page of the index for future expension.

The fill factor value is a % of 0 to 100 that specify how much to fill the data page after the index is created. A value of 100 means the page will be full & will take the least amount of storage space.

[Note : there is a diffrence betwwen the value of fill factor is 0 or 100. In case of 100 the index has some space but in the case of 0 no space.]

EX :
-------

If you set the value of fill factor is 80 meanse 20% space will be left to grow up the data.

TC of it :
-------------
- Fill factor value will be set when there are lot of changed(Insert, Delete, Update) in your table.

- If your table is read only or minimum change in data (in case of DWH where only historic data is stored) then you should not set the fill factor value. (i.e default value (0) is fine)

Regards
Manish