Can't imagine how on earth every insert could cause a page split with any thought in advance given to setting the proper fillfactor. How bizarre. But it would do the same to your nonclustered index, which at that point is your only lookup method for that table, so the detrimental affects are roughly the same.
I can.
I have (actually had) a table that caused a split with nearly every insert.
To explain further, the system read data into a "staging" table with an identity as the clustered primary key.
Once processed, the records were inserted into the archive table. The integer from the identity was the clustered primary key on this table.
Each row was nearly 8000 bytes in length. The rows in the staging table were not processed in order, so almost every insert into this table caused a page split.
Changing the fill factor to 50% reduced the splits by 30%. The size of the table and the indexes got to be 220 GB, which was 25% of the total database size.
Approximately 14k rows were inserted into this table every day.
A new identity column was created as the clustered primary key on this table, and the page splits stopped.
Is this a common thing? No. But fillfactor, IMHO, is one of the most misused feature in SQL Server.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/