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?