4

The default FILLFACTOR is 100 and I have been told at times to reduce it to 80 or lower so that page splits will be reduced due to the empty space on each page.

I don't understand this--if a new index is created on a table with a default FILLFACTOR of 80, 20% space of each page is left empty. So assume a row needs to use 10% of a page, so when it's full it can hold 10 records. With a FILLFACTOR of 80, now it can only hold 8 records. So when you add the 9th record, will there be a page split? With 100% FILLFACTOR, a page split will only happen on the 11th record, so it seems that a lower fill factor makes it worse.

3

Fill factor does not impact “regular” inserts/updates. It tells the engine to leave some space while building/rebuilding the index. This is not “reserved” and will fill up with DML activity. It doesn’t protect you against page splits, it simply postpones them a bit but it depends on the key and insert patterns. For example, for an identity or other ever increasing types, page splits can’t occur due to inserts, and it does cause a waste of memory space so be careful. I wouldn’t modify it, unless I had s compelling reason to. HTH

  • oh I see. so you mean Fill factor postpones page splits when rebuilding the clustered index, and leave some space for future insertion? – user183639 Jun 20 at 4:56
  • Yes. Also, you can remove the word "clustered" above since the concept is the very same regardless of whether the index is CL or NC. – Tibor Karaszi Jun 20 at 6:16
3

The fill factor does not apply to new rows being added to a page. The fill factor only applies to how full a page is filled when the index is created, rebuilt, or reorganized.

Accordingly, if the index is rebuilt or reorganized on a scheduled basis, your index will be able to accommodate new rows with fewer page splits if the fill factor is set appropriately.

0

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.

Useful articles :

https://www.sqlskills.com/blogs/kimberly/database-maintenance-best-practices-part-ii-setting-fillfactor/

https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2530-fill-factor/

https://www.red-gate.com/simple-talk/blogs/how-to-identify-the-source-of-page-splits-in-a-database/

https://www.sqlskills.com/blogs/jonathan/an-xevent-a-day-27-of-31-the-future-tracking-page-splits-in-sql-server-denali-ctp1/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy