dm_db_database_page_allocations

sys.dm_db_database_page_allocations is an undocumented SQL Server T-SQL Dynamic Management Function. This DMF provides details about allocated pages, allocation units, and allocation extents.

John Lewis Krimmel - The Quilting Frolic - nothing to do with dm_db_database_page_allocations, but still a good looking 19th century painting!

John Lewis Krimmel – The Quilting Frolic – nothing to do with dm_db_database_page_allocations, but still a good looking 19th century painting!

The function definition in SQL Server 2016 is:

The function definition tells us the parameters required to run the function:

  1. @DatabaseId – smallint specifying the database – not optional
  2. @TableId – int specifying the object_id of the table to return results for. Optional. If this parameter is not specified, results are returned for the entire database.
  3. @IndexId – int specifying the index_id of the table specified in @TableId. Optional. If NULL, return data for all indexes attached to the table, in addition to heap data.
  4. @PartitionId – bigint specifying the single partition of interest. Optional. If NULL, return data for all partitions.
  5. @Mode – nvarchar(64), with default value of N'LIMITED', can instead specify N'DETAILED'. In LIMITED mode, SQL Server does not return data in the following columns:
    1. page_type
    2. page_type_desc
    3. page_level
    4. next_page_file_id
    5. next_page_page_id
    6. previous_page_file_id
    7. previous_page_page_id
    8. is_page_compressed

Rows returned include the following columns:

To see details for all tables in a single database, Use CROSS APPLY to apply this function to rows returned from sys.objects view, as in the following example, with the DETAILED option:

The same statement with the LIMITED option, filtered to a single table, with only the clustered index pages shown.

Common values for the page_type and page_type_desc columns:

╔═══════════╦═════════════════════╗
║ page_type ║ page_type_desc      ║
╠═══════════╬═════════════════════╣
║ NULL      ║ NULL                ║
║ 1         ║ DATA_PAGE           ║
║ 2         ║ INDEX_PAGE          ║
║ 3         ║ TEXT_MIX_PAGE       ║
║ 4         ║ TEXT_TREE_PAGE      ║
║ 10        ║ IAM_PAGE            ║
║ 20        ║ BULK_OPERATION_PAGE ║
╚═══════════╩═════════════════════╝

John Huang talks about the known page types here.

Be aware, the details presented above are subject to change with any new release of SQL Server, since the sys.dm_db_database_page_allocations system DMF is not documented by Microsoft.

See our library of documentation for certain objects in the sys schema here.