Using DBCC PAGE to view Row Details
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.
To do this, we need some pages, so we’ll create a simple table with a couple of columns.
CREATE TABLE dbo.DBCC_PAGE_Demo
Column1 int NULL
, Column2 varchar(100) NOT NULL
, Column3 datetime NOT NULL
) ON [PRIMARY]
WITH (DATA_COMPRESSION = NONE);
We’ll populate it with a sample row:
INSERT INTO dbo.DBCC_PAGE_Demo (Column1, Column2, Column3)
VALUES (27272727, 'When we found him dead, he had a little foam on his nose, and some blood too' +
', and the doctor said, "That must be an overdose of heroin"', '1971-07-03 01:27:00')
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:
The query below uses a cursor to iterate over all the
DATA_PAGES in the table:
DBCC TRACEON(3604) WITH NO_INFOMSGS;
DECLARE @dbid int = DB_ID();
DECLARE @fileid int;
DECLARE @pageid int;
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FROM sys.schemas s
INNER JOIN sys.objects o ON o.schema_id = s.schema_id
CROSS APPLY sys.dm_db_database_page_allocations(DB_ID(), o.object_id, NULL, NULL, 'DETAILED') dpa
WHERE o.name = N'DBCC_PAGE_Demo'
AND s.name = N'dbo'
AND dpa.page_type_desc = N'DATA_PAGE';
FETCH NEXT FROM cur INTO @fileid, @pageid;
WHILE @@FETCH_STATUS = 0
DBCC PAGE (@dbid, @fileid, @pageid, 3);
FETCH NEXT FROM cur INTO @fileid, @pageid;
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:
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
BUFFER: section indicates the buffer page number (bpage) which represents exactly where in the buffer pool the page exists. Also included are:
- bhash – The hash of the page
- bpageno – the file and page number
- bdbid – the database ID
- breferences – the number of processes that have referenced the buffer page.
- bcputicks – the amount of time latches have waited to access the page
- bsampleCount – the number of times SQL Server has sampled the latch wait time for this page
- 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.
- bstat, bstat2 – the status of the buffer page.
- blog – the transaction log record where the page was last modified
- bnext – the address of the next page in the buffer pool for this object
- 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:
- 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,
Column3 are physically stored on the page before the variable-length-character column
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
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
Column3 is always 8 bytes since it is defined as a
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.
If you found this post useful, please
consider donating a small amount
to help keep the lights on and site running.
[…] code above is borrowed from my post using DBCC PAGE to view row details. The DBCC PAGE command in this post is using output style 2, which includes the full page hex dump. […]