Using DBCC PAGE to view Row Details

· by · in

SQL Server stores database data on 8KB pages. The details around looking at page contents are a pretty advanced subject. However, understanding some brief details about the page structure via the output of DBCC PAGE can be very useful. In this post I show how to display the output of all data pages in a given table. We’ll also see a sample page output, and identify the major sections of that output.

A Party, by Pieter de Hooch, 1675. Almost as much fun as looking at DBCC PAGE output!

A Party, by Pieter de Hooch, 1675. Almost as much fun as looking at DBCC PAGE output!

To do this, we need some pages, so we’ll create a simple table with a couple of columns.

We’ll populate it with a sample row:

We can now use DBCC PAGE to look at the table data. In the DBCC PAGE command, we’re requesting the print option 3. The various options for this parameter are:

0 - print just the page header
1 - page header plus per-row hex dumps and a dump of the page slot array (unless its a page that doesn't have one, like allocation bitmaps)
2 - page header plus whole page hex dump
3 - page header plus detailed per-row interpretation

The query below uses a cursor to iterate over all the DATA_PAGES in the table:

The DBCC TRACEON(3604); is required to direct the output from DBCC commands to the console, instead of the error log.

We’re using the undocumented sys.dm_db_database_page_allocations dynamic management view to obtain the list of pages where the table is storing data; I’ve documented what I know about it here.

Output from the code above looks like this:

PAGE: (1:100112)


BUFFER:


BUF @0x00000217946760C0

bpage = 0x0000021A2DEA6000          bhash = 0x0000000000000000          bpageno = (1:100112)
bdbid = 10                          breferences = 1                     bcputicks = 0
bsampleCount = 0                    bUse1 = 20058                       bstat = 0x10b
blog = 0x212121cc                   bnext = 0x0000000000000000          bDirtyContext = 0x000002175004A530
bstat2 = 0x0                        

PAGE HEADER:


Page @0x0000021A2DEA6000

m_pageId = (1:100112)               m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 301   m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594057654272                                
Metadata: PartitionId = 72057594051821568                                Metadata: IndexId = 0
Metadata: ObjectId = 206623779      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 16                        m_slotCnt = 1                       m_freeCnt = 7936
m_freeData = 254                    m_reservedCnt = 0                   m_lsn = (616:14229:25)
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) = NOT ALLOCATED          
PFS (1:97056) = 0x41 ALLOCATED  50_PCT_FULL                              DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED           

Slot 0 Offset 0x60 Length 158

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 158                   
Memory Dump @0x000000B75227A060

0000000000000000:   30001000 1726a001 30e51700 03660000 03000001  0....& .0å...f......
0000000000000014:   009e0057 68656e20 77652066 6f756e64 2068696d  .ž.When we found him
0000000000000028:   20646561 642c2068 65206861 64206120 6c697474   dead, he had a litt
000000000000003C:   6c652066 6f616d20 6f6e2068 6973206e 6f73652c  le foam on his nose,
0000000000000050:   20616e64 20736f6d 6520626c 6f6f6420 746f6f2c   and some blood too,
0000000000000064:   20616e64 20746865 20646f63 746f7220 73616964   and the doctor said
0000000000000078:   2c202254 68617420 6d757374 20626520 616e206f  , "That must be an o
000000000000008C:   76657264 6f736520 6f662068 65726f69 6e22      verdose of heroin"

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

Column1 = 27272727                  

Slot 0 Column 2 Offset 0x17 Length 135 Length (physical) 135

Column2 = When we found him dead, he had a little foam on his nose, and some blood too, and the doctor said, "Tha
t must be an overdose of heroin"    

Slot 0 Column 3 Offset 0x8 Length 8 Length (physical) 8

Column3 = 1971-07-03 01:27:00.000

This section indicates the file number where the page exists, and the page number itself. The file number is 1, and the page number is 100112.

PAGE: (1:100112)

The BUFFER: section indicates the buffer page number (bpage) which represents exactly where in the buffer pool the page exists. Also included are:

  1. bhash – The hash of the page
  2. bpageno – the file and page number
  3. bdbid – the database ID
  4. breferences – the number of processes that have referenced the buffer page.
  5. bcputicks – the amount of time latches have waited to access the page
  6. bsampleCount – the number of times SQL Server has sampled the latch wait time for this page
  7. bUse1 – the duration in seconds since the bcputicks and bsampleCount counters have been reset. This is perhaps a reasonable proxy for the amount of time the page has been in the buffer pool.
  8. bstat, bstat2 – the status of the buffer page.
  9. blog – the transaction log record where the page was last modified
  10. bnext – the address of the next page in the buffer pool for this object
  11. bDirtyContext – I’m guessing, but this looks like a pointer to the memory structure used by SQL Server’s storage engine to keep track of dirty pages.

Paul Randal has an excellent post about the anatomy of the PAGE HEADER section here. For our purposes, the following bits are interesting:

  1. m_slotCnt – this is the “slot count”, which is the number of rows stored on the page.

The allocation status section shows which allocation maps show the page as allocated or free. In our case, the Global Allocation Map, or GAM, shows the page as allocated. This is normal for data pages. The Shared Global Allocation Map, or SGAM, shows the page as “not allocated”, which indicates the page is either part of a mixed-extent that has no free pages, or the page is part of a dedicated extent. Those values are also well documented by Paul Randal here.

The next section contains a raw memory dump of the portion of the page containing valid information. The format consists of one row for each consecutive 20 bytes on the page, and includes the page header details, along with the row data. You can see the contents of the varchar column, Column2, quite clearly. The contents of each row and column are displayed directly below the raw memory dump.

In the output example above, you can see “Slot 0”, which is the first row, consists of three columns, Column1, Column2, and Column3. Looking carefully at the offset values, you’ll see the two numeric columns, Column1 and Column3 are physically stored on the page before the variable-length-character column Column2:

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

Column1 = 27272727                  

Slot 0 Column 2 Offset 0x17 Length 135 Length (physical) 135

Column2 = When we found him dead, he had a little foam on his nose, and some blood too, and the doctor said, "Tha
t must be an overdose of heroin"    

Slot 0 Column 3 Offset 0x8 Length 8 Length (physical) 8

Column3 = 1971-07-03 01:27:00.000

It’s a common misconception that column data is stored in the same physical order as the columns are defined in the table. This isn’t the case, and makes sense when you consider that modifying either Column1 or Column3 never requires modifying the location of the other columns on the page since they are both predefined length. Column1 is always 4 bytes since it is an int, and Column3 is always 8 bytes since it is defined as a datetime. Putting Column2 after the other columns means updating the value of Column2 to a physically longer value simply entails writing the contents of the value over top of the current value, extending out into the rest of the page.

The value for Column1 (27272727) is shown in the raw memory dump in the 2nd set of 4 bytes: 1726a001, since SQL Server uses little-endian storage. To convert this value to decimal, simply re-arrange the byte order from back-to-front, so you get 01A02617 – if you convert that into an int via SELECT CONVERT(int, 0x01A02617, 1) you’ll see the original value, 27272727.

The value for Column3, 1971-07-03 01:27:00, is stored in bytes 09 through 15 (datetime values always consume 8 bytes). On-page, this value is stored as 30e51700 03660000, again in little-endian format. Re-ordering the byte values back-to-front, we get 00006636 0017e530. The first 4 bytes represents the number of days since 1900-01-01, and last 4 bytes represents a fraction of a single day stored in three-hundredths of a second. Looking at SELECT CONVERT(int, 0x00006603, 1) we see the number of days since 1900-01-01 is 26115. If we look at SELECT CONVERT(int, CONVERT(int, 0x0017e530, 1) * 3.333333), we get 5219999 which is the number of milliseconds past midnight that equates to 1:27:00 am. Finally, if we DATEADD both those values to 1900-01-01 00:00:00, we get the target datetime value of 1971-07-03 01:27:00. Run SELECT DATEADD(MILLISECOND, 5219999, DATEADD(DAY, 26115, '1900-01-01 00:00:00')) to see that.

I hope you find these details useful – please let me know if you find anything unclear, or incorrect. Also, take a look at the rest of our internals articles.