Page Allocation: Dedicated vs. Mixed Extents

When I was at a SQLSaturday a few months ago, I attended a session that hit upon SQL Server Internals, one of my favorite topics.  During the session the presenter was talking about page allocation, and mentioned that pages for a table and its indexes will reside in the same extents.  Now, I remember immediately thinking a table and its indexes may have a few pages allocated from the same mixed extent, but I knew that was a finite number of pages, and after that, each object would have pages allocated from dedicated extents.  Specifically, the pages allocated to the table would come from different extents than those allocated to the indexes.

I wanted to test this out myself, but never made the time until recently.  I was listening to the MCM Data Structures video while running (don’t laugh, give it a try!) and was reminded of the topic when I heard Paul Randal ( b | t ) talk about extents and how pages are allocated to extents.  As a reminder, the first eight (8) pages that are allocated to a table or index are allocated one page a time, and those pages can be allocated from any existing mixed extent.  A mixed extent is a logical group of 8 pages where the pages can belong to different objects.  Realize the first 8 pages for a table or index will probably come from more than one mixed extent.

After those first 8 pages have been allocated, then the engine allocates pages from dedicated extents, where all the pages for an extent are allocated to the same table or index.

When I started testing this I had to figure which pages came from which extent.  Remember, an extent is a logical structure…it’s just eight pages grouped together.  If you view a page using DBCC PAGE, you can easily see the PageID, but there is no “ExtentID” on the page.  I messaged Paul about it and he said something like, “just divide the PageID by 8.”  Right.  So pages 0 through 7 are for the first extent, pages 8 through 15 for the second extent and so on.

For my test I created a new database, and then I looked at Page 1 in the database (the first PFS page) to see what pages were already allocated.  In my database, the highest page allocated was 161.

CREATE DATABASE [TestDB]
ON  PRIMARY
( NAME = N'TestDB',
FILENAME = N'C:\Databases\SQL2008R2\TestDB.mdf' ,
SIZE = 10240KB ,
FILEGROWTH = 5120KB )
LOG ON
( NAME = N'TestDB_log',
FILENAME = N'C:\Databases\SQL2008R2\TestDB_log.ldf' ,
SIZE = 5120KB ,
FILEGROWTH = 5120KB );

ALTER DATABASE [TestDB] SET RECOVERY SIMPLE;

USE TestDB;
GO

Next I created a table, inserted some rows and used DBCC IND to see what pages had been allocated to the table.  Realize that no pages will be allocated to the table until you insert data.

CREATE TABLE ExtentTest (
intCol BIGINT IDENTITY (1,1),
vcharCol VARCHAR(8000));

INSERT INTO ExtentTest
(vcharCol) VALUES (
REPLICATE('Row1',1800)),
(REPLICATE('Row2',1800)),
(REPLICATE('Row3',1800)),
(REPLICATE('Row4',1800)),
(REPLICATE('Row5',1800)),
(REPLICATE('Row6',1800)),
(REPLICATE('Row7',1800)),
(REPLICATE('Row8',1800));

DBCC IND ('TestDB','ExtentTest', -1);
GO

I have 9 pages allocated, one is Page Type 10 for the IAM page, the others are Page Type 1, which is my data page.  I’ve truncated the output a bit for readability.

DBCC IND for ExtentTest table

My PageIDs and “Extent IDs” are such:

My pages were allocated from two extents, and it almost looks like extent 19 is dedicated to the table.  Let’s see what happens if I add a few more rows.

INSERT INTO ExtentTest
(vcharCol) VALUES (
REPLICATE('Row9',1800)),
(REPLICATE('Row10',1800)),
(REPLICATE('Row11',1800)),
(REPLICATE('Row12',1800));

DBCC IND ('TestDB','ExtentTest', -1);
GO

DBCC IND for ExtentTest table...with more rows

My next pages are 160, 161, 162 and 163 which would be extent 20.  Now I’m in a dedicated extent.  As an aside, if I run DBCC PAGE on page 152 (the other page for extent 19), it doesn’t look like my other data pages at all.

All right, so I’ve got an existing dedicated extent, let’s create a nonclustered index and see where that data goes.

CREATE NONCLUSTERED INDEX NCI_ExtentTest ON ExtentTest (intCol)

DBCC IND ('TestDB','ExtentTest', -1);
GO

DBCC IND for ExtentTest table...after adding a nonclustered index

When I run DBCC IND now, I have an entry with a PageType of 2 (index) and that PageID is 168.  That would be extent 21.  Neither the index page, or its IAM page (169), came from the existing dedicated extent for the table.  Just to make sure table and index pages do reside in different dedicated extents, let’s add a lot of data and run DBCC IND again.  I get a lot more rows, but I’m only going to look for those of PageType 2.

SET NOCOUNT ON
DECLARE @rownum BIGINT = 13

WHILE @rownum < 3400
BEGIN

INSERT INTO ExtentTest
(vcharCol) VALUES (
'Row' + CAST(@rownum AS VARCHAR(4)))

SET @rownum = @rownum + 1
END

DBCC IND ('TestDB','ExtentTest', -1);
GO

DBCC IND for ExtentTest...now with even more rows

No pages from extent 20.  If I continued adding data, I expect that pages would be allocated from extent 24, the first dedicated extent (where page 192 resides).

As I thought, pages for a table and its indexes do not exist in the same dedicated extents.  It is possible for the first 8 pages of a table and indexes to reside in the same mixed extent, but after the first 8 pages are allocated for any object – table or index – the remaining pages reside in dedicated extents.