DBCC IND: It is one of the undocumented commands,
It is not supported by Microsoft. This command helps in identifying the page
numbers that table or index is using. The syntax looks like below.
Syntax: DBCC IND ('DBName' or DBID
The result set of DBCC IND looks like below:
My table using the page numbers 290, 290, 292 and 293.
Explanation of the what does each column mean:
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:
1 – data page 2 – index page 3 and 4 – text
pages 8 – GAM page 9 – SGAM page
10 – IAM page 11 – PFS page
10 – IAM page 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
DBCC PAGE: It is another undocumented command. As the name suggests this command helps view the contents of the data and index pages. The result of this command is little hard to understand. The syntax looks like below.
Syntax: DBCC PAGE('DBName' or DBID
PrintOption can be 0 or 1 or 2 or 3 -
each option provides different information.
To see the result of this command, lets create a test database, table and insert few records as below.
USE MASTER
CREATE
DATABASE Test
GO
USE Test
CREATE TABLE
Pagetable
(
SNo INT
IDENTITY(1,1),
Name
VARCHAR(50) NOT NULL,
Gender
varchar(5),
Fathername
varchar(50),
DOB DATETIME
NOT NULL,
CONSTRAINT
pk_pagetable PRIMARY KEY (Sno),
CONSTRAINT
uq_name_father UNIQUE (Name, Fathername)
)
GO
INSERT INTO DBO.Pagetable (Name,Gender,Fathername,DOB)
VALUES('Tim','M','Tom','1999-03-15')
INSERT INTO DBO.Pagetable (Name,Gender,Fathername,DOB)
VALUES('Jim','M','Cam','1999-03-15')
INSERT INTO DBO.Pagetable (Name,Gender,Fathername,DOB)
VALUES('Tina','F','Mik','1999-03-15')
Now lets use the DBCC PAGE command to see the connects of this Test database. But we dont know which page does this database is using. So first run the below DBCC IND command, it results as below screenshot.
DBCC IND
('test',pagetable,-1)
Result:
Let’s say I am interested in viewing the
DBCC PAGE contents of the PageID 286, run the below command.
And we need to turn on the trace 3604 to
view the results.
DBCC TRACEON (3604)
DBCC PAGE
('test',1,286,3) with TABLERESULTS
Result:
Please go through the below posts for more details on DBCC IND and PAGE.
