5

We've been having a problem with page splits on a table that is a particular nuisance - its an audit log of activities in the database and has grown over 1TB. The main indexes are on the record type, which is an NVARCHAR(100) - because you need that when there are 5 record types - it makes much more sense than a TINYINT, and a record id - which is an NVARCHAR(200) instead of the integer key of the record.

They are also covering indexes, with the key, old value, new value, etc - very wide.

This is an old system, and unfortunately the code for this auditing is everywhere rather than centralized in a procedure. It can't be changed, and we're going thru the painful process of a long micro-services re-write.

So, I have reduced the fill factor on two of the indexes from 100% to 85%.

And the page splits have gotten worse. I'd say around 3x more page splits.

Is this a common outcome? Most advice says to reduce fill factor to improve page split performance. I can understand why it may do this, because of the width of the data in the keys.

Would the advice be to reduce the fill factor further, or to put it back to what it was?

|improve this question
  • One question--are you actually having a performance issue, or are you just wanting to fix the excessive page splits because it's possible to fix them? If it's not really causing a performance issue, then I would consider just leaving it alone. You don't have to answer--just food for thought. – Tony Hinkle Aug 26 at 17:52
  • Thanks everyone for the advice - @david-browne-microsoft and Tony Hinkle were correct in that it was pretty terrible for the next day and then settled down, as the pages were spaced out again. It is well below what it was, but I am still left wondering how far I should reduce it further.. That would be another question though :) – crucible Sep 2 at 4:31
3

It's really not possible that changing the fill factor would cause more page splits, assuming that you are measuring the page split rate immediately after a 100% fill factor rebuild/reorg vs. an 85% fill factor rebuild/reorg. It will be difficult, if not impossible, to find an authoritative reference that states this explicitly, but any accurate discussion on this topic states it implicitly. If page splits have increased on your system, it is simply due to the required indexing of the data that has been added or modified.

It is important to note (as you made no mention of it) that you will need to rebuild or reorganize the clustered index in order for the fill factor to actually do anything. The fill factor is only used when an index is created, rebuilt, or reorganized--it does not affect how much data is put into new pages.

As David Browne points out in his answer, if your pages have been splitting a lot, there are already a lot of pages that have significant free space in them. After a page split, there will be two pages about 50% full. When you reorg/rebuild with a fill factor, all of the pages end up 85% full, so you may have actually reduced the average amount of free space in the pages.

Please see Specify Fill Factor for an Index, and Kendra Little has a good article that I would also recommend on this topic at 5 Things About Fillfactor.

Also, I might add that if the page splits are not really causing a problem (user pain, etc.) then it might not be worth "fixing." When you're dealing with third-party applications or things that are out of maintenance, it's usually worth considering the option to ignore some of the inefficient operations unless they are truly causing a problem.

|improve this answer
7

When you have lots of page splits caused by random middle-of-BTree inserts, your pages will naturally average around 65%* full. Split pages start at 50% full and any that fill up past 100% get split back down to 50%.

So rebuilding your index with a fill factor of 85% might temporarily increase page splits, just not as much as rebuilding with a fill factor of 100%

*Why not 75%? I think it's because each split page covers half the value range of the original page, the new pages fill up half as fast as the original page.

|improve this answer
0

It shouldn't be. read this. It should temporarily reduce the number of page split occurrence due to the allowance set in the page, in your case, 15%. But if data movement/transaction is quite fast, it may become full fast and just perform page splits again.

|improve this answer
0

Page split is normal behavior of SQL Server.

When we insert new records to a table, if current page doesn't have enough space to write the record/row, new page will be allocated to the table. This processes is also comes under page split. This type of page split is not bad.

It becomes bad when it caused by UPDATE and new records doesn't fit within the page. In this case new page assigned to the set of record & space left to existing page.

As I understand after you decrease FILLFACTOR, more number of pages being allocated. So this page split caused by INSERT addition of new records.

Thanks!

|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.