Problem: In databases with big multiple tables fragmentation is a big issue. Index rebuilds *don't* eliminate fragmentation enough to get to full disk read speed for a table scan. "MAXDOP = 1" improves the situation but throughput is still far than what could be provided by the disk.
The images attached show a 200GB production OLTP database. They have one pixel per database page.
In the first file the pixels are colored by allocation unit. For all allocation units over 1GB there is one extra file showing its allocated pages.
All indexes have just been rebuild "ONLINE = ON, MAXDOP = 1, SORT_IN_TEMPDB = ON" so they *should* be optimally laid out. They aren't. I can confirm that table scans proceed with low speeds (5-40MB/s) and low CPU usage (fractions of one CPU core). Process Explorer shows extreme variation in scan speed (between 1MB/s and 140MB/s).
Please note that although the tables were rebuilt with optimal options they are heavily fragmented.
Proposed solution: Allocate new pages for any given table in user customizable deltas. For example, I'd like to increase the size of the largest tables (about 20GB) in 16MB chunks. These chunks should be allocated at once and be contiguous on disk.
Such an allocation behavior would completely remove the fragmentation problem because fragments are at least 16MB in size.
Such a per-table setting would be easy to understand and document and would basically remove the fragmentation problem for big tables under many DML workloads.
It could look like this:
ALTER TABLE T SET ALLOCATION_DELTA = 16MB