How are single-page allocations tracked?

Question: I’ve been reading some information on how SQL Server keeps track of which parts of a data file are used by a particular object – IAM pages – but they only track extents. I’ve also read that small tables don’t use extents but instead use up to eight single pages from the data file. How are those tracked?

Answer: They’re actually tracked in the same way – by an IAM page.

Back in the days of SQL Server 7.0 storage was expensive and so people didn’t want very small tables taking up an entire extent (set of eight contiguous 8KB data file pages) when 64KB wasn’t needed. Consequently, the first eight pages allocated to an object (or any of its indexes) where one-page-at-a-time allocations, commonly known as single-page allocations or mixed pages (so called because the extent they are allocated from contains pages allocated to a mixture of objects).

This ‘feature’ is still present in the Storage Engine today and I’ll be surprised if it gets removed any time soon as the single page allocation code is baked deeply into the Storage Engine code base.

You are correct that an IAM (Index Allocation Map) page contains a bitmap that allows it to track all the extents (from a roughly 4GB chunk of a data file) that are allocated to a particular object or index (or partition thereof, keeping things simple). If allocations to an object come from multiple 4GB chunks (of the same or multiple files) then a set of IAM pages are required to track all the allocations – called an IAM chain.

The first eight single-page allocations are tracked in a special area called the single-page slot array in the very first IAM page in the IAM chain, and you can look at it to see.

CREATE DATABASE foo;GOUSE foo;GOCREATE TABLE t1 (c1 INT IDENTITY, c2 CHAR (8000) DEFAULT 'a');GO-- Insert 10 recorsds, requiring 10 pagesINSERT INTO t1 DEFAULT VALUES;GO 10

Now we can use the DBCC IND command to find the IAM page:

DBCC IND ('foo', 't1', -1);GO

The output will list eleven rows, the IAM page and the ten data pages. You’ll notice that the page ID in the first row is listed as the IAM page ID in the following ten rows. From the first row of output, take the values of the PageFID and PagePID and substitute them in the code below:

DBCC TRACEON (3604);GODBCC PAGE ('foo', , , 3);GODBCC TRACEOFF (3604);GO

The trace flags just allow the DBCC PAGE output to come to your query window instead of the SQL Server error log.

The output will include something like this:

IAM: Single Page Allocations @0x000000001473A08ESlot 0 = (1:153)         Slot 1 = (1:155)            Slot 2 = (1:156)        Slot 3 = (1:157)        Slot 4 = (1:158)        Slot 5 = (1:159)Slot 6 = (1:176)        Slot 7 = (1:177)                     IAM: Extent Alloc Status Slot 1 @0x000000001473A0C2(1:0) - (1:176) = NOT ALLOCATED(1:184) - =ALLOCATED(1:192) - (1:296) = NOT ALLOCATED

This shows the single-page slot array (with the first eight pages) and the 4GB tracking bitmap showing the single dedicated extent allocated to the object. Rows two through nine in the DBCC IND output will exactly match the contents of the single-page slot array, and rows ten and eleven in the DBCC IND output will be the first two page IDs from the extent marked as ALLOCATED in the bitmap dump.

I hope this makes things a little clearer for you.