Use the undocumented DBCC Page, DBCC IND, and sys.dm_db_database_page_allocations to Peek the Data on the Physical Page

Basically, you need to use the DBCC Page command to examine the contents of a physical page. The command typically uses 4 parameters. The hardest one is page number.

 

/*

DBCC PAGE

(

['database name'|database id], -- can be the actual name or id of the database

  file number, -- the file number where the page is found

  page number, -- the page number within the file

  print option = [0|1|2|3] -- display option; each option provides differing levels of information

)

*/

 

1.  How to find the database id?

If you do not use the DB name, and like to use the DB ID, you can find it in three ways:

 

-- Find All DB IDs

sp_helpdb

 

-- Find the Current DB ID

select DB_ID()

 

-- Find the DB ID for a Specific DB

select DB_ID('AdventureWorks2012')

 

2.  How to find the file number?

-- find the file ID for the current database files. Often, 1=data file, 2 = log file

sp_helpfile

 

-- If you know the file logical name

select file_id('AdventureWorks2012_Data')

 

3.  How to find the page number?

3.1.     If you know the page number, you can use it directly.

3.2.        Otherwise, you can use DBCC IND to find all of the page numbers of the data and indexes for a table. It takes 3 parameters and returns many columns. PagePID is our primary interest.

/*

DBCC IND

(

['database name'|database id], -- the database to use

            table name,                            -- the table name to list results

            index id,                                 -- index_id from sys.indexes;

                         -1 shows all indexes and IAMs, -2 just show IAMs

)

 /* 

 

The columns returned include (from Paul Randal)

·        PageFID - the file ID of the page

·        PagePID - the page number in the file

·        IAMFID - the file ID of the IAM page that maps this page (this will be NULL for IAM pages themselves as they're not self-referential)

·        IAMPID - the page number in the file of the IAM page that maps this page

·        ObjectID - the ID of the object this page is part of

·        IndexID - the ID of the index this page is part of

·        PartitionNumber - the partition number (as defined by the partitioning scheme for the index) of the partition this page is part of

·        PartitionID - the internal ID of the partition this page is part of

·        IAM_chain_type - see IAM chains and allocation units in SQL Server 2005

·        PageType - the page type. Some common ones are:

o   1 - data page

o   2 - index page

o   3 and 4 - text pages

o   8 - GAM page

o   9 - SGAM page

o   10 - IAM page

o   11 - PFS page

·        IndexLevel - what level the page is at in the index (if at all). Remember that index levels go from 0 at the leaf to N at the root page (except in clustered indexes in SQL Server 2000 and 7.0 - where there's a 0 at the leaf level (data pages) and a 0 at the next level up (first level of index pages))

·        NextPageFID and NextPagePID - the page ID of the next page in the doubly-linked list of pages at this level of the index

·        PrevPageFID and PrevPagePID - the page ID of the previous page in the doubly-linked list of pages at this level of the index

3.3.     Find the page numbers via the first page and pages used in a hard way.

 

-- Step 1: Use the right DB

 

USE AdventureWorks2012

GO

 

-- Step 2: find the hobt_ids for the table you are interested. Hobt_ID indicates the ID of the data heap or B-tree that contains the rows for this partition.  It is usually the same as the partition ID.

 

-- Find the hobt_id for each partition. Each partition corresponds to each index of the table. See below for details

 

SELECT *

FROM sys.partitions

WHERE object_id=OBJECT_ID('[HumanResources].[Employee]')

 

/*

partition_id                 object_id                     index_id        partition_number         hobt_id                              rows

72057594065453056 1237579447                 1                  1                                  72057594065453056        290            

72057594065518592 1237579447                 2                  1                                  72057594065518592        290            

72057594065584128 1237579447                 3                  1                                  72057594065584128        290            

72057594065649664 1237579447                 5                  1                                  72057594065649664        290

72057594065715200 1237579447                 6                  1                                  72057594065715200        290            

72057594065780736 1237579447                 7                  1                                  72057594065780736        290

*/

 

-- There are six indexes for this table. 

 

SELECT * FROM sys.indexes

WHERE object_id=OBJECT_ID('[HumanResources].[Employee]')

/*

object_id                       name                                                                              index_id    type            type_desc

1237579447                 PK_Employee_BusinessEntityID                                  1                  1                  CLUSTERED

1237579447                 IX_Employee_OrganizationNode                                  2                  2                  NONCLUSTERED

1237579447                 IX_Employee_OrganizationLevel_OrganizationNode  3                  2                  NONCLUSTERED

1237579447                 AK_Employee_LoginID                                                5                  2                  NONCLUSTERED

1237579447                 AK_Employee_NationalIDNumber                               6                  2                  NONCLUSTERED

1237579447                 AK_Employee_rowguid                                                 7                  2                  NONCLUSTERED

*/

--Step 3: find the first page number in hexadecimal of a partition using the hobt_id (using 72057594065453056 as an example). It returns 0x206900000100 as the hexadecimal value of the 1st page for this partition.

 

SELECT * 

FROM sys.system_internals_allocation_units 

WHERE container_id = 72057594065453056

 

/*

allocation_unit_id     type    type_desc      container_id       total_pages  used_pages

72057594073055232      1       IN_ROW_DATA    72057594065453056  10           9                     

data_pages     first_page             root_page              first_iam_page

7              0x206900000100         x3C2400000100          0x3B2400000100

 

How to interpret the first page number from hexadecimal to decimal?

 

206900000100=>20690000 0100=>20 69 00 00 01 00=>02 96 00 00 10 00=>02960000 1000=>0001 00006920=>FG=1, Page#=26912

 

Similarly, you can find the 1st page number for other partitions of the table.

*/

--A more convenient way is to use the following query to find the 1st page number for each partition of the table. Now, you have the page number information for the table.

 

Select Object_Name(p.object_idAs 'tableName'

    , i.name As 'indexName'

    , p.partition_number

    , au.type_desc

    , Convert (varchar(6),

      Convert (intSubString (au.first_page, 6, 1) +

         SubString (au.first_page, 5, 1))) +

   ':' + Convert (varchar(20),

      Convert (intSubString (au.first_page, 4, 1) +

         SubString (au.first_page, 3, 1) +

         SubString (au.first_page, 2, 1) +

         SubString (au.first_page, 1, 1))) As 'firstPage'

    , Convert (varchar(6),

      Convert (intSubString (au.root_page, 6, 1) +

         SubString (au.root_page, 5, 1))) +

   ':' + Convert (varchar(20),

      Convert (intSubString (au.root_page, 4, 1) +

         SubString (au.root_page, 3, 1) +

         SubString (au.root_page, 2, 1) +

         SubString (au.root_page, 1, 1))) As 'rootPage'

    , Convert (varchar(6),

      Convert (intSubString (au.first_iam_page, 6, 1) +

         SubString (au.first_iam_page, 5, 1))) +

   ':' + Convert (varchar(20),

      Convert (intSubString (au.first_iam_page, 4, 1) +

         SubString (au.first_iam_page, 3, 1) +

         SubString (au.first_iam_page, 2, 1) +

         SubString (au.first_iam_page, 1, 1))) As 'firstIAM_page'

From sys.indexes As i

Join sys.partitions As p

    On i.object_id = p.object_id

    And i.index_id = p.index_id

Join sys.system_internals_allocation_units As au

    On p.hobt_id = au.container_id

Where Object_Name(p.object_id) = 'Employee'--table name only here

Order By tableName

 

/*

tableName      indexName                     type_desc      firstPage      rootPage firstIAM_page

Employee       PK_Employee_BusinessEntityID  IN_ROW_DATA    1:26912        1:9276   1:9275

Employee       IX_Employee_OrganizationNode  IN_ROW_DATA    1:9277         1:9277   1:9279

Employee       IX_Employee_OrganizationLevel IN_ROW_DATA    1:9280         1:9280   1:9281

Employee       AK_Employee_LoginID           IN_ROW_DATA    1:9282         1:9285   1:9283

Employee       AK_Employee_NationalIDNumber  IN_ROW_DATA    1:766          1:770    1:767

Employee       AK_Employee_rowguid           IN_ROW_DATA    1:867          1:867    1:868

*/

 

4.  What are the print options?

(Optional) Print option can be either 0, 1, 2, or 3.

·       0 – print just the page header, the default

·       1 – page header plus per-row hex dumps and a dump of the page slot array (unless it’s 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

5.  How to use and interpret the DBCC Page result?

5.1.     Turn on the trace flag 3604 first

     dbcc traceon(3604)

5.2.     Issue the command with correct parameters

DBCC PAGE ('AdventureWorks2012', 1, 26912, 0) -- page number 26912 from the above query

5.3.     View and interpret the result - See Paul Randal's post on the explanation of the headerinformation

 

PAGE: (1:26912)

 

BUFFER:

 

BUF @0x00000002791D63C0

 

bpage = 0x0000000261908000          bhash = 0x0000000000000000          bpageno = (1:26912)

bdbid = 7                           breferences = 0                     bcputicks = 0

bsampleCount = 0                    bUse1 = 54841                       bstat = 0x9

blog = 0x15a                        bnext = 0x0000000000000000         

 

PAGE HEADER:

 

Page @0x0000000261908000

 

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

m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x220

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

Metadata: AllocUnitId = 72057594073055232                               

Metadata: PartitionId = 72057594065453056                                Metadata: IndexId = 1

Metadata: ObjectId = 1237579447     m_prevPage = (0:0)                  m_nextPage = (1:26913)

pminlen = 47                        m_slotCnt = 46                      m_freeCnt = 4

m_freeData = 8096                   m_reservedCnt = 0                   m_lsn = (56:16:25)

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

m_tornBits = -2139840934            DB Frag ID = 1                   

 

Allocation Status

 

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

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

ML (1:7) = NOT MIN_LOGGED          

 

But we often use the print option 3 in the tabular format to view the data

 

DBCC PAGE ('AdventureWorks2012', 1, 26912, 3) with TableResults.

 

Please note, if the page type =1, it is a data page, the result will displays the header info and data info as below:

 

SQL Server 2005:


SQL Server 2012:


If the page type =2, it is an index page, the result will displays the header info and index info separately as below:


 

If the page type =10, it is an IAM page, the result will displays the header info and IAM Header info only as below:


 

6.  In SQL Server 2012, DBCC IND has been replaced with sys.dm_db_database_page_allocations, which is still undocumented.

sys.dm_db_database_page_allocations returns more than DBCC IND such as partition_id, allocation_unit_id, extent information, page fullness, compression information, and whether the page has any ghost records. See Kalen Delany’s Post for more details.