Home » Blog » The sys.dm_db_database_page_allocations DMF

The sys.dm_db_database_page_allocations DMF

2014-07-09 - General, Series, SQL Server 2012, SQL Server Internals, Storage Wednesday

Finding the Pages of a Table in SQL Server 2012 and Later

Introduction

In SQL Server versions before SQL Server 2012, you have to use the undocumented DBCC IND command if you want to know which pages belong to a particular table. Since version 2012, SQL Server provides a simpler (but still undocumented) method to get that information.

The sys.dm_db_database_page_allocations DMF

The sys.dm_db_database_page_allocations DMF takes five parameters:

[sql] @DatabaseId SMALLINT,
@TableId INT = NULL,
@IndexId INT = NULL,
@PartitionId BIGINT = NULL,
@Mode NVARCHAR(64) = 'LIMITED'
[/sql]

The @DatabaseId, @TableId, @IndexId and @PartitionId detail the object whose pages you are interested in. @TableId, @IndexId and @PartitionId can be NULL, the @DatabaseId has to be specified. If for example, you specify the @TableId and @IndexId of a particular index but you pass in NULL for the @PartitionId, the function will return all pages in all partitions for that particular index. If only the @DatabaseId is valued, all (table related) pages in that database are returned.

The @Mode can be either 'LIMITED' or 'DETAILED'. 'LIMITED' returns less information. However, 'DETAILED' uses significantly more resources. Therefore, it should be used only on small sets of pages.

This DMF returns quite a few columns. The full list you can see in the following SELECT statement:

[sql] SELECT DDDPA.database_id,
DDDPA.object_id,
DDDPA.index_id,
DDDPA.partition_id,
DDDPA.rowset_id,
DDDPA.allocation_unit_id,
DDDPA.allocation_unit_type,
DDDPA.allocation_unit_type_desc,
DDDPA.data_clone_id,
DDDPA.clone_state,
DDDPA.clone_state_desc,
DDDPA.extent_file_id,
DDDPA.extent_page_id,
DDDPA.allocated_page_iam_file_id,
DDDPA.allocated_page_iam_page_id,
DDDPA.allocated_page_file_id,
DDDPA.allocated_page_page_id,
DDDPA.is_allocated,
DDDPA.is_iam_page,
DDDPA.is_mixed_page_allocation,
DDDPA.page_free_space_percent,
DDDPA.page_type,
DDDPA.page_type_desc,
DDDPA.page_level,
DDDPA.next_page_file_id,
DDDPA.next_page_page_id,
DDDPA.previous_page_file_id,
DDDPA.previous_page_page_id,
DDDPA.is_page_compressed,
DDDPA.has_ghost_records
FROM sys.dm_db_database_page_allocations
(
DB_ID(),
OBJECT_ID('dbo.tst'),
NULL,
NULL,
'DETAILED'
) AS DDDPA;
[/sql]

The function returns one row per page that belongs to one of the partitions selected by the parameters.
The columns database_id to partition_id specify the partition to which the page belongs. The three allocation unit columns reference the allocation unit of the page. The preceding rowset_id column is what the sys.allocation_units catalog view calls container_id. The two extent columns identify the first page of the page's extent and the IAM columns tell us which IAM page has the current page cataloged. allocated_page_file_id and allocated_page_page_id contain the information we are really after here. They specify the file id and page number of the current page.

All the other columns give us more detailed information about the page itself, for example the page type or the level of the page within the index. Many of these additional columns are only valued when the @Mode parameter is set to 'DETAILED'.

The screenshot below shows a few of the column returned for one of my example tables:

Summary

Since SQL Server 2012, the sys.dm_db_database_page_allocations DMF provides an easier to use alternative to the DBCC IND command. It also provides a lot more information about each page than that DBCC command. However, like DBCC IND, the sys.dm_db_satabase_page_allocations DMF is sadly not part of the official documentation, and as such its functionality cannot be guaranteed.