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:
- http://blogs.technet.com/b/josebda/archive/2009/03/16/what-s-really-inside-a-sql-server-database-file.aspx
- http://msdn.microsoft.com/en-us/library/ms190969%28SQL.105%29.aspx
- http://msdn.microsoft.com/en-us/library/ms189051%28SQL.105%29.aspx
- http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/25/646865.aspx
- http://support.microsoft.com/kb/83065
1 comment:
Just a tip... you don't need to turn Trace Flag 3604 if you use WITH TABLERESULTS.
Post a Comment