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).
Are there other options?