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 ?
-
Taken from Paul Randal Myth series:– TheGameiswarFeb 7, 2016 at 8:31
-
25a) fill factor is adhered to at all times No. From Books Online: Important: The fill-factor setting applies only when the index is created, or rebuilt. The SQL Server Database Engine does not dynamically keep the specified percentage of empty space in the pages. Trying to maintain the extra space on the data pages would defeat the purpose of fill factor because the Database Engine would have to perform page splits to maintain the percentage of free space specified by the fill factor on each page as data is entered.– TheGameiswarFeb 7, 2016 at 8:31
-
Link:sqlskills.com/blogs/paul/…– TheGameiswarFeb 7, 2016 at 8:31
1 Answer
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
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.
-
I understand that the fill factor being 100 caused the page split that I mentioned in the above scenario, but what I meant to ask was that, if this situation happens quite often and a lot more pages are split, there will be a time where when all the pages have undergone the split process and now they are similar to a case where the fill factor was set to some arbitrary value initially. 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?– karun_rFeb 7, 2016 at 5:50
-
No after restart the value in sp_configure will not change even if many page splits happened in database many pages are half filled. I will add this in answer– ShankyFeb 7, 2016 at 5:53
-
@karun10 No, fill factor is a configuration setting and should be considered a goal, not a reflection of present (undesired) state. Feb 7, 2016 at 12:20