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