SQL Server - DBCC PAGE and DBCC IND

DBCC IND – used to know the page details of tables & indexes.

Syntax:
DBCC IND ( {dbname}, {table_name},{index_id} )

Description of columns displayed from DBCC IND

PageFID
File ID of Page

PagePID
Page ID

IAMFID,IAMPID
File ID&Page ID -> IAM mapping


ObjectID
 object ID(Table)

IndexID
Clustered index (IndexID = 1),Non clustered index (IndexID = 2)

PartitionNumber
 Number of Partition which holds data & index pages

PartitionID
ID of Partition which holds data & index pages

iam_chain_type
In-row data,Row-Overflow data

PageType
1=Data page,2=Index page,3&4=Text page,10=IAM

IndexLevel
0=leaf level

NextPageFID,NextPagePID,PrevPageFID,PrevPagePID
Next & Previous Page ID 's & File ID 's of a page




 

DBCC PAGE – used to see the contents of  a Page.

Syntax:
DBCC  PAGE( {dbid|dbname}, pagenum [,print option] [,cache] [,logical] )


DBCC PAGE & DBCC IND both are undocumented commands.

Few examples for DBCC PAGE & DBCC IND:



CREATE DATABASE TEST_PAGE
GO


USE TEST_PAGE
GO

CREATE TABLE TEST_INROW_DATA (Col1  INT IDENTITY(1,1),Col2 CHAR(20),Col3 VARCHAR(20))

INSERT INTO TEST_INROW_DATA VALUES('Sathya','SQL Server')
GO 10

-- to see IAM details alone
DBCC IND('TEST_PAGE',TEST_INROW_DATA,-2)
GO


CREATE CLUSTERED INDEX CI_IDX ON TEST_INROW_DATA(Col1)

-- to see CLUSTERED INDEX page details ,whose IndexID = 1
DBCC IND('TEST_PAGE',TEST_INROW_DATA,1)
GO

CREATE NONCLUSTERED INDEX NCI_IDX ON TEST_INROW_DATA (Col2)

-- to see NONCLUSTERED INDEX page details ,whose IndexID = 2
DBCC IND('TEST_PAGE',TEST_INROW_DATA,2)
GO

--to see all indexes page details
DBCC IND('TEST_PAGE',TEST_INROW_DATA,-1)
GO

--By default DBCC PAGE informations will go to error log,
--to display PAGE informations on current connection SET DBCC TRACEON(3604)

DBCC TRACEON(3604)

DBCC PAGE('TEST_PAGE',1,168,3) WITH TABLERESULTS
GO

DBCC PAGE('TEST_PAGE',1,170,3) WITH TABLERESULTS
GO











--DROP DATABASE TEST_PAGE




For more informations about data & index pages, refer below links:

1 comment:

Jeff Moden said...

Just a tip... you don't need to turn Trace Flag 3604 if you use WITH TABLERESULTS.