dm_db_database_page_allocations

2019-08-01 · by Hannah Vernon · in sys

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!

The function definition in SQL Server 2016 is:

create function sys.dm_db_database_page_allocations
    (
    @DatabaseId         SMALLINT,
    @TableId            INT             = NULL,
    @IndexId            INT             = NULL,
    @PartitionId        BIGINT          = NULL,
    @Mode               NVARCHAR(64)    = 'LIMITED'
    )
returns table
as
    return select DA.[database_id], DA.[object_id], DA.[index_id], DA.[partition_id], DA.[rowset_id],
        DA.[allocation_unit_id], DA.[allocation_unit_type], IP.[name] as 'allocation_unit_type_desc',
        DA.[data_clone_id], DA.[data_clone_state] as 'clone_state',
        case (DA.[data_clone_state]) when 0 then N'PRIMARY' else N'SECONDARY' end AS clone_state_desc,
        DA.[extent_file_id], DA.[extent_page_id],
        DA.[allocated_page_iam_file_id], DA.allocated_page_iam_page_id, DA.[allocated_page_file_id], DA.allocated_page_page_id,
        DA.is_allocated, DA.is_iam_page, DA.is_mixed_page_allocation, DA.page_free_space_percent,
        DA.page_type, DA.page_type_desc, DA.page_level, DA.next_page_file_id, DA.next_page_page_id, DA.previous_page_file_id, DA.previous_page_page_id,
        DA.is_page_compressed, DA.has_ghost_records
    from openrowset (table DM_DB_DATABASE_ALLOCATIONS, @DatabaseId, @TableId, @IndexId, @PartitionId, @Mode) DA
        JOIN sys.syspalvalues IP ON IP.[class] = 'AUTY' AND IP.[value] = DA.[allocation_unit_type]

create function sys.dm_db_database_page_allocations

(

@DatabaseId SMALLINT,

@TableId INT = NULL,

@IndexId INT = NULL,

@PartitionId BIGINT = NULL,

@Mode NVARCHAR(64) = 'LIMITED'

)

returns table

return select DA.[database_id], DA.[object_id], DA.[index_id], DA.[partition_id], DA.[rowset_id],

DA.[allocation_unit_id], DA.[allocation_unit_type], IP.[name] as 'allocation_unit_type_desc',

DA.[data_clone_id], DA.[data_clone_state] as 'clone_state',

case (DA.[data_clone_state]) when 0 then N'PRIMARY' else N'SECONDARY' end AS clone_state_desc,

DA.[extent_file_id], DA.[extent_page_id],

DA.[allocated_page_iam_file_id], DA.allocated_page_iam_page_id, DA.[allocated_page_file_id], DA.allocated_page_page_id,

DA.is_allocated, DA.is_iam_page, DA.is_mixed_page_allocation, DA.page_free_space_percent,

DA.page_type, DA.page_type_desc, DA.page_level, DA.next_page_file_id, DA.next_page_page_id, DA.previous_page_file_id, DA.previous_page_page_id,

DA.is_page_compressed, DA.has_ghost_records

from openrowset (table DM_DB_DATABASE_ALLOCATIONS, @DatabaseId, @TableId, @IndexId, @PartitionId, @Mode) DA

JOIN sys.syspalvalues IP ON IP.[class] = 'AUTY' AND IP.[value] = DA.[allocation_unit_type]

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

@DatabaseId – smallint specifying the database – not optional
@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.
@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.
@PartitionId – bigint specifying the single partition of interest. Optional. If NULL, return data for all partitions.
@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:


      database_id                int           NULL
    , object_id                  int           NULL
    , index_id                   int           NULL
    , partition_id               int           NULL
    , rowset_id                  bigint        NULL
    , allocation_unit_id         bigint        NULL
    , allocation_unit_type       int           NULL
    , allocation_unit_type_desc  nvarchar(60)  COLLATE Latin1_General_CI_AS_KS_WS NOT NULL
    , data_clone_id              int           NULL
    , clone_state                int           NULL
    , clone_state_desc           nvarchar(9)   NOT NULL
    , extent_file_id             smallint      NULL
    , extent_page_id             int           NULL
    , allocated_page_iam_file_id smallint      NULL
    , allocated_page_iam_page_id int           NULL
    , allocated_page_file_id     smallint      NULL
    , allocated_page_page_id     int           NULL
    , is_allocated               tinyint       NULL
    , is_iam_page                tinyint       NULL
    , is_mixed_page_allocation   tinyint       NULL
    , page_free_space_percent    int           NULL
    , page_type                  int           NULL
    , page_type_desc             nvarchar(256) NULL
    , page_level                 tinyint       NULL
    , next_page_file_id          smallint      NULL
    , next_page_page_id          int           NULL
    , previous_page_file_id      smallint      NULL
    , previous_page_page_id      int           NULL
    , is_page_compressed         tinyint       NULL
    , has_ghost_records          tinyint       NULL

database_id int NULL

, object_id int NULL

, index_id int NULL

, partition_id int NULL

, rowset_id bigint NULL

, allocation_unit_id bigint NULL

, allocation_unit_type int NULL

, allocation_unit_type_desc nvarchar(60) COLLATE Latin1_General_CI_AS_KS_WS NOT NULL

, data_clone_id int NULL

, clone_state int NULL

, clone_state_desc nvarchar(9) NOT NULL

, extent_file_id smallint NULL

, extent_page_id int NULL

, allocated_page_iam_file_id smallint NULL

, allocated_page_iam_page_id int NULL

, allocated_page_file_id smallint NULL

, allocated_page_page_id int NULL

, is_allocated tinyint NULL

, is_iam_page tinyint NULL

, is_mixed_page_allocation tinyint NULL

, page_free_space_percent int NULL

, page_type int NULL

, page_type_desc nvarchar(256) NULL

, page_level tinyint NULL

, next_page_file_id smallint NULL

, next_page_page_id int NULL

, previous_page_file_id smallint NULL

, previous_page_page_id int NULL

, is_page_compressed tinyint NULL

, has_ghost_records tinyint NULL

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:

SELECT o.name
    , dpa.*
FROM sys.objects o
    CROSS APPLY sys.dm_db_database_page_allocations(DB_ID(), o.object_id, NULL, NULL, N'DETAILED') dpa
WHERE o.type = N'U';

SELECT o.name

, dpa.*

FROM sys.objects o

CROSS APPLY sys.dm_db_database_page_allocations(DB_ID(), o.object_id, NULL, NULL, N'DETAILED') dpa

WHERE o.type = N'U';

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

SELECT o.name
    , i.name
    , i.type_desc
    , dpa.*
FROM sys.objects o
    INNER JOIN sys.indexes i ON o.object_id = i.object_id
    CROSS APPLY sys.dm_db_database_page_allocations(DB_ID(), o.object_id, NULL, NULL, 'LIMITED') dpa
WHERE o.type = N'U'
    AND o.name = 'MyTableName'
    AND i.index_id = 1; --clustered indexes are always index_id 1

SELECT o.name

, i.name

, i.type_desc

, dpa.*

FROM sys.objects o

INNER JOIN sys.indexes i ON o.object_id = i.object_id

CROSS APPLY sys.dm_db_database_page_allocations(DB_ID(), o.object_id, NULL, NULL, 'LIMITED') dpa

WHERE o.type = N'U'

AND o.name = 'MyTableName'

AND i.index_id = 1; --clustered indexes are always index_id 1

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.

dm_db_database_page_allocations

Related

Leave a ReplyCancel reply

Search

Categories

Pages

Meta