The GAM Interval and GAM Extents

Introduction

In The Index Allocation Map I talked about the GAM Interval and pointed out that each GAM Interval is made up of 64000 extents. That information was based on other online resources including several articles by Paul Randal who had a leading role in developing the SQL Server engine and as such is usually a very reliable resource. The same information can also be found in Kalen Delaney's excellent book on Microsoft SQL Server 2012 Internals.

However, in this case the information presented in those resources is slightly inaccurate. In this article I am going to show you how big a GAM interval actually is. On the way, I am also going to cover the related topic of GAM extents.

GAM Interval

SQL Server uses several page types to catalog the state of all the pages in a database file. Each database is logically split into pages of 8192 bytes. All pages are organized in extents. An extent is a group of eight consecutive pages. The page-state tracking page types are GAM, SGAM, IAM, DIFF, ML and PFS pages. All but PFS pages track the state using a bitmap that associates each extent in a GAM interval with a single bit in that bitmap. (PFS pages track status information for single pages not extents.) That means that all those page types need to be present once for each GAM interval.

GAM pages track if an extent is in use. SGAM pages indicate if an extent is a shared extent. DIFF pages mark extents that have changed since the last full backup and ML pages keep track of pages that were affected by minimally logged operations in bulked-logged recovery mode. IAM pages finally link extents to allocation units. (See The Index Allocation Map.)

GAM Extent

IAM Pages are on demand pages and are associated with an allocation unit. As such, they do not have a fixed position within the database file. GAM, SGAM, DIFF and ML pages on the other hand all live in a special extent, the GAM extent. The GAM extent is always the first extent of a GAM interval. The GAM extent for the first GAM interval in each database file contains two additional pages. Page 0 in every file is always a File Header Page. Page 1 is always the first PFS page. All other GAM extents contain only the before mentioned four pages. The remaining pages in a GAM extent are unused as of SQL Server 2012.

The Mysterious GAM Interval Size

Page type 8 indicates that a page is a GAM page and the first GAM page is always page 2:

If a GAM interval were 64000 extents in size, we would expect the next GAM page to be page 512002 (64000 * 8 + 2). However, that page is of type 1 and therefore is not a GAM page:

Clearly, something is off here. To investigate further I created a simple one-page-per-row table in an otherwise empty database and filled it with a little over 1 million rows:

The first GAM interval contains allocation units for system tables, but the second GAM interval was empty at the start of this script. The number of rows should be enough to fill that second GAM interval completely with data for this table.

The IAM page for this table that covers the second GAM interval is page [1:299]:

Remember, an IAM page does not have to be located in the GAM interval that it covers. As an IAM page covers a GAM interval in the same way as a GAM page does, seeing a completely full page should give us some clues on how big a GAM interval actually is. The shortened output of the page buffer is below:

If you look at address 0x6FA3A0C0 you will see the two bytes 0x381f. Those are always present in an IAM page at that position. The map starts directly thereafter with the byte 0xfe. This tells us that the first extent of the GAM interval does not belong to this table. However, that is expected, as the first extent is always a GAM extent. The first byte after that position that does not have the value 0xff is byte 0x6FA3BFF6. That means there seem to be 0x6FA3BFF6 – 0x6FA3A0C2 = 7988 bytes in the map. 7988 bytes make 63904 bits. Each bit covers one extent, so a GAM interval should be 63904 extents or 511232 pages.

With a GAM interval length of 511232 pages, we would expect the second GAM page at page 511232 + 2 = 511234. Let us confirm:

This produces an error, complaining about an invalid page type. The page is actually completely zeroed out, as you can confirm by running the following statement:

However, directly above the error it tells us that the GAM page for this page is page [1:511232], the first page of the GAM interval. That page is indeed the GAM page for the second GAM interval:

Summary

So let use quickly rehash everything we talked about. Every database file is organized in 8192-byte pages that in turn are grouped into 8-byte extents. The file is further logically organized in sections of 63904 extents or 511232 pages. Those sections are called GAM intervals. The first extend in each GAM interval is the GAM extent that contains metadata pages. The GAM page is the first page in that GAM extent. However, the first two pages of a file are reserved for other pages. Therefore, the first GAM page in a file is not page 0 but page 2. Each GAM extent has at most six used pages (as of SQL Server 2012). The remaining ones stay unused.