Home Dashboard Directory Help

Per Table Allocation Delta by xor88


 as Won't Fix Help for as Won't Fix

Sign in
to vote
Type: Suggestion
ID: 752040
Opened: 7/2/2012 11:08:53 AM
Access Restriction: Public


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:

Sign in to post a comment.
Posted by Microsoft on 2/19/2013 at 3:30 PM
Thanks for your suggestion, Unfortunately this cannot be implemented at this time.
Sign in to post a workaround.
File Name Submitted By Submitted On File Size  
SqlServerPageVisualizer.zip (restricted) 7/2/2012 -