Some neat buffer design information from SQL Server

[GavinRay97]

I discovered there is a command in SQL Server you can use to get a memory dump + statistics on buffers in SQL Server
It's similar to the one that Postgres gives you, but more in depth about the layout and design of the underlying architecture + in-memory representation I think:

You can do this with:

DBCC TRACEON (3604);

GO

-- User the name of some DB here
-- `1` is the file ID, 662 is the page ID (you will have to play around to get a good page) 
DBCC PAGE (master, 1, 662, 3); 

GO

Here is a buffer dump that corresponds to a table with the definition:

CREATE TABLE [Invoice]
(
    [InvoiceId] INT NOT NULL IDENTITY,
    [CustomerId] INT NOT NULL,
    [InvoiceDate] DATETIME NOT NULL,
    [BillingAddress] NVARCHAR(70),
    [BillingCity] NVARCHAR(40),
    [BillingState] NVARCHAR(40),
    [BillingCountry] NVARCHAR(40),
    [BillingPostalCode] NVARCHAR(10),
    [Total] NUMERIC(10,2) NOT NULL,
    CONSTRAINT [PK_Invoice] PRIMARY KEY  ([InvoiceId])
);

Some interesting things I noticed:

• prev and next pages aren't successive, even though this particular data was data created in one INSERT transaction.
• The overall design is very similar to what we learn from Andy's lectures! You're right at home if you know the terminology.
• Anyone have a guess at what -335103495 means for torn bits? Is this a min value used as a toggle state maybe?

PAGE: (1:662)

BUFFER:

	BUF @0x000001E17092EC40

	bpage = 0x000001DE6301E000          bPmmpage = 0x0000000000000000       bsort_r_nextbP = 0x0000000000000000
	bsort_r_prevbP = 0x0000000000000000 bhash = 0x0000000000000000          bpageno = (1:662)
	bpart = 12                          bstat = 0x109                       breferences = 0
	berrcode = 0                        bUse1 = 59338                       bstat2 = 0x0
	blog = 0x1cc7a7a                    bsampleCount = 0                    bIoCount = 0
	resPoolId = 0                       bcputicks = 0                       bReadMicroSec = 828
	bDirtyPendingCount = 0              bDirtyContext = 0x0000000000000000  bDbPageBroker = 0x0000000000000000
	bdbid = 1                           bpru = 0x000001DE313A0040           

PAGE HEADER:

	Page @0x000001DE6301E000

	m_pageId = (1:662)                  m_headerVersion = 1                 m_type = 1
	m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x2200
	m_objId (AllocUnitId.idObj) = 269   m_indexId (AllocUnitId.idInd) = 256 Metadata: AllocUnitId = 72057594055557120
	Metadata: PartitionId = 72057594047496192                                Metadata: IndexId = 1
	Metadata: ObjectId = 759673754      m_prevPage = (1:657)                m_nextPage = (1:663)
	pminlen = 29                        m_slotCnt = 59                      m_freeCnt = 83
	m_freeData = 7991                   m_reservedCnt = 0                   m_lsn = (299:232:13)
	m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
	m_tornBits = -335103495             DB Frag ID = 1                      

Allocation Status

	GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL
	DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED           

Slot 0 Offset 0x60 Length 119

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFO
Record Size = 119                   

Memory Dump @0x000000488C3F6060

0000000000000000:   70001d00 3c000000 17000000 00000000 819c0000  p...<............�..
0000000000000014:   017b0300 00000000 00090000 0005004b 0057005b  .{.......	.....K.W.[
0000000000000028:   00610069 00360039 00200053 0061006c 0065006d  .a.i.6.9. .S.a.l.e.m
000000000000003C:   00200053 00740072 00650065 00740042 006f0073  . .S.t.r.e.e.t.B.o.s
0000000000000050:   0074006f 006e004d 00410055 00530041 00320031  .t.o.n.M.A.U.S.A.2.1
0000000000000064:   00310033 00000000 00000000 00483c00 000000    .1.3.........H<....

Version Information = 
	Transaction Timestamp: 15432
	Version Pointer: Null

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
InvoiceId = 60                      

Slot 0 Column 2 Offset 0x8 Length 4 Length (physical) 4
CustomerId = 23                     

Slot 0 Column 3 Offset 0xc Length 8 Length (physical) 8
InvoiceDate = 2009-09-11 00:00:00.000                                    

Slot 0 Column 4 Offset 0x2d Length 30 Length (physical) 30
BillingAddress = 69 Salem Street    

Slot 0 Column 5 Offset 0x4b Length 12 Length (physical) 12
BillingCity = Boston                

Slot 0 Column 6 Offset 0x57 Length 4 Length (physical) 4
BillingState = MA                   

Slot 0 Column 7 Offset 0x5b Length 6 Length (physical) 6
BillingCountry = USA                

Slot 0 Column 8 Offset 0x61 Length 8 Length (physical) 8
BillingPostalCode = 2113            

Slot 0 Column 9 Offset 0x14 Length 9 Length (physical) 9
Total = 8.91                        

Slot 0 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (88011ddc4767)       

Pretty neat if anyone else is interested in this stuff

[skyzh]

Looks good. I'm planning an MVCC storage + txn manager for the next semester, and probably I will do more work on representing page layout...

[GavinRay97]

I thought it also might be useful for class/helping students too, to give them a visualization of what database pages look like
(I learn best by seeing/visualizing things)

In the Storage lectures, we see the slides showing the Slotted Page + tuple layouts, this could be a good way to "connect the dots" from that lecture to the real world/give an example 😃

Also similar concept, Kaitai Struct grammar + visualizer tool from other issue:

• Suggestion: Give students Kaitai Struct grammar for viewing binary page data as structured object trees #263