today we are going to talk about
- what extent is
- File header page
- boot page
- Page Free Space (PFS)
- Global allocation map(GAM)
- Shared global allocation map (SGAM)
- index allocation map or allocation units (IAM)
- Differential Changed Map (DIFF)
- Minimally logged (ML)
- Index page
Extents in data files
when we create a database SQL server groups 8 pages into logical units called extents
there are two types, mixed disabled by default since SQL server 2016 and before that, they used to use traceflag 1118
this happens when multiple databases use the same extent
and the other one is uniform extents which is the default now
SQL server uses allocation maps to track page and extent usage
GAM(global allocation map) = a bitmap that tracks extent usage
if it has a zero then it is in use
if it has a 1 then it is a free extent
global allocation map are uniform maps that are used for one database
and there are Shared global allocation maps or SGAM pages these are shared between databases
each one of those tracks about 64 000 extents or 4 GBs
so each database has 1 GAM for every 4gbs
so we have the page that stores 8kb
we have extents that store 64 kb
and we have GAM that tracks them, okay, what tracks what objects these extents belong to?
IAM or index allocation maps or allocation units
now IAM pages are created for regular data types that fits in the 8kb separately from LOB row overflow or LOB data files
so, like we saw in the previous blog, if we had varchar(8000) two cols, and one of them had to go outside the 8kb page, we will have one for the in row and one for the overflow
now if we had a text column, then we will have a separate one for LOB data
now the GAM and SGAM and them they are pages essentially, so what tracks them?
the first IAM
and PFS tracks free space
and ML tracks the bulk operations
so we have pages tracked by IAM
we have extents that is tracked by GAM
and we have file page that tracks the page
and we have a boot page that tracks the database.
File header page
it is numbered 0 in a database so if we do the following
USE TestDatabase
CREATE TABLE FILE_PAGE_EXPLORE(
ID INT)
INSERT INTO FILE_PAGE_EXPLORE(ID) VALUES (1)
DBCC IND ('TestDatabase',
'dbo.file_page_explore',
-1)
DBCC TRACEON(3604)
DBCC PAGE('TestDatabase'
,1,0,3)
we get the following
PAGE HEADER:
Page @0x0000020B64C34000
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 = 6922 m_freeData = 4784
m_reservedCnt = 0 m_lsn = (66:98440:17) m_xactReserved = 0
m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = -1317461446
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
File Header Data:
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 1172
FileIdProp = 1
FileGroupId = 1 Size = 173056 MaxSize = 4294967295
Growth = 8192 Perf = 0 BackupLsn = (0:0:0)
FirstUpdateLsn = (0:0:0) OldestRestoredLsn = (0:0:0) FirstNonloggedUpdateLsn = [NULL]
MinSize = 1024 Status = 0 UserShrinkSize = 4294967295
SectorSize = 4096 MaxLsn = (0:0:0) FirstLsn = (0:0:0)
CreateLsn = (0:0:0) DifferentialBaseLsn = (0:0:0) DifferentialBaseGuid = 00000000-0000-0000-0000-000000000000
FileOfflineLsn = (0:0:0) FileIdGuid = b9c84b85-4b5b-473d-9fb6-764a66043900
RestoreStatus = 0 RestoreRedoStartLsn = (0:0:0) RestoreSourceGuid = 00000000-0000-0000-0000-000000000000
HardenedSkipLsn = [NULL] ReplTxfTruncationLsn = [NULL] TxfBackupLsn = [NULL]
FstrContainerSize = [NULL] MaxLsnBranchId = 00000000-0000-0000-0000-000000000000
SecondaryRedoStartLsn = [NULL] SecondaryDifferentialBaseLsn = [NULL]
ReadOnlyLsn = (0:0:0) ReadWriteLsn = (0:0:0) RestoreDifferentialBaseLsn = (0:0:0)
RestoreDifferentialBaseGuid = 00000000-0000-0000-0000-000000000000
RestorePathOrigin
FCBFileDEK
m_dbeStatusBits = 0 m_dtCreated = 1900-01-01 00:00:00.000
m_dtLastRegenerated = 1900-01-01 00:00:00.000 m_dtLastModified = 1900-01-01 00:00:00.000
m_dtLastSet = 1900-01-01 00:00:00.000 m_dtOpened = 1900-01-09 00:00:00.000
m_algId = 0 m_algId = 0 m_dwBitLen = 0
m_cbThumbprint = 0 m_rgbThumbprint = 0x
ProxyFileIdProp = 1 ForeignRedoLsn = (0:0:0) ForeignRedoTime = 1900-01-01T00:00:00
ForeignRedoOldestBeginLsn = (0:0:0)
so we get information related to the file itself where the allocation maps are the type here is 15 = page file header
we see stuff like when was the last restoration, how it was, why it was like that
the size
the growth rate
max size
some old physical hard drive data
logging related information
and in the 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
we can see the numbers of the other pages that tracks the extents
Boot page
now here instead of tracking the physical file, it tracks the database itself
it is the 9th when first created so:
dbcc page('TestDatabase',1,9,3)
and the output:
PAGE: (1:9)
PAGE HEADER:
Page @0x0000020B477DA000
m_pageId = (1:9) m_headerVersion = 1 m_type = 13
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 99 m_indexId (AllocU..................................................
1114867035
DB Frag ID = 1
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x64 MIXED_EXT ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED
Slot 0, Offset 0x60, Length 1786, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = Record Size = 1786
dbi_version = 957 dbi_createVersion = 957 dbi_SEVersion = 0
dbi_dvSplitPoint = 0:0:0 (0x00000000:00000000:0000) dbi_dbbackupLSN = 0:0:0 (0x00000000:00000000:0000)
dbi_LastLogBackupTime = 1900-01-01 00:00:00.000 dbi_nextseqnum = 1900-01-01 00:00:00.000
dbi_status = 0x00010000 dbi_crdate = 2025-02-10 14:08:17.710dbi_dbname = TestDatabase
dbi_dbid.......................
m_guid = 00000000-0000-0000-0000-000000000000
dbi_differentialBaseGuid = 00000000-0000-0000-0000-000000000000 dbi_firstSysIndexes = 0001:00000014
dbi_dynamicFileAllocationNewFileID = 0 dbi_oldestBackupXactLSN = 0:0:0 (0x00000000:00000000:0000)
dbi_lastLogBackupOldestActiveXactLsn = 0:0:0 (0x00000000:00000000:0000) dbi_lastLogBackupCheckpointLsn = 0:0:0 (0x00000000:00000000:0000)
dbi_versionChangeLSN = 0:0:0 (0x00000000:00000000:0000) dbi_mdUpgStat = 0x0004
dbi_category = 0x0000000000000000 dbi_safetySequence = 0 dbi_dbMirrorId = 00000000-0000-0000-0000-000000000000
dbi_pageUndoLsn = 0:0:0 (0x00000000:00000000:0000) dbi_pageUndoState = 0
dbi_disabledSequence = 0 dbi_dbmRedoLsn = 0:0:0 (0x00000000:00000000:0000)
dbi_dbmOldestXactLsn = 0:0:0 (0............................
here we see a similar information and stuff relevant to the compatibility level
containment state
securtiy
checkpoints
logs and backups
partitions
creation date
trace flags and more
Page free space PFS
so(paul randal):
- it is the second page meaning number 1
- there is one every 8088 pages
- type =11
- as it sounds it tracks free space
- ghosted records to be deleted
- full rows
- every byte represents a page
- where to insert LOB datatypes or heaps
- now we know a byte in binary means 8 bits
- bits 0-2 indicates how full the page is
- as you can see in the graph 0 means empty,
- 1 means 1-50% full
- 2 51-80
- 3 81-95
- 4 96-100 percent full
- 7th bit is unused
- 6th is it allocated
- 5th is it a mixed extent
- 4th is it an IAM page
- 3rd is there any row to be deleted, is it ghosted
this information is helpful in inserting LOB overflow or LOB data, and in heaps now let’s look at one
dbcc page('TestDatabase',1,1,3)
sample output
PAGE HEADER:
Page @0x0000020B64C32000
m_pageId = (1:1) m_headerVersion = 1 m_type = 11
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
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 = 2 m_freeData = 8188
m_reservedCnt = 0 m_lsn = (66:111600:7) m_xactReserved = 0
m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = -1849234597
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
PFS: Page Alloc Status @0x000000ACE1676000
(1:0) - (1:3) = ALLOCATED 100_PCT_FULL
(1:4) - (1:5) = NOT ALLOCATED 0_PCT_FULL
(1:6) - (1:7) = ALLOCATED 100_PCT_FULL
(1:8) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:9) - = ALLOCATE
Global Allocation Map or GAM(uniform = default)
so like we said before, SQL server has pages each 8 of them constitutes extents and they are tracked using GAM pages
each one of them can track 64 thousand extents or 4 gigs
after that a new one will be created
their type = 8
it is the third or number 2 in the file always
it has two bits for each page
if it is allocated = 0
not allocated = we can use it = 1
now we said earlier that an extent is 8 pages
and we know that the first 8 pages are created immediately upon the creation of the file
and these are:
so if we dbcc page the page 2
dbcc page('TestDatabase',1,2,3)
the output:
PAGE HEADER:
Page @0x0000020B64C30000
m_pageId = (1:2) m_headerVersion = 1 m_type = 8
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
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 = (66:111600:21) m_xactReserved = 0
m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = -766964551
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
GAM: Header @0x000000ACE1676064 Slot 0, Offset 96
status = 0x0
GAM: Extent Alloc Status @0x000000ACE16760C2
(1:0) - (1:165632) = ALLOCATED
(1:165640) - (1:173048) = NOT ALLOCATED
so as you can see all the extents from 1 to 165632 and its extents are all allocated
but after that we have a lot of empty pages
Shared global allocation maps or SGAM(shared)
now here instead of allocating one global allocation map for each database
here it finds for the small database an empty one and makes them share it
this could cause contention in highly concurrent databases especially in tempdb
since everybody might try to allocate stuff to the same page
and they might block each other from using the same resource, key, RID, page, object, database
that is why the default is GAM since it prevents such a problem
now the bits interpretation here is different
if we have at least one extent that yet to be allocated the bit would equal one
but if it did not have any empty extents = 0
now like we saw in the preceding output our file had a SGAM page but it is yet to be allocated
its type is 9
and it is the 4th page or 3
so if we dbcc page it:
dbcc page('TestDatabase',1,3,3)
the output would be like:
PAGE HEADER:
Page @0x0000020B64C2E000
m_pageId = (1:3) m_headerVersion = 1 m_type = 9
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 = (43:51752:10) m_xactReserved = 0
m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 1766461006
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
SGAM: Header @0x000000ACDB276064 Slot 0, Offset 96
status = 0x0
SGAM: Extent Alloc Status @0x000000ACDB2760C2
(1:0) - (1:320) = NOT ALLOCATED
(1:328) - = ALLOCATED
(1:336) - (1:376) = NOT ALLOCATED
(1:384) - = ALLOCATED
(1:392) - (1:456) = NOT ALLOCATED
(1:464) - = ALLOCATED
(1:472) - (1:536) = NOT ALLOCATED
(1:544) - = ALLOCATED
(1:552) - (1:640) = NOT ALLOCATED
(1:648) - = ALLOCATED
(1:656) - (1:720) = NOT ALLOCATED
(1:728) - = ALLOCATED
(1:736) - (1:173048) = NOT ALLOCATED
so it found the pages that had empty spaces but, it would not be used since our default is that these are allocated to the database and there is no need to use them since storage is plenty and way too cheaper than memory or CPU or any other resource
Index allocation map or IAM or allocation units
now we track extents using GAM or SGAM, so we know if one of them is being used
but how do we know who is using them, and what objects they belong to?
IAM
now each one of them tracks a GAM interval or a 4GBs
and we could have multiple tables etc. so we would need as much
and we could have multiple indexes
and we could have different datatypes like row_overflow LOB data or in_row each needs its own IAM page
its type is 10 and we could do the following to get the page number
create table alloaction_unit_explore(
col1 int)
insert into allocation_unit_explore(col1) values (1)
dbcc ind('TestDatabase','dbo.allocation_unit_explore',-1)
dbcc traceon(3604)
dbcc page('TestDatabase',1,735,3)
735 is our page so the output was like
PAGE: (1:735)
PAGE HEADER:
Page @0x0000020BEBBB6000
m_pageId = (1:735) m_headerVersion = 1 m_type = 10
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 258 m_indexId (AllocUnitId.idInd) = 256 Metadata: AllocUnitId = 72057594054836224
Metadata: PartitionId = 72057594047561728 Metadata: IndexId = 0
Metadata: ObjectId = 1765581328 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 90 m_slotCnt = 2 m_freeCnt = 6
m_freeData = 8182 m_reservedCnt = 0 m_lsn = (66:111696:15)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0 DB Frag ID = 1
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED PFS (1:1) = 0x70 IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL
DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED
IAM: Header @0x000000ACD57F6064 Slot 0, Offset 96
sequenceNumber = 0 status = 0x0 objectId = 0
indexId = 0 page_count = 0 start_pg = (1:0)
IAM: Single Page Allocations @0x000000ACD57F608E
Slot 0 = (0:0) Slot 1 = (0:0) Slot 2 = (0:0)
Slot 3 = (0:0) Slot 4 = (0:0) Slot 5 = (0:0)
Slot 6 = (0:0) Slot 7 = (0:0)
IAM: Extent Alloc Status Slot 1 @0x000000ACD57F60C2
(1:0) - (1:165632) = NOT ALLOCATED
(1:165640) - = ALLOCATED
(1:165648) - (1:173048) = NOT ALLOCATED
now first we see that our type is 10 which what we expected
fore more about index id check out paul randal
we don’t have next or previous IAM pages since our table was small enough to be allocated by just one
we have two slots slot 0 for:
IAM page header
it has,
- sequence number: where is it in the IAM chain is it the first the last
- status = unused
- objectid
- indexID: more on it in randal’s blog
- page_count: unused
- start page: the first page in this GAM interval that is being tracked by our IAM page
- then after that we have the array that says IAM: Single Page Allocations, now this tracks which pages has been a mixed allocation extent it is only present in the first page
after that we have our slot 1 or the slot for the bitmap, it tracks the allocation itself
Combining GAM and SGAM
now we have combinations adopted from paul randal
feel free to check them out
Data page
most of the pages that we explored in the previous blogs are data pages
feel free to check them out
Index page
now we will dive into more details on this in upcoming blogs.
Minimally logged (ML) page
it is type 17, and it is page 7
it holds information about the transactions that used bulk mode
for example:
create table ml_explore(
col1 int)
dbcc traceon(3604)
dbcc page('TestDatabase',1,7,3)
the output would be:
PAGE HEADER:
Page @0x0000020B64C26000
m_pageId = (1:7) m_headerVersion = 1 m_type = 17
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
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 = (0:0:1) m_xactReserved = 0
m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 0
DB Frag ID = 1
ML_MAP: Header @0x000000ACD15F6064 Slot 0, Offset 96
status = 0x0
ML_MAP: Extent Alloc Status @0x000000ACD15F60C2
(1:0) - (1:173048) = NOT MIN_LOGGED
now since we did not bulk anything we won’t see anything
Differential Changed Map (DIFF)
now here it shows us which extents has been changed since the last full or differential backup
for example:
create table explore_diff(
col1 int )
insert into explore_diff(col1)values(1)
dbcc traceon(3604)
dbcc page('TestDatabase',1,6,3)
the output was like:
PAGE HEADER:
Page @0x0000020B64C20000
m_pageId = (1:6) m_headerVersion = 1 m_type = 16
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
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 = (67:6904:23) m_xactReserved = 0
m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 994682355
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 @0x000000ACCA7F6064 Slot 0, Offset 96
status = 0x0
DIFF_MAP: Extent Alloc Status @0x000000ACCA7F60C2
(1:0) - (1:165704) = CHANGED
(1:165712) - (1:169840) = NOT CHANGED
(1:169848) - = CHANGED
(1:169856) - (1:173048) = NOT CHANGED
so the type is 16 and it is number 6 and it shows us which stuff has been changed as you can see in slot 0
now we are done with pages, see you in the next blog