Microsoft SQL Server 2012 Internals (Developer Reference)

Author: Kalen Delaney, Bob Beauchemin, Conor Cunningham, Jonathan Kehayias, Benjamin Nevarez, Paul S. Randal
This Month Stack Overflow 1


by anonymous   2017-08-20

The 72K of reserved space includes a 64K extent (8 pages of 8K each) plus the 8K IAM page overhead. Of this 72K, only the IAM page and a single data page is actually used. sp_space_used reports the IAM page in the index_size, albeit not technically an index. You can see these details with the undocumented sys.dm_db_database_page_allocations TVF (use only on a test system):

SELECT extent_file_id, extent_page_id, page_type_desc
FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(N'dbo.MyTable'), 0, 1, 'DETAILED');

This database apparently has the MIXED_PAGE_ALLOCATION database option set to OFF so a full 64K extent is allocated a initially. If the option were ON, the single data page would be allocated from a mixed extent instead of a 64K extent dedicated to the table. The space allocated in that case would be 16K - a 8K single data page plus the IAM page.

Although mixed extents do reduce space requirements for small tables (under 64K), mixed extents have more overhead and can cause allocation contention in a high concurrency workload so it is off by default in SQL 2016 onwards. In older SQL versions, mixed extent allocation was on by default and can be turned off at the server level with trace flag 1118.

You can see the mixed extent setting in sys.databases:

SELECT name, is_mixed_page_allocation_on
FROM sys.databases;

To toggle the setting:



Space within a data page includes overhead for the page itself as well as records within the page. This overhead, plus the space needed for user data, will determine how many rows can fit on a page and number of data pages required to store a given number of rows. See Paul Randal's anatomy of a page and anatomy of a record articles for details of that overhead.


From your follow-up comment:

7998 bytes, so there are more 194 bytes to go for the next allocation. What am I missing?

I almost never use heaps but as you can see in the page dump, the associated PFS (page free space) allocation status for this page is 100 percent full. According to Kalen Delaney's Microsoft SQL Server 2012 Internals book, the PFS status is actually a 3-bit mask of these ranges:

  • 000: empty
  • 001: 1-50% full
  • 010: 51-80% full
  • 011: 81-95% full
  • 100: 96-100% full

So it looks like once heap page fullness crossed the 96% percent threshold it was considered 100% full and a new page was allocated. Note this does not happen on a table with a clustered index because the page for a new row is first determined by the CI key and a new page allocated only if it can't fit in that page at all. Yet another reason to avoid heaps.