A Replacement for DBCC IND in SQL Server 2012

Posted by & filed under , , , .

Lego TreeEach release of SQL Server is chalk full of new features and an assorted amount of bells and whistles.  Some of these, like Availability Groups and ColumnStore, are great additions and their introduction includes much fanfare.  Among all of those new features, is the occasional little bell which offers us a chance to change how we investigate SQL Server.  In this case, the bell of the day is the new dynamic management function (DMF) named sys.dm_db_database_page_allocations.

Basics of sys.dm_db_database_page_allocations

The new DMF provides replacement functionality for DBCC IND. Based on the parameters provided, it will return a list of all pages that are associated with one or more tables and indexes.  The available parameters for sys.dm_db_database_page_allocations are:

  • @DatabaseId: Database from which to return the page listing for tables and indexes.  The parameter is required and accepts the use of the DB_ID() function.
  • @TableId: Object_id for the table from which to return the page listing.  The parameter is required and accepts the use of the OBJECT_ID() function.  NULL can also be used to return all tables.
  • @IndexId: Index_id from the table which the page list is from.  The parameter is required and accepts the use of NULL to return information for all indexes.
  • @PartionId: ID of the partition which the page list is returning.  The parameter is required and accepts the use of NULL to return information for all indexes.
  • @Mode: Defines the mode for returning data, the options are DETAILED or LIMITED.  With LIMITED, the information is limited to page metadata, such as page allocation and relationships information.  Under the DETAILED mode, additional information is provided, such as page type and inter-page relationship chains.

Uses for sys.dm_db_database_page_allocations

There are number of ways that sys.dm_db_database_page_allocations can be utilized.  For starters, as a replacement for DBCC IND, the DMF can be used in all cases where the DBCC command would be used.  The main advantage of using the DMF is the ability to easily join the page information to other DMVs and DMFs on the database.  When using DBCC IND, the data would first need to be placed into a table to interact with other data.

If you’re not familiar with DBCC IND, there are plenty of good uses for that command and this DMF.  Some of these include the following:

  • Investigating the structure of indexes: Useful for showing how an index is built to help form an understanding how SQL Server processes data.  If a table or index is partitioned, this DMF can help validate that there are in-fact multiple B-tree structures for the index.
  • Researching page corruption: When you have a corrupt page, looking at the preceding and following pages can help to identify the data on the corrupt page.
  • Investigating space utilization: When a table has been identified as taking a large amount of space, use this DMF to determine what types of pages are being utilized and offer an opportunity to see the effects of storage choices.
  • Replicating sysindexes page values: The compatibility view sysindexes contains the columns root, first, and first_iam_pages which are not a part of sys.indexes.  This DMF allows the ability to recreate those values.

What other uses for DBCC IND do you have?  Is there anything you can accomplish with DBCC IND that isn’t possible with sys.dm_db_database_page_allocations?  Or visa versa? Share them in the comments section, and I’ll update this list.

sys.dm_db_database_page_allocations COMPARISON

There are many similarities and differences between using the new DMF and using DBCC IND.  For starters, the columns between the two overlap in a number of places (though the names of columns do differ), as shown in Table 1.  As the table shows, there is 100% coverage in sys.dm_db_database_page_allocations for the columns returned by DBCC IND.

Table 1. Mapping Overlapping Columns

DMF Column DBCC Column Description
object_id ObjectID Object ID for the table or view
index_id IndexID ID for the index
partition_id PartitionNumber Partition number for the index
rowset_id PartitionID Partition ID for the index
allocation_unit_type_desc iam_chain_type Description of the allocation unit
allocated_page_iam_file_id IAMFID File ID for the index allocation map page associated to the page
allocated_page_iam_page_id IAMPID Page ID for the index allocation map page associated to the page
allocated_page_file_id PageFID File ID of the allocated page
allocated_page_page_id PagePID Page ID for the allocated page
page_type PageType Page type ID for the allocated page
page_level IndexLevel Level of the page in B-Tree index
next_page_file_id NextPageFID File ID for the next page
next_page_page_id NextPagePID Page ID for the next page
previous_page_file_id PrevPageFID File ID for the previous page
previous_page_page_id PrevPagePID Page ID for the previous page

Comparative queries between sys.dm_db_database_page_allocations and DBCC IND can be created using the mapping table.  The only other differences are the values returned as NULL from sys.dm_db_database_page_allocations instead of 0 when there is no referencing page.  Also, with the DMF, to return the same information as DBCC IND, only allocated pages are included.  For the DMF, to return similar data, the DETAILED mode is required.  Queries for these two statements are provided in Listing 1.


--Listing 1. Queries for sys.dm_db_database_page_allocations and DBCC IND

SELECT
allocated_page_file_id AS PageFID
,allocated_page_page_id AS PagePID
,allocated_page_iam_file_id AS IAMFID
,allocated_page_iam_page_id AS IAMPID
,object_id AS ObjectID
,index_id AS IndexID
,partition_id AS PartitionNumber
,rowset_id AS PartitionID
,allocation_unit_type_desc AS iam_chain_type
,page_type AS PageType
,page_level AS IndexLevel
,next_page_file_id AS NextPageFID
,next_page_page_id AS NextPagePID
,previous_page_file_id AS PrevPageFID
,previous_page_page_id AS PrevPagePID
FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('Sales.SalesOrderHeader'), 1, NULL, 'DETAILED')
WHERE is_allocated = 1;
GO

DBCC IND (0,'Sales.SalesOrderHeader',1)

Besides the columns that match, there are a number of additional columns in the DMF.  These columns, defined in Table 2, provide metadata on pages and information on the extents they tie into.  There is one set of columns that I haven’t figured out yet these are data_clone_id, clone_state, and clone_state_desc.

Table 2. New sys.dm_db_database_page_allocations Columns

DMF Column Description
database_id ID of the database
allocation_unit_id ID of the allocation unit
allocation_unit_type Type of allocation unit
data_clone_id Unknown
clone_state Unknown
clone_state_desc Unknown
extent_file_id File ID of the extent
extent_page_id Page ID for the extent
is_allocated Indicates whether a page is allocated
is_iam_page Indicates whether a page is the index allocation page
is_mixed_page_allocation Indicates whether a page is allocated
page_free_space_percent Percentage of space free on the page
page_type_desc Description of the page type
is_page_compressed Indicates if the page is compressed
has_ghost_records Indicates if the page has ghost records

Beyond the additional columns, there are a few other differences between the DBCC command the the DMF.  The main one is that with the DMF, you have the ability to join the results from the output directly into other DMVs and DMFs, without the need for intermediate tables.  Also, with the is_allocated flag, results can be included for those pages that have been assigned (via uniform extents) but haven’t been allocated with data.  Lastly, through the additional metadata, you can easily determine which pages in an index are compressed versus non-compressed.

Conclusion

The addition of sys.dm_db_database_page_allocations to SQL Server is a welcomed new feature of SQL Server 2012.  While it is still considered an undocumented feature, the same as DBCC IND, it has the potential to become much more useful that DBCC IND ever was.  How do you plan to use the DMF in your environments?