Per Table Allocation Delta - by xor88

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 752040 Comments
Status Closed Workarounds
Type Suggestion Repros 0
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.