Understand SQL Pages
In Sql server the pages are fundamental of unit of data storage. In pages the data is stored in a row format.The sql server page size is 8kb.SQL server engine manages the allocation and deallocation of the pages. It organizes in hierarchical structure. Once page is filled SQL server engine will allocate new page from extents(group 8 contiguous pages are called extents).
There are many types of pages in SQL. We are going to discuss the most commonly used ones.
- Data Pages
- Index Pages
- IAM pages
Page Structure :
Page Header : Page header stores information about the page like page type, next and previous page if it’s an index page for better searching, free space in the page etc.
Data Row : Data will be stored
Row Offset: It contains all datarows information about how far the row is from the start of the page. Every data row has a row offset and the size of row offset is 2 bytes per row.
Once the row is completely filled SQL engine allocates new partition and maps IAM chain type/Allocation types based on data.
In Sql there are three types of row allocations.
- In-Row Data
- Row-Overflow Data
- Lob Data
We can view these by executing following command.
DBCC IND('mydb','table',1)
In-Row data :
When the row size stays within the 8,060-byte limit, SQL Server stores all of the data in the IN_ROW_DATA.
Row-overflow data :
This allocation unit stores variable-length column data that exceeds the maximum row size limit.
LOB data :
If a column with LOB data type is defined, then SQL Server uses the LOB_DATA allocation unit. LOB datatypes are VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), XML, IMAGE etc.
Note : Read more about SQL Row allocations here https://madithatimaheswarreddy.medium.com/sql-row-allocations-0780098d08eb
Note : When we do changes for data the data pages will be updated unless they are not mapped to indexs.If the column is mapped to indexs it will also update index pages.
To check the page structure run the following command.
DBCC traceon(3604)
DBCC PAGE ('DataBaseName', 1, PagePID, 3);In the above script we can get the PagePID from “DBCC IND(‘mydb’,’table’,1)”. By executin the script we can get the page structure details as following. Please refer the above page structre diagram for better understanding.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
PAGE: (1:376)
BUFFER:
BUF @0x0000000C52EB8100
bpage = 0x0000000E93AD8000 bPmmpage = 0x0000000000000000 bsort_r_nextbP = 0x0000000000000000
bsort_r_prevbP = 0x0000000000000000 bhash = 0x0000000000000000 bpageno = (1:376)
bpart = 4 ckptGen = 0x0000000000000000 bDirtyRefCount = 0
bstat = 0x9 breferences = 0 berrcode = 0
bUse1 = 20832 bstat2 = 0x0 blog = 0x15a
bsampleCount = 0 bIoCount = 0 resPoolId = 0
bcputicks = 0 bReadMicroSec = 567 bDirtyContext = 0x0000000000000000
bDbPageBroker = 0x0000000000000000 bdbid = 13 bpru = 0x0000000E81B20040
PAGE HEADER:
Page @0x0000000E93AD8000
m_pageId = (1:376) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 183 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594049921024
Metadata: PartitionId = 72057594043432960 Metadata: IndexId = 1
Metadata: ObjectId = 613577224 m_prevPage = (0:0) m_nextPage = (1:378)
pminlen = 8 m_slotCnt = 4 m_freeCnt = 7969
m_freeData = 283 m_reservedCnt = 0 m_lsn = (37:3784:23)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 133531341 DB Frag ID = 1
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x40 ALLOCATED 0_PCT_FULL
DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED
Slot 0 Offset 0xa4 Length 35
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 35
Memory Dump @0x0000000A0BF880A4
0000000000000000: 30000800 01000000 03000002 001a0023 0042616e 0..............#.Ban
0000000000000014: 67616c6f 72654261 6e67616c 6f7265 galoreBangalore
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
Id = 1
Slot 0 Column 67108865 Offset 0x11 Length 0 Length (physical) 9
DROPPED = NULL
Location = [BLOB Inline Data] Slot 0 Column 2 Offset 0x1a Length 9 Length (physical) 9
0000000A0BF7E890: 42616e67 616c6f72 65 Bangalore
Slot 0 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (8194443284a0)
Slot 1 Offset 0xc7 Length 27
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 27
Memory Dump @0x0000000A0BF880C7
0000000000000000: 30000800 02000000 03000002 0016001b 0044656c 0................Del
0000000000000014: 68694465 6c6869 hiDelhi
Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4
Id = 2
Slot 1 Column 67108865 Offset 0x11 Length 0 Length (physical) 5
DROPPED = NULL
Location = [BLOB Inline Data] Slot 1 Column 2 Offset 0x16 Length 5 Length (physical) 5
Location = 0x44656c6869
Slot 1 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (61a06abd401c)
Slot 2 Offset 0xe2 Length 35
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 35
Memory Dump @0x0000000A0BF880E2
0000000000000000: 30000800 03000000 03000002 001a0023 00487964 0..............#.Hyd
0000000000000014: 65726162 61644879 64657261 626164 erabadHyderabad
Slot 2 Column 1 Offset 0x4 Length 4 Length (physical) 4
Id = 3
Slot 2 Column 67108865 Offset 0x11 Length 0 Length (physical) 9
DROPPED = NULL
Location = [BLOB Inline Data] Slot 2 Column 2 Offset 0x1a Length 9 Length (physical) 9
0000000A0BF7E890: 48796465 72616261 64 Hyderabad
Slot 2 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (98ec012aa510)
Slot 3 Offset 0x105 Length 22
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 22
Memory Dump @0x0000000A0BF88105
0000000000000000: 30000800 04000000 03000202 00110016 00636865 0................che
0000000000000014: 636b ck
Slot 3 Column 1 Offset 0x4 Length 4 Length (physical) 4
Id = 4
Slot 3 Column 67108865 Offset 0x0 Length 0 Length (physical) 0
DROPPED = [NULL]
Location = [BLOB Inline Data] Slot 3 Column 2 Offset 0x11 Length 5 Length (physical) 5
Location = 0x636865636b
Slot 3 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (a0c936a3c965)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Completion time: 2023-06-15T21:46:52.8611556+05:30For better UI experience or readability run the following query.
DBCC PAGE ('databasename', 1, PagePID, 3) WITH TABLERESULTS