Craig Farrell (10/5/2010)
Which begs the question, why the heck are there extents in the first place for anything but LOB data...
An extent is nothing more than a contiguous 8 pages (64kb) aligned on a 64kb boundary. It has no special structure.
Why they exist, my guesses:
Make allocation easier. The GAM and SGAM pages are both 1 bit per extent. DIFF and ML pages as well I believe. If allocation was on a page level, there would be 8 times more allocation pages in the database files. (PFS pages are per-page though)
Make read-ahead more efficient. Read-ahead requires contiguous pages. So SQL can issue a read-ahead request for pages 36176 - 36208 possibly as a single IO request. To ask for pages 36208, 36210, 36215, 36218, 36220 and 36222 requires6 IO requests. (I think. I need to double check IO basics again)
Among probably many other reasons.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability