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_id) As 'tableName'
, i.name As 'indexName'
, p.partition_number
, au.type_desc
, Convert (varchar(6),
Convert (int, SubString (au.first_page, 6, 1) +
SubString (au.first_page, 5, 1))) +
':' + Convert (varchar(20),
Convert (int, SubString (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 (int, SubString (au.root_page, 6, 1) +
SubString (au.root_page, 5, 1))) +
':' + Convert (varchar(20),
Convert (int, SubString (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 (int, SubString (au.first_iam_page, 6, 1) +
SubString (au.first_iam_page, 5, 1))) +
':' + Convert (varchar(20),
Convert (int, SubString (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.
