DBCC PAGE output analysis

0

I created the following table

USE [Test]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Table_3]
(
    [id] [int] NOT NULL,
    [itemcode] [int] NOT NULL,
    [value] [decimal](18, 5) NOT NULL,
    [date] [date] NOT NULL,
    [gain] [decimal](18, 5) NULL,
    [loss] [decimal](18, 5) NULL
) ON [PRIMARY]
GO

insert into dbo.table_3 
values (1,1,1000.0,'28-Feb-2018',null,null)


DBCC IND ('Test','Table_3',-1)
GO

The output of DBCC IND command above gave two rows

PageFID PagePID IAMFID  IAMPID  ObjectID    IndexID PartitionNumber PartitionID iam_chain_type  PageType    IndexLevel  NextPageFID NextPagePID PrevPageFID PrevPagePID
1   297 NULL    NULL    581577110   0   1   72057594040877056   In-row data 10  NULL    0   0   0   0
1   296 1   297 581577110   0   1   72057594040877056   In-row data 1   0   0   0   0   0

Then I used the above output to get page details

DBCC TRACEON (3604)
DBCC PAGE ('Test',1,297,1)
GO

The output of the same is

PAGE: (1:297) BUFFER: BUF @0x04797150 bpage = 0x07D74000 bhash = 0x00000000
bpageno = (1:297) bdbid = 9 breferences = 0 bcputicks = 0 bsampleCount = 0 bUse1 = 26039
bstat = 0x10b blog = 0x15adb21c bnext = 0x00000000
PAGE HEADER: Page @0x07D74000 m_pageId = (1:297) m_headerVersion = 1
m_type = 10 m_typeFlagBits = 0x0 m_level = 0
m_flagBits = 0x0 m_objId (AllocUnitId.idObj) = 125 m_indexId (AllocUnitId.idInd) = 256 Metadata: AllocUnitId = 72057594046119936
Metadata: PartitionId = 72057594040877056
Metadata: IndexId = 0 Metadata: ObjectId = 581577110 m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 90
m_slotCnt = 2 m_freeCnt = 6 m_freeData = 8182
m_reservedCnt = 0 m_lsn = (48:296:16) 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

DATA: Slot 0, Offset 0x60, Length 94, DumpStyle BYTE Record Type = PRIMARY_RECORD Record Attributes =
Record Size = 94 Memory Dump @0x0D52C060 00000000: 00005e00 00000000 00000000 00000000 00000000 ..^................. 00000014: 00000000 00000000 00000000 00000000 00000000 .................... 00000028: 00000000 01002801 00000100 00000000 00000000 ......(............. 0000003C: 00000000 00000000 00000000 00000000 00000000 .................... 00000050: 00000000 00000000 00000000 0000 ..............

Slot 1, Offset 0xbe, Length 7992, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes =
Record Size = 7992

Memory Dump @0x0D52C0BE

If there is just one row in the table, how can it have two Slots (Slot 0 and Slot 1, I have not pasted the text for slot 1 as it is huge) ? Since it is a table with 5 fixed data type columns, the size I expect is 2*4 (int)+ 1*3(date) +3*9 (decimal (18,5)) = 38 bytes but the Record Size mentions 94, am I misreading it ?

Follow up question

Based on Dan's comment I have changed the page id and selected 296 this time.

Here is the output of DBCC PAGE ('Test',1,296,1)

Slot 0, Offset 0x60, Length 45, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Record Size = 45

Memory Dump @0x0CC1C060

00000000: 10002a00 01000000 01000000 0100e1f5 05000000 ..*...........áõ.... 00000014: 00f03d0b 00000100 01000000 1c000000 60a29f07 .ð=.............`¢. 00000028: d8660600 30
Øf..0

Shouldn't come out to be 38 (as per my understanding explained above) instead of 45 ?

sql-server
asked on Stack Overflow Mar 10, 2018 by Gsab • edited Mar 11, 2018 by Gsab

1 Answer

0

Record header (4 bytes long)

-2 bytes of record metadata 
-2 bytes pointer to null bitmap

Fixed length data (38 bytes long)

-8 bytes per int
-3 bytes per date
-27 bytes per decimal

Null bitmap (3 bytes long)

-2 bytes for count of columns in the record
-1 byte to store one bit per column in the record

Sum is 45 bytes

answered on Stack Overflow Nov 13, 2018 by Piotr Wojnarski

User contributions licensed under CC BY-SA 3.0