We have a large data warehouse database where we continuously get new rows inserted in 5 different tables, at the left-hand side of the b-tree (=at end of the table)
This cause a lot of page splits. Paul Randall calls these "good" page splits as in they do not cost much when inserting the data.
But when we query sys.dm_db_index_physical_stats we see an avg_fragment_size_in_pages just under 8, namely the 8 8kB pages that can fit into one 64kB extend. A table with 50000 pages has 6300 fragments. Not very "good" page splits anymore!
The only way I know we can bring these extends together (in order to get better read-ahead), is to REBUILD the table (or table partition).
Question 1) Are there other options?
We have one table that in addition to the new inserts get a lot of updates to the newer rows. I.E we get 6 rows every hour, and once a week all rows for the entire week are recalculated (=updated). Fillfactor only works for index REBUILDs
Question 2) Is there any way I can get SQL Server to use a FillFactor look-alike when inserting rows on the left-most page in the B-Tree?