Finding table pages with DBCC IND (A Storage Wednesday Post)

Introduction

In February 2014, I started the Storage Wednesday series. Since then I have written every Wednesday about the way SQL Server stores and accesses data. The list of posts can be found at the end of the first one: The Page.

In continuation of the Storage Wednesday series, today I would like to invite you to discover a way of identifying all pages that belong to a table or index, using the DBCC IND statement.

DBCC IND Example

The DBCC IND statement is considered undocumented and as such it is not supported by Microsoft. However, a lot has been written about it, so it is widely known within the SQL Server community. Nevertheless, do not use this statement in production.

The DBCC IND statement takes three parameters like this:

The first parameter is the database id. If you pass in 0 here, the current database is used. The second parameter is the table name in quotes. You can also pass in the object_id of the table instead of the quoted name like this:

The third parameter is the index_id. There is an optional fourth parameter that allows us to specify the partition_id if we are only interested in a single partition.

The output of DBCC IND is a list of all database pages that make up the selected index or partition. The output looks like this:

The PageFID and PagePID columns make up the address of the page. ObjectID, IndexID, PartitionNumber and PartitionID describe the object, index and partition that the page belongs to. iam_chain_type tells us if the page belongs to a row data or a row overflow or LOB allocation unit. The PageType finally tells us what type of page we are looking at. There are also a few columns, which deal with the IAM page for this page. Those will make sense once I explained IAM pages in a future post. Finally, there are five additional columns, which deal with additional information relevant to indexes. Those columns are not shown in the above screenshot.

Summary

DBCC IND can be used to identify all database pages that belong to a particular index. It returns one row for each page and includes information about the role that the page plays within the index.