DBCC IND

Another copy and paste article mainly originated from SQL Server Storage Engine Blog  and SQL Server 2008 internals.

Syntax

DBCC IND ( { 'dbname' | dbid }, { 'objname' | objid },
{ nonclustered indid | 1 | 0 | -1 | -2 } [, partition_number] )

DBCC IND command is used to see which pages have which data, as well as which pages precede and follow various pages in all levels of the index.Basically, examine composition of index.

The first parameter is the database name or the database ID. The second parameter is an
object name or object ID within the database; the object can be either a table or an indexed
view. The third parameter is a specifi c nonclustered index ID (2-250 or 256-1005) or the
values 1, 0, –1, or –2. The values for this parameter have the following meanings:
■ 0 Displays information for in-row data pages and in-row IAM pages of the specifi ed
object.
■ 1 Displays information for all pages, including IAM pages, data pages, and any
existing LOB pages or row-overflow pages of the requested object. If the requested
object has a clustered index, the index pages are included.
■ –1 Displays information for all IAMs, data pages, and index pages for all indexes on
the specifi ed object. This includes LOB and row-overflow data.
■ –2 Displays information for all IAMs for the specified object.
■ Nonclustered index ID Displays information for all IAMs, data pages, and index
pages for one index. This includes LOB and row-overflow data that might be part of
the index’s included columns.

Column Descriptions for DBCC IND Output

• 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

Create a table to hold result dumped from DBCC IND. This technique is also often used to take snapshot of database status.

CREATE TABLE IndexPages
(PageFID tinyint,
PagePID int,
IAMFID tinyint,
IAMPID int,
ObjectID int,
IndexID tinyint,
PartitionNumber tinyint,
PartitionID bigint,
iam_chain_type varchar(30),
PageType tinyint,
IndexLevel tinyint,
NextPageFID tinyint,
NextPagePID int,
PrevPageFID tinyint,
PrevPagePID int,
Primary Key (PageFID, PagePID));

TRUNCATE TABLE sp_IndexPages;

INSERT INTO sp_IndexPages
EXEC ('DBCC IND (AdventureWorks2008, [Sales.SalesOrderDetail], -1)');

SELECT IndexLevel
, PageFID
, PagePID
, PrevPageFID
, PrevPagePID
, NextPageFID
, NextPagePID
FROM IndexPages
ORDER BY IndexLevel DESC, PrevPagePID;

Note: Use sys.dm_db_index_physical_stats to check the index level and its page numbers in each level.

SELECT index_depth AS D
, index_level AS L
, record_count AS 'Count'
, page_count AS PgCnt
, avg_page_space_used_in_percent AS 'PgPercentFull'
, min_record_size_in_bytes AS 'MinLen'
, max_record_size_in_bytes AS 'MaxLen'
, avg_record_size_in_bytes AS 'AvgLen'
FROM sys.dm_db_index_physical_stats
(DB_ID ('test')
, OBJECT_ID ('test')
, 1, NULL, 'DETAILED');