How Many Objects Can Own Pages in a Mixed Extent

I saw an exchange on Twitter this past weekend on the #sqlhelp hash tag about how pages are allocated for a temp table. The replier (blog|@sqL_handLe) — whom you should be following if you’re not already, very smart guy — gave the classic response about mixed extents and how they can be owned by up to 8 objects. The link he provided, Understanding Pages and Extents, agrees with him and says, “Mixed extents are shared by up to eight objects”. Case solved, end of story, right?

My mind was thinking about how the original poster asked about creating temp tables, and I thought that means it will have a IAM (index allocation map) page allocated as well when the first page is allocated. So at the very least, there should be at least 2 pages in the mixed extent that belongs to that object so there is no way that extent could possibly be owned by up to 8 objects. I needed to prove this to myself so I set up a demo

First, I create a temp table and check the pages allocated to it using sys.dm_db_database_page_allocations. As I blogged previously, a newly created table is empty (How Big is an Empty Table in SQL Server), but an empty table does have allocated pages under some circumstances.

-- Create table for testing
-- Each record will effectively consume 1 page
Create table #TestTable (ID int identity(1, 1) Not Null primary key,
    val1 char(5000) null);
Go

-- Query for pages allocated to table
-- Using DMV for tracking page allocations in SQL 2012+
-- If doing this on earlier versions, use the undocumented
-- DBCC command DBCC IND()
-- The table is empty so no pages are allocated yet
Select object_name(object_id) As TableName,
    index_id,
    allocation_unit_type_desc,
    is_allocated,
    page_type_desc,
    has_ghost_records
From sys.dm_db_database_page_allocations(
    2, -- tempdb database ID
    object_id('tempdb..#TestTable'),
    Null,
    Null,
    'Detailed');
Go

Each row in the table will consume enough of the page that there is one row per page. If I add one row, it yields one data page that is mostly full. It also yields one IAM page for tracking the allocations for this table.

-- Query for pages allocated to table
Select object_name(object_id, 2) As TableName,
    index_id,
    allocation_unit_type_desc,
    is_allocated,
    page_type_desc,
    allocated_page_page_id,
    allocated_page_iam_page_id
From sys.dm_db_database_page_allocations(
    2, -- tempdb database ID
    object_id('tempdb..#TestTable'),
    Null,
    Null,
    'Detailed');
Go

Clearly two pages are allocated to this object now, a data page and an IAM page, but I found it odd that the data page was page 399 and the IAM page was page 400. Extents are aligned in 8 page intervals and the first page in the file is page 0 so the first page of an extent should be divisible by 8. 400 is divisible by 8 so the IAM page is the first page in the IAM and the data page is in the previous extent. So they’re actually in different extents. I can see this by adding the extent_page_id column to the output which how the first page of the extent.

-- Query for pages allocated to table
Select object_name(object_id, 2) As TableName,
    index_id,
    allocation_unit_type_desc,
    is_allocated,
    page_type_desc,
    allocated_page_page_id,
    allocated_page_iam_page_id,
    extent_page_id
From sys.dm_db_database_page_allocations(
    2, -- tempdb database ID
    object_id('tempdb..#TestTable'),
    Null,
    Null,
    'Detailed');
Go

I believe that this is just coincidence, but I feel I need to prove that the IAMs and data pages are not intentionally allocated in different extents. So I create 7 more temp tables with 1 record each resulting in 1 data page and 1 IAM page. Let’s see if they get allocated to different extents.

-- Create a bunch of tables to fill the extent
Declare @a char(5000) = 'aaaaa';

Select @a As Val1
Into #TestTable1;

Select @a As Val1
Into #TestTable2;

Select @a As Val1
Into #TestTable3;

Select @a As Val1
Into #TestTable4;

Select @a As Val1
Into #TestTable5;

Select @a As Val1
Into #TestTable6;

Select @a As Val1
Into #TestTable7;

-- Query for pages in these tables
Select object_name(object_id, 2) As TableName,
    index_id,
    allocation_unit_type_desc,
    is_allocated,
    page_type_desc,
    allocated_page_page_id,
    allocated_page_iam_page_id,
    extent_page_id
From sys.dm_db_database_page_allocations(
    2, -- tempdb database ID
    Null,
    Null,
    Null,
    'Detailed')
Where object_name(object_id, 2) Like '#TestTable%'
Order By allocated_page_page_id;
Go

In the truncated results above, you can clearly see that it allocates the first data page and then the IAM page in order wherever it falls. It often is the same extent, but not necessarily so.

An IAM covers all the pages for an index or table within a GAM interval (4 GB block of file as tracked by a single GAM page). It will not allocate another IAM page unless a page ends up in a different 4 GB block. To make this a little more complex, we also have to consider index pages. The #TestTable table has a clustered index on it so if I add another row, it will allocate another data page resulting in 2 data pages and 1 IAM page and now it will also allocate an index page to track which rows are allocated in which node of the B-tree index pages (data pages). If this was a heap, it would not create index pages (no, this does NOT mean heaps are better than clustered indexes, don’t even go there).

-- Add more data
Insert Into #TestTable (val1)
Values ('aaaaa');
Go

-- Query for pages allocated to table
Select object_name(object_id, 2) As TableName,
    index_id,
    allocation_unit_type_desc,
    is_allocated,
    page_type_desc,
    allocated_page_page_id,
    allocated_page_iam_page_id,
    extent_page_id
From sys.dm_db_database_page_allocations(
    2, -- tempdb database ID
    object_id('tempdb..#TestTable'),
    Null,
    Null,
    'Detailed');
Go

We see in the results above that there are now 4 pages allocated for this table, each in a different extent. Purely coincidental as you can see that 2 of those pages are the first page in their extents. They just happened to bridge extents by happenstance. If I had real activity occurring on this instance, I would see different results.

If I take index pages out of the equation, and just insert records into the other temp tables which are heaps, maybe I can create a shared extent that is owned by 8 different objects. We already have one page from #TestTable in a new extent, so let’s add 1 record to each of the 7 other temp tables to see what we get.

-- Add even more data
Declare @a char(5000) = 'aaaaa';

Insert Into #TestTable1
Select @a;

Insert Into #TestTable2
Select @a;

Insert Into #TestTable3
Select @a;

Insert Into #TestTable4
Select @a;

Insert Into #TestTable5
Select @a;

Insert Into #TestTable6
Select @a;

Insert Into #TestTable7
Select @a;

-- Query for pages in same extent as above object
Select object_name(object_id, 2) As TableName,
    index_id,
    allocation_unit_type_desc,
    is_allocated,
    page_type_desc,
    allocated_page_page_id,
    allocated_page_iam_page_id,
    extent_page_id
From sys.dm_db_database_page_allocations(
    2, -- tempdb database ID
    Null,
    Null,
    Null,
    'Detailed')
Where object_name(object_id, 2) Like '#TestTable%'
Order By allocated_page_page_id;
Go

We can see that the final extent is 8 pages from 8 different tables. It the circumstances are right, the pages in a single extent can be owned by up to 8 objects.

Here are some blog posts that helped me in the writing of this blog post, all by Paul Randal (@PaulRandal|blog):

Inside the Storage Engine: Anatomy of an extent
Inside the Storage Engine: IAM pages, IAM chains, and allocation units
Are mixed pages removed by an index rebuild?