Review SQL Server Data Page via Undocumented DBCC Command

Review SQL Server Data Page via Undocumented DBCC Command

By : Kasim Wirama, MCITP, MCDBA

 

Table in SQL Server is composed from one or more data and index page. You can examine where and how your data is located in physical disk and in-memory buffer by looking into undocumented DBCC command, namely DBCC PAGE. You won’t find the command description in SQL Server Books Online.

 

DBCC PAGE command gives you information about the data page relating to a table into 3 sections, which are page header, data row and row offset table. Here is the command syntax:

 

DBCC PAGE ( database id or database name, filenumber, page number, print option)

 

Available values for print option are :

0 – this option will print only buffer header and page header.

1 – this option will print buffer header, page header, data row separately (slot), and row offset table.

2 – similar to option 1 but data row will be printed as a whole rather than separated format.

3 – this option will print buffer header and page header, and data row will be printed separately followed by column values listed separately.

 

Only sysadmin level could execute the command and before issuing the command, you need to turn on flag 3604 by executing DBCC TRACEON (3604);

 

Let’s have a look an example here :

Use AdventureWorks2008

GO

DBCC TRACEON (3604);

GO

DBCC PAGE (AdventureWorks2008, 1, 12136, 1);

 

12136 shows data from table Sales.SalesOrderHeader. I would give other posting on how to locate your data into page in a database.

 

The result is shown below :

 

PAGE: (1:12136)

 

BUFFER:

 

BUF @0x039C9950

 

bpage = 0x06F08000 bhash = 0x00000000 bpageno = (1:12136)

 

bdbid = 8 breferences = 0 bUse1 = 24630

 

bstat = 0x1c00009 blog = 0x21212159 bnext = 0x00000000

 

PAGE HEADER:

 

Page @0x06F08000

 

m_pageId = (1:12136) m_headerVersion = 1 m_type = 1

 

m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x200

 

m_objId (AllocUnitId.idObj) = 172 m_indexId (AllocUnitId.idInd) = 256

 

Metadata: AllocUnitId = 72057594049200128

 

Metadata: PartitionId = 72057594046775296 Metadata: IndexId = 1

 

Metadata: ObjectId = 1010102639 m_prevPage = (0:0) m_nextPage = (1:12137)

 

pminlen = 115 m_slotCnt = 41 m_freeCnt = 163

 

m_freeData = 7947 m_reservedCnt = 0 m_lsn = (214:3256:467)

 

m_xactReserved = 0 m_xdesId = (0:1409) m_ghostRecCnt = 0

 

m_tornBits = 1778459904

 

Allocation Status

 

GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED

 

PFS (1:8088) = 0x40 ALLOCATED 0_PCT_FULL DIFF (1:6) = NOT CHANGED

 

ML (1:7) = NOT MIN_LOGGED

 

DATA:

 

 

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

 

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

 

Record Size = 191

 

Memory Dump @0x62E9C060

 

00000000: 30007300 8baa0000 07000000 00cf9000 0.s..........

 

00000010: 00000000 00db9000 00000000 00d69000 ..............

 

00000020: 00059081 74000017 01000005 000000d9 ....t..........

 

00000030: 030000d9 03000005 00000099 3f0000b7 ...........?..

 

00000040: ebc3a08e 10420c00 0000004d d42c0100 .B.....M,..

 

00000050: 00000058 025e0000 00000021 53b679ca ...X.^.....!Sy

 

00000060: 3915419c ba8fe090 3e12e600 000000d6 9.A..>.....

 

00000070: 90000018 00000022 03009600 b200bf00 .......".....

 

00000080: 50004f00 35003200 32003100 34003500 P.O.5.2.2.1.4.5.

 

00000090: 37003800 37003100 30002d00 34003000 7.8.7.1.0.-.4.0.

 

000000A0: 32003000 2d003000 30003000 36003700 2.0.-.0.0.0.6.7.

 

000000B0: 36003130 35303431 56693834 3138326.105041Vi84182

 

Slot 1, Offset 0x11f, Length 195, DumpStyle BYTE

 

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

 

Record Size = 195

 

Memory Dump @0x62E9C11F

 

00000000: 30007300 8caa0000 05000000 00cf9000 0.s..........

 

00000010: 00000000 00db9000 00000000 00d69000 ..............

 

00000020: 000590e8 73000017 01000005 00000099 ...s...........

 

00000030: 03000099 03000005 000000f2 150000b7 ..............

 

00000040: ebc3a0c1 7cc50000 00000073 f5120000 |.....s...

 

00000050: 000000b4 ec050000 0000002d c48d733b .........-.s;

 

00000060: d0a14898 22f95a67 ea738900 000000d6 H."Zgs....

 

00000070: 90000018 00000022 03009a00 b600c300 .......".....

 

00000080: 50004f00 31003800 38003500 30003100 P.O.1.8.8.5.0.1.

 

00000090: 32003700 35003000 30003100 30002d00 2.7.5.0.0.1.0.-.

 

000000A0: 34003000 32003000 2d003000 30003000 4.0.2.0.-.0.0.0.

 

000000B0: 31003100 37003131 35323133 56693239 1.1.7.115213Vi29

 

000000C0: 343131411

 

....

 

Slot 40, Offset 0x1e62, Length 169, DumpStyle BYTE

 

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

 

Record Size = 169

 

Memory Dump @0x62E9DE62

 

00000000: 30007300 b3aa0000 05000000 00cf9000 0.s..........

 

00000010: 00000000 00db9000 00000000 00d69000 ..............

 

00000020: 00059107 65000017 01000001 0000009d ...e...........

 

00000030: 4c00009d 4c000001 000000a4 16000008 L...L..........

 

00000040: 0000001c cc060200 000000f8 80290000 ...........)..

 

00000050: 0000004e f80c0000 000000bd 48b39900 ...N......H..

 

00000060: 773e4da5 314540c8 75a2bd00 000000d6 w>M1E@u....

 

00000070: 90000018 00800422 03008000 9c00a900 ......."......

 

00000080: 31003000 2d003400 30003300 30002d00 1.0.-.4.0.3.0.-.

 

00000090: 30003200 35003800 36003300 33333333 0.2.5.8.6.3.3333

 

000000A0: 37365669 33303234 3476Vi30244

 

OFFSET TABLE:

 

Row - Offset

 

40 (0x28) - 7778 (0x1e62)

 

39 (0x27) - 7608 (0x1db8)

 

38 (0x26) - 7439 (0x1d0f)

 

37 (0x25) - 7248 (0x1c50)

 

36 (0x24) - 7053 (0x1b8d)

 

35 (0x23) - 6860 (0x1acc)

 

34 (0x22) - 6667 (0x1a0b)

 

33 (0x21) - 6474 (0x194a)

 

32 (0x20) - 6283 (0x188b)

 

31 (0x1f) - 6091 (0x17cb)

 

30 (0x1e) - 5899 (0x170b)

 

29 (0x1d) - 5707 (0x164b)

 

28 (0x1c) - 5514 (0x158a)

 

27 (0x1b) - 5322 (0x14ca)

 

26 (0x1a) - 5129 (0x1409)

 

25 (0x19) - 4938 (0x134a)

 

24 (0x18) - 4746 (0x128a)

 

23 (0x17) - 4556 (0x11cc)

 

22 (0x16) - 4364 (0x110c)

 

21 (0x15) - 4169 (0x1049)

 

20 (0x14) - 3974 (0xf86)

 

19 (0x13) - 3780 (0xec4)

 

18 (0x12) - 3586 (0xe02)

 

17 (0x11) - 3392 (0xd40)

 

16 (0x10) - 3197 (0xc7d)

 

15 (0xf) - 3003 (0xbbb)

 

14 (0xe) - 2810 (0xafa)

 

13 (0xd) - 2616 (0xa38)

 

12 (0xc) - 2422 (0x976)

 

11 (0xb) - 2227 (0x8b3)

 

10 (0xa) - 2033 (0x7f1)

 

9 (0x9) - 1840 (0x730)

 

8 (0x8) - 1646 (0x66e)

 

7 (0x7) - 1451 (0x5ab)

 

6 (0x6) - 1257 (0x4e9)

 

5 (0x5) - 1064 (0x428)

 

4 (0x4) - 870 (0x366)

 

3 (0x3) - 675 (0x2a3)

 

2 (0x2) - 482 (0x1e2)

 

1 (0x1) - 287 (0x11f)

 

0 (0x0) - 96 (0x60)

 

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

As you see, there are 4 main sections of the output, i.e.: BUFFER, PAGE HEADER, DATA SLOT, and OFFSET TABLE.

 

BUFFER section gives information about how your data is stored in in-memory buffer.

 

PAGE HEADER section displays information about the page, with detail below :

·         Page id

It shows file number and page number in related database.

·         nextPage

the next page after current page.

·         prevPage

the previous page before current page.

·         Metadata:ObjectId

This shows whose object (usually table) it belongs to

·         Metadata:PartitionId

It shows partition id (in sys.partitions) where this page is located in.

·         Metadata:AllocUnitId

It shows allocation unit inside partition id for the page.

·         LSN

It shows recent LSN (entry in transaction log) that changes the page.

·         slotCnt

it shows number of rows in the page

·         Level

It shows level of the page in index structure (0 means leaf page)

·         IndexId

Index id for the page. If it is data page then IndexId = 0

·         freeData

it is byte offset for first free space on the page.

·         Pminlen

It is number of bytes of fixed length portion of rows

·         freeCnt

it is number of free byte.

·         reservedCnt

it is number of bytes reserved for all transaction.

·         Xactreserved

It is number of bytes reserved for recently started transaction.

·         tornBits

it shows torn page number for torn page detection. If torn page detection is turned off then it is used for checksum verification.

·         flagBits

it is 2-byte bitmap showing additional information about the page.

 

 

DATA section contains data on the table. It is divided into 3 parts, i.e.: left item (before colon) shows position (in byte) of the related row (slot). Second item shows actual data stored in 4 of 8 hexadecimal digits. And third item shows data in ASCII (readable) format.

 

OFFSET TABLE section shows contents of row offset for each slot, with first row (0) until last row (40). The first row is in slot 0 with smallest offset (96). Logical ordering (based on slot number) is not always in line with physical ordering (actual row stored in disk, shown by row offset).

Share this post: | | | |
Published Sunday, December 20, 2009 6:52 PM by Kasim.Wirama
Filed under:

Comments

# Looking Inside SQL Server Data Row

Tuesday, December 22, 2009 6:43 PM by SQL Server knowledge center

Looking Inside SQL Server Data Row By : Kasim Wirama, MCITP, MCDBA If you have read my 2 previous postings

Copyright © INDC, 2006. All rights reserved.
Powered by Community Server (Commercial Edition), by Telligent Systems