Fill Factor: Will the fill factor change automatically after it detects the changes in the index pages?

Question

So lets say that the default fill factor is left at 0 initially. Data has been loaded up into the server and one fine weekend, the index rebuild job is run. Now if it detects any new data which must be mapped into a already full index page, it will split the page into half and position the record accordingly. So, now, essentially the fill factor of the database is not 0 as there are two pages which are half full. Will this reflect when we run sp_configure the next time ?

Answer 1

So, now, essentially the fill factor of the database is not 0 as there are two pages which are half full

You are correct, but this is actually caused by the fill factor value. You cannot say that now fill factor is not 100 (assuming your case) that would be an incorrect statement because fill factor was 100 this page split event was forced. Suppose you have index page which was almost full and new row needs to be accommodated now this would cause page split and move half rows to new page there by causing fragmentation. So what caused this: it was precisely the 100 fill factor value. That is why you must be cautious with what fill factor value you choose

A low, nonzero fill-factor value may reduce the requirement to split pages as the index grows, the index will require more storage space and can decrease read performance

Also note that whatever fill factor value you use you CANNOT avoid page split even if you have ever increasing columns. There are 2 types page splits good and bad. The bad one is when data is inserted in middle creating a gap.

Some information about Full Factor in Paul's Myth Series about Fill Factor

Read more about fill factor

Will this reflect when we run sp_configure the next time ?So when we restart the SQL Server and check the current value of the fill factor using sp_configure or any other option, does it reflect the current calculated value? Or will it just remain with the same value?

No after restart the value of fill factor in sp_configure will not change even if database has many page splits and many pages are half filled. This is called as fragmentation.