When looking at the first DCM page, where is the next DCM page documented?

Question

TL/DR;

When looking at the first DCM page in a database (which documents which extents have been modified, so that the DIFF backup process doesn't have to scan the whole database for changes, but can hop to the extents to scan the pages which have been changed) where is the location of the next DCM page documented?

I would have thought that it would be documented in the m_nextPage attribute of the page header. But upon checking this doesn't seem to be the case.

Finding the DCM pages

I was instructing our apprentice on what the differential backups contain and how the DBMS documents the changes in the individual pages and then in the consolidated DCM page.

Reference: Pages and extents architecture guide (Microsoft Learn | SQL)

I then started playing around with a very large database and set off the following commands:

DBCC TRACEON (3604);
DBCC PAGE ( 6, 1, 0, 3 );       -- File Header 

This produced the following output:

PAGE: (1:0)


BUFFER:


BUF @0x000002CC7AC80780

bpage = 0x000002CC2D11E000          bhash = 0x0000000000000000          bpageno = (1:0)
bdbid = 6                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 48700                       bstat = 0x9
blog = 0x15a                        bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000002CC2D11E000

m_pageId = (1:0)                    m_headerVersion = 1                 m_type = 15
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x208
m_objId (AllocUnitId.idObj) = 99    m_indexId (AllocUnitId.idInd) = 0   Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0           Metadata: IndexId = 0               Metadata: ObjectId = 99
m_prevPage = (0:0)                  m_nextPage = (0:0)                  pminlen = 0
m_slotCnt = 1                       m_freeCnt = 7019                    m_freeData = 3321
m_reservedCnt = 0                   m_lsn = (2501578:318516:1)          m_xactReserved = 0
m_xdesId = (0:0)                    m_ghostRecCnt = 0                   m_tornBits = 1668847350
DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED 

After reading the outptut I noticed the information near the bottom and assumed (correctly) that the DCM page would be located in page number 6 (well, 7 actually, but...).

Let's take a look:

DBCC PAGE ( 6, 1, 6, 0 );       -- 1. DCM Page

Yes, this seems to be the first DCM pages:

PAGE: (1:6)


BUFFER:


BUF @0x000002CC7AC80300

bpage = 0x000002CC2D112000          bhash = 0x0000000000000000          bpageno = (1:6)
bdbid = 6                           breferences = 1                     bcputicks = 0
bsampleCount = 0                    bUse1 = 48800                       bstat = 0x9
blog = 0x7a7a7a7a                   bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000002CC2D112000

m_pageId = (1:6)                    m_headerVersion = 1                 m_type = 16
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 99    m_indexId (AllocUnitId.idInd) = 0   Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0           Metadata: IndexId = 0               Metadata: ObjectId = 99
m_prevPage = (0:0)                  m_nextPage = (0:0)                  pminlen = 90
m_slotCnt = 2                       m_freeCnt = 6                       m_freeData = 8182
m_reservedCnt = 0                   m_lsn = (2501605:3132423:42)        m_xactReserved = 0
m_xdesId = (0:0)                    m_ghostRecCnt = 0                   m_tornBits = -431908416
DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED           

DIFF_MAP: Header @0x000000B60C7FA064 Slot 0, Offset 96

status = 0x0                        

DIFF_MAP: Extent Alloc Status @0x000000B60C7FA0C2

(1:0)        - (1:24)       =     CHANGED                                
(1:32)       - (1:40)       = NOT CHANGED                                
(1:48)       -              =     CHANGED                                
(1:56)       - (1:80)       = NOT CHANGED                                
(1:88)       -              =     CHANGED                                
(1:96)       - (1:104)      = NOT CHANGED                                
(1:112)      - (1:128)      =     CHANGED                                
(1:136)      - (1:152)      = NOT CHANGED                                
(1:160)      - (1:168)      =     CHANGED                                
(1:176)      -              = NOT CHANGED                                
(1:184)      - (1:192)      =     CHANGED                                
(1:200)      - (1:208)      = NOT CHANGED   
...

However, finding the next DCM page was a trial and error. I added 4'096'000 pages to the 6 I already had (which is wrong, it should be only 512'000 pages. Thanks Martin Smith for pointing that out), but received a page number slightly higher than the next DCM page. The next DCM page could be found in the header information.

GAM (1:4089856) = ALLOCATED         SGAM (1:4089857) = NOT ALLOCATED    PFS (1:4092528) = 0x0   0_PCT_FULL
DIFF (1:4089862) = NOT CHANGED      ML (1:4089863) = NOT MIN_LOGGED    

The next DCM page is not documented in the m_nextPage header information, so I am assuming it should be in the last bit of the first DCM page.

Questions

1. Is my assumption correct that SQL Server detects the next DCM page as the last bit of the first DCM page? Or does SQL Server have other means of finding the second, third, fourth, etc. DCM pages?

2. Wouldn't it be more efficient to document the next DCM page in the m_nextPage header information of the first DCM page?

Answer 1

where is the location of the next DCM page documented?

It's located at the same offset at the next GAM interval as DCMs are per GAM interval since they track extents (which is roughly 4 GB).

Note that this is a known interval (GAM) and is faster to calculate the location based on the known value than to read the page and get the value for the next item.

So the offeset of a DCM page to the previous GAM page is always the same and because the offset of GAM to GAM is well documented, there is no need to calculate / store or otherwise reference the next DCM pages seperately?

Correct. Here is a concrete example, the first DCM is on the 7th page (page number 6 since it's 0 based) and the GAM interval is 511232 pages, so the next DCM is at 511232 + 6 = Page 511238. Screenshot below for example output.