7

For interest, I was reading about the internal structures of a Microsoft SQL Server file. It's fairly obvious how IAM pages are connected. However, it is not clear to me where later GAM pages are to be found.

It is written that the first GAM page is page 2 in each file. DBCC PAGE confirms this. The links above state there is another GAM page in "4GB" or after "64,000 extents." When I look in those places (they are not the same number) I do not find a GAM page.

How many pages away from the first GAM page will I find the second GAM page (the "stride" of a GAM interval) in a SQL Server data file?

3 Answers 3

9

I have available a TB-sized database. There should be plenty of GAM pages to find.

Let's first examine the strides from those documents. 4GB is 524,288 pages. Adding this offset to 2 for the 1st page, and using DBCC PAGE, does not return a GAM page. Hunting a couple of extents either side does not return a GAM page either.

64,000 extents is 512,000 pages. Looking at page 512,002 and its neighbours does not return a GAM page either. It seems these are both rough approximations rather than hard facts about the file structure.

Let's examine the header of the first GAM page again.

dbcc traceon(3604);
dbcc page('Sandbox', 1, 2, 0);

m_prevPage and m_nextPage both are zero. There is no chain of pages to follow.

m_slotCnt is 2, though. I wonder what the different records represent? Using dbcc page('Sandbox', 1, 2, 1); shows the first record has length 94 and the second has length 7,992. Since each GAM bit represents one extent 7992 bytes represents 511,488 pages. Sadly there are no GAM pages in the vacinity of that offset either. More on this later.

Now this blog suggests that allocations bitmaps may not have slot arrays. So maybe DBCC was confused when I asked for per-row information and the two "records" are actually one continuous bitmap. Doing the sums gives 517,504 pages but, unfortunately, no second GAM page.

Looking again at page 2, but this time at the detailed per-row interpretation (option 3) I see

(1:0)        - (1:369568)   =     ALLOCATED
(1:369576)   -              = NOT ALLOCATED
(1:369584    - (1:511224)   =     ALLOCATED

I found a description here (under section 3.GAM) which says "[511224] is the first page of last extent in this range." So the first page of the next extent would be 511232. Since that would be the start of a new GAM range I'd expect to find another GAM page thereabouts. And there it is at page 511232.

Repeating this I find further GAM pages at 1022464, 1533696 & 2044928. There is a consistent interval of 511232 between these. This, I conclude, is the stride for a GAM interval. To check I take an arbitrary multiple 511232 * 127 = 64926464 and, sure enough, it is also a GAM page.

Working backward 511232 pages is 63,904 extents which is 7,988 bytes in a GAM page map. This is 4 short of the 7,992 I found for the record in slot 2. Which just happens to be the length of a record header. I should have realised.

0
9

sources

This has been documented in a bunch of places for a long time. The first place I saw it written about was in this blog post from 2012:

  • GAM: Page ID = 2 or Page ID % 511232
  • SGAM: Page ID = 3 or (Page ID –1) % 511232
  • PFS: Page ID = 1 or Page ID % 8088

Identification of these pages has also been included in sp_WhoIsActive, via this block of code:

CASE
    WHEN x.page_no = 1 OR x.page_no % 8088 = 0 THEN 'PFS'
    WHEN x.page_no = 2 OR x.page_no % 511232 = 0 THEN 'GAM'
    WHEN x.page_no = 3 OR (x.page_no - 1) % 511232 = 0 THEN 'SGAM'
    WHEN x.page_no = 6 OR (x.page_no - 6) % 511232 = 0 THEN 'DCM'
    WHEN x.page_no = 7 OR (x.page_no - 7) % 511232 = 0 THEN 'BCM'
    WHEN x.page_no IS NOT NULL THEN '*'
    ELSE NULL
END AS page_type

Which may be useful to anyone reading this who wishes to identify other page types of importance.

4

This info can can also be gleaned in newer versions from sys.dm_db_page_info.

Here's a brute-force method on a test database:

SELECT page_info.* 
FROM sys.database_files AS df 
CROSS APPLY generate_series(0, df.size-1) AS series 
CROSS APPLY sys.dm_db_page_info(DB_ID(), df.file_id, series.value, 'DETAILED') AS page_info 
WHERE  df.type_desc = 'ROWS' 
AND page_info.page_type_desc = 'GAM_PAGE';

See also The Global Allocation Map (GAM):

GAM pages are used by SQL Server to track, which extents are in use and which ones are unallocated. Each GAM page contains a 7988-byte bitmap covering all extent in a single GAM interval of 511232 pages. Each bit corresponds to a single extent, and a value of 1 indicates that that extent is still unallocated.

First version of this answer contributed by https://dba.stackexchange.com/users/44434

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.