Fill factor explained

The Fill Factor specifies the % of fullness of the leaf level pages of an index. When an index is created or rebuilt the leaf level pages are written to the level where the pages are filled up to the fill factor value and the remainder of the page is left blank for future usage. This is the case when a value other than 0 or 100 is specified. For example, if a fill factor value of 70 is chosen, the index pages are all written with the pages being 70 % full, leaving 30 % of space for future usage.

You might choose a high fill factor value when there is expected little to no change on the underlying table. High fill factor value creates an index smaller in size and the queries on the underyling table can retrieve data with less disk I/O operations since there are less pages to read. But if you have an index that changes frequently, you want to have a lower value to keep some free space available for the new index entries. otherwise, if fill factor is large and there is little free space in the indexes, SQL Server would have to do a lot of page splits to fit the new values into the index pages.

the lower fill factor of your index, the larger the index size. if we take a fill factor of 50% increases index size to about 2 times the size of an index with fill factor of 100%.

With new data added to the table, the index page needs to have sufficient space to take the new entries. Where there is not enough space, a page split needs to take place, therefore impacting the performance.