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.
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.)
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.
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:
000000006FA3A000: 010a0000 00020001 1b010000 01005a00 2c010000 ..............Z.,...
000000006FA3A014: 01000200 54000000 0600f61f 2b010000 01000000 ....T.....ö.+.......
000000006FA3A028: 7e890000 f2010000 07000000 00000000 00000000 ~..ò...............
000000006FA3A03C: c50f9ea9 00000000 00000000 00000000 00000000 Å.©................
000000006FA3A050: 00000000 00000000 00000000 00000000 00005e00 ..................^.
000000006FA3A064: 01000000 00000000 00000000 00000000 00000000 ....................
000000006FA3A078: 00000000 00000000 00000000 00000000 00cd0700 .................Í..
000000006FA3A08C: 01000000 00000000 00000000 00000000 00000000 ....................
000000006FA3A0A0: 00000000 00000000 00000000 00000000 00000000 ....................
000000006FA3A0B4: 00000000 00000000 00000000 381ffeff ffffffff ............8.þÿÿÿÿÿ
000000006FA3A0C8: ffffffff ffffffff ffffffff ffffffff ffffffff ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ
000000006FA3A0DC: ffffffff ffffffff ffffffff ffffffff ffffffff ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ
000000006FA3BFCC: ffffffff ffffffff ffffffff ffffffff ffffffff ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ
000000006FA3BFE0: ffffffff ffffffff ffffffff ffffffff ffffffff ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ
000000006FA3BFF4: ffff0000 00000000 be006000 ÿÿ......¾.`.
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:
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.