MSSQL - A looks @ page dump for table and index pages

Objective: We will take a look at the page dump using DBCC for a table and index page (NC - non clustered)

Few notes:

File groups in MSSQL are equivalent of Oracle's Tablespace
Creating Cluster index on MSSQL physically rebuilds the table and is more like IOT in oracle.
So in MSSQL if we dont want the clustered index, better to code the primary key index as non-clustered.
Page size is 8KB in MSSSQL.

References:
https://www.red-gate.com/simple-talk/databases/sql-server/learn/oracle-to-sql-server-crossing-the-great-divide-part-3/
https://techcommunity.microsoft.com/t5/core-infrastructure-and-security/viewing-sql-server-non-clustered-index-page-contents/ba-p/370420

Table creation in MSSQL:

CREATE TABLE test_table
    (
      id INT ,
      random_data INT ,
      update_date DATE ,
      vc_small VARCHAR(10) ,
      vc_padding VARCHAR(100)
    ) ;
go
 
CREATE INDEX bt_i_rand ON test_table(random_data) ;
go
 
DECLARE @div INT = 50 ;
DECLARE @mod INT = 100 ;
DECLARE @limit INT = @div * @mod ;
DECLARE @driver INT = 1000 ;
 
WITH    generator
          AS ( SELECT   1 AS id
               UNION ALL
               SELECT   id + 1
               FROM     generator
               WHERE    id < @driver
             )
    INSERT  INTO test_table
            SELECT  id ,
                    ABS(xx % @mod) ,
                    NULL ,
                    NULL ,
                    REPLICATE('x', 100)
            FROM    ( SELECT TOP ( @limit )
                                @driver * ( g1.id - 1 ) + g2.id id ,
                                CAST(NEWID() AS VARBINARY) xx
                      FROM      generator g1
                                CROSS JOIN generator g2
                    ) iv
    OPTION  ( MAXRECURSION 0, FORCE ORDER ) ;
go

To get table space usage details:

SELECT  SUBSTRING(tab.name, 1, 16) table_name ,
        tab.object_id object_id ,
        prt.index_id index_id ,
        SUBSTRING(alu.type_desc, 1, 12) alloc_type ,
        alu.data_space_id ,
        STR(alu.total_pages, 8, 0) tot_pages ,
        STR(alu.used_pages, 8, 0) used_pages ,
        STR(alu.data_pages, 8, 0) data_pages
FROM    sys.schemas sch
        INNER JOIN sys.tables tab ON tab.schema_id = sch.schema_id
        INNER JOIN sys.partitions prt ON prt.object_id = tab.object_id
        INNER JOIN sys.allocation_units alu
                                  ON alu.container_id = prt.partition_id
WHERE   sch.name = 'DBO'
ORDER BY tab.name ,
        prt.partition_id ,
        prt.index_id ,
        alu.allocation_unit_id
go
table_name       object_id   index_id    alloc_type   data_space_id tot_pages used_pages data_pages
---------------- ----------- ----------- ------------ ------------- --------- ---------- ----------
..
test_table        1525580473           0 IN_ROW_DATA              1       81        80         79
test_table        1525580473           2 IN_ROW_DATA              1       25        18         16
(4 rows affected)
1>

Let us now assess the block or page dump in sql server 2019..

To get for a DBCC command:

DECLARE @dbcc_stmt sysname;
SET @dbcc_stmt = 'IND';
DBCC HELP (@dbcc_stmt);
GO

>>> but i didnt get any result for either IND or PAGE commands.

DBCC commands to use:

DBCC IND( database, table, index_id )
DBCC PAGE ( database, file_id, block_id, level)

Both the below command can be run from sqlcmd:

DBCC IND(testdb,test_table,0);
GO
DBCC TRACEON (3604);
GO
DBCC PAGE (testdb, 1, 4192, 3);
GO

but it is better to run the DBCC page command from SSMS to copy/paste the output easily.

Notice the following fields:

PAGE: (1:4192) <<<<<<<<< prints the fileid,pageid in the file

BUFFER Section <<<<<<<<<< A section which talks about the page in buffer

PAGE HEADER <<<<<<<<<<< The header which talks about page type, page free space info, Allocation Map (S/GAM), Allocation Unit etc.

Metadata: ObjectId = 1525580473 << the object to which this page belongs

m_slotCnt = 64 <<<< number of slots in the page (number of rows)

m_freeCnt = 32 <<<< free bytes

m_freeData = 8032 <<<< [124*64 + 96 bytes of header] [8032+32-8192 = 128, this is used for offset tracking]

Allocation status:
GAM (1:2) = ALLOCATED
SGAM (1:3) = NOT ALLOCATED
PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL <<<<<<<<<<< notice the "Page Free Space (PFS)" says this block is 100% full
DIFF (1:6) = CHANGED <<<<<<<<< This block has changed since last db backup (Differntial Changed Map - DCM) "BACKUP DATABASE" command.
ML (1:7) = NOT MIN_LOGGED <<<<<<<<<< This block indicates if this page was impacted by bulk_logged operation (Bulk Changed Map - BCM).Here it is not.

Now the actual row data info starting with slot 0:
Slot 0 Offset 0x60 Length 124
Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 124   <<<<<<<<<<<<<< 124 bytes for each record                
Memory Dump @0x00000002BB1F8060
0000000000000000:   30000f00 01000000 55000000 05010005 000c0200  0.......U...........
0000000000000014:   18007c00 78787878 78787878 78787878 78787878  ..|.xxxxxxxxxxxxxxxx
0000000000000028:   78787878 78787878 78787878 78787878 78787878  xxxxxxxxxxxxxxxxxxxx
000000000000003C:   78787878 78787878 78787878 78787878 78787878  xxxxxxxxxxxxxxxxxxxx
0000000000000050:   78787878 78787878 78787878 78787878 78787878  xxxxxxxxxxxxxxxxxxxx
0000000000000064:   78787878 78787878 78787878 78787878 78787878  xxxxxxxxxxxxxxxxxxxx
0000000000000078:   78787878                                      xxxx   
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
id = 1                              
Slot 0 Column 2 Offset 0x8 Length 4 Length (physical) 4
random_data = 85                    
Slot 0 Column 3 Offset 0x0 Length 0 Length (physical) 0
update_date = [NULL]                
Slot 0 Column 4 Offset 0x0 Length 0 Length (physical) 0
vc_small = [NULL]            <<<<<<<<<<< notice this is null, we are going to update this value and see how the page looks later.       
Slot 0 Column 5 Offset 0x18 Length 100 Length (physical) 100
vc_padding = xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
... this repeats until slot 64.

>>>>>> There is no information at the tail part. Just that slot 63 finished dumping info.

Now let us look at the index page the same way:

DBCC IND(testdb,test_table,2);
GO
DBCC TRACEON (3604);
GO
DBCC PAGE (testdb, 1, 4192, 3);
GO

Command:

DBCC IND(testdb,test_table,2);
GO

Output:

PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID     PartitionNumber PartitionID          iam_chain_type       PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
      1         333   NULL        NULL  1525580473           2               1    72057594043367424 In-row data                10       NULL           0           0           0           0
      1        4200      1         333  1525580473           2               1    72057594043367424 In-row data                 2          0           1        4235           0           0
      1        4201      1         333  1525580473           2               1    72057594043367424 In-row data                 2          1           0           0           0           0
      1        4202      1         333  1525580473           2               1    72057594043367424 In-row data                 2          0           1        4272           1        4238

The blocks were ranging from 4200 to 4272, there were 18 used pages of which 16 are data pages.
We pick 4200 to dump the page info..

DBCC PAGE (testdb, 1, 4200, 3);
GO
Ex. Output:

FileId PageId Row Level random_data (key) HEAP RID (key) KeyHashValue Row Size
1 4200 0 0 0 0x6010000001001E00 (b490a6c8ceeb) 16
1 4200 1 0 0 0x6010000001002800 (2054aeabf8c9) 16
1 4200 2 0 0 0x6010000001003700 (10dc288b0cc5) 16
1 4200 3 0 0 0x6210000001000A00 (942fbc405eeb) 16

Notice the column header "random_data (key)" - the column on which we built the index.

Let us break this number:
0x6210000001000A00
62 10 00 00 01 00 0A 00

I can say "0A" is a slot identifier, but cant confirm on the page id yet!

I will check further on how this leaf node index entry finds its table page & slot.

Additional info:

Running the DBCC with level 2 trace will dump the information like table's.

DBCC PAGE (testdb, 1, 4200, 2);
GO

Ex. Output:

BUFFER:

BUF @0x000001058847FE40
...
PAGE HEADER:
..
m_pageId = (1:4200) <<< fileid:pageid
Metadata: IndexId = 2  <<<< non clustered index (in case of clustered index, it will be 1)
Metadata: ObjectId = 1525580473 <<< the table object id
m_slotCnt = 315 <<< number of leaf entries or index entries
m_freeCnt = 2426 <<< Free bytes
m_freeData = 5136 <<< 315*16 + 96 = 5136
                 
Allocation Status
GAM (1:2) = ALLOCATED
SGAM (1:3) = NOT ALLOCATED
PFS (1:1) = 0x40 ALLOCATED   0_PCT_FULL  <<< this block is still free
DIFF (1:6) = CHANGED <<< block changed since last backup
ML (1:7) = NOT MIN_LOGGED           
DATA:
Memory Dump @0x00000002BF5F8000
00000002BF5F8000:   01020000 00020001 00000000 00000d00 8b100000  ................‹...
00000002BF5F8014:   01003b01 b6000000 7a091014 68100000 01000000  ..;.¶...z ..h.......
00000002BF5F8028:   74000000 46040000 a4000000 00000000 00000000  t...F...¤...........

Hope the blog helps!

Thanks

MSSQL - Pages and Extents Architecture Notes

MSSQL pages & extents notes

Pages - The fundamental unit of storage in mssql.

Extents - Contigous 8 blocks or pages form 1 extent. The extents simplify page management.

Page size default: 8KB

Pages have header - storing page type (data or index or large object - lob, PFS - Page Free Space, shared/global allocation map , type of allocation unit (IAM), block change info after log backup & database backup), amount of free space in the page, allocation unit id of the object owning it.

Row offsets are used to identify the position of the row in a page.

Rows cant span multiple pages.

ROW_OVERFLOW_DATA allocation unit is used to support tables with row size more than 8060 bytes, the address to ROW_OVERFLOW_DATA is kept in IN_ROW_DATA allocation unit.

The individual column size cant exceed 8K bytes. Only combined sizes of columns in a table can exceed 8K bytes and become eligible for this ROW_OVERFLOW_DATA and IN_ROW_DATA operation.

It is recommended to avoid ROW_OVERFLOW_DATA columns in clustered key indexes; otherwise further inser/update operations could fail. In nonclustered no issues.

Extents: MSSQL allocates and deallocates storage space using extents.

Uniform and mixed are 2 types of extents. Uniform only one object owns the extent, but with multiple several; upto 8 per extent 1 page per object at max can own a extent.

From SQL Server 2016, user and tempdb by default are allocated uniform extents only.

Functions : sys.dm_db_database_page_allocations - provides information about page allocation vs  sys.dm_db_page_info - provides information about pages

Manage Extent allocation: (To retrieve the information about weather a extent is free or not & allocate them on demand)

Uniform extents use - GAM (Global Allocation Map) ; free is set to 1 vs otherwise set to 0

Mixed extents use - SGAM (Shared Global Allocation Map) ; used as SGAM and it has free page, hence it is set to 1 vs otherwise set to 0 (not free or not SGAM)

Page Free Space (PFS) - page maintains the free space information about other pages like weather it is allocated, % of free space. A single PFS can track 8088 pages, so after 8088 pages a new PFS will be allocated.

DB engine manage space in a file using the below sequence of allocation in an extent:

Page 0 -> File Header

Page 1 -> PFS

Page 2 -> GAM

Page 3 -> SGAM

etc...

To manage space used by object: An object has 3 types of allocations; the allocations are managed using Index Allocation Mapping.

Index Allocation Mapping (IAM): 3 types of allocation unit, 1. IN_ROW_DATA, 2. ROW_OVERFLOW_DATA, 3. LOB_DATA.

As the name implies, IN_ROW_DATA stores the heap data. The overflow data of a heap is kept in ROW_OVERFLOW_DATA and large objects of a heap is kept in LOB_DATA.

Note a single IAM page supports 4GB worth of extents. Every file per allocation unit has 1 IAM or if the AU size is more than 4GB file, then a new IAM page will be created.

IAM pages are randomly located, but they are chained. Information about IAM can be found in sys.system_internals_allocation_units view.

Thank you!