DBCC IND and DBCC PAGE

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, 'TableName' or ObjectId, NOn Clustred Index ID)

The third parameter can be the Non Clustered Index Id from Sys.indexes table or 1 or 0 or -1 or -2.  -1 provides complete information about all type of pages ( in row data, row over flow data, IAM, all indexes ) associated with the table.

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

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, FileNumber, PageNumber, PrintOption)

                 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.

http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-using-dbcc-page-and-dbcc-ind-to-find-out-if-page-splits-ever-roll-back/