Each 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

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

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?