sys.dm_db_database_page_allocations is one of the dynamic management functions shipped with SQL Server 2012. As its name implies, it does the same; it gives the information as to how objects keep data in different pages and its allocation in the databases.
This function is somehow same as undocumented DBCC IND command but it provides much more information than DBCC IND that we will discuss later in this article.
Syntax :
sys.dm_db_database_page_allocations (@DatabaseId , @TableId , @IndexId , @PartionID , @Mode)
Parameters :
@DatabaseId :You need to pass the required database ID. This parameter is mandatory and data type of this argument is small integer.
@TableId:You need to pass the required table ID. This parameter is optional and data type of this argument is integer.
@IndexId:You need to pass the required Index ID. This parameter is optional and data type of this argument is integer.
@PartionID:You need to pass the required Partion ID. This parameter is optional and data type of this argument is integer.
@Mode:You need to pass the required Mode. This parameter is mandatory and data type of this argument is nvarchar(64). In this argument we must pass only ‘DETAILED’ OR ‘LIMITED’.
Purpose :
As we know that SQL Server stores data in the pages and whenever we need to view this information in earlier versions (2005, 2008) of SQL Server we need to invoke an undocumented command DBCC IND to get the info. The problem with this command is that if you want to further manipulate its result set you need to insert its record set in a temporary table and it also has a limited level of information.
In SQL SERVER 2012, sys.dm_db_database_page_allocations came as a replacement for the DBCC IND command and it gives more information about the internal storage of the data.
Given below are the major differences between sys.dm_db_database_page_allocations and DBCC IND.
| S.No | sys.dm_db_database_page_allocations | DBCC IND |
| 1 | It is an in line function, so you can easily manipulate its result set without using any temporary table. | You need temporary table to manipulate its result set. |
| 2 | It gives you the information about unallocated pages of the object as well. | It gives you the information about allocated pages of the object only. |
| 3 | It gives you the complete details of internal storage including allocated unit details. | It does not give you the complete details of internal storage including allocated unit details. |
| 4 | You can query all the database objects allocation at a time. | You can query only one object at a time. |
Given are column comparisons between sys.dm_db_database_page_allocations and DBCC IND command. Also highlighted are the new fields introduced in this function.
Let me explain few examples to explain this function :
Example 1 :
In this example, this function will give you the page allocation of “Sales” table only.
Select * from sys.dm_db_database_page_allocations(DB_ID(), object_id('Sales'),NULL,NULL,'DETAILED')
Example 2 :
In this example, this function will give you the page allocation for all objects including system objects.
Select * from sys.dm_db_database_page_allocations(DB_ID(), NULL ,NULL,NULL,'DETAILED')
Reference : CONNECT
SQL Server Portal 
[…] Vale ressaltar que a partir da versão 2012 o DBCC Ind foi substituído pela DMF – Dynamic Management Function: SYS.DM_DB_DATABASE_PAGE_ALLOCATIONS, conforme apresenta Muhammad Imran em seu post: https://raresql.com/2013/01/24/sql-server-2012-sys-dm_db_database_page_allocations-dynamic-management… […]
You should test it for page)free_space_percent and find out that even as late as SQLServer 2017 Enterprise Edition that it ALWAYS return Nulls for in-row data and only works for out-of-row data. You should also check the double linking and find out that it breaks the chain many times even on a totally static table.
This function isn’t ready for “Prime Time”, IMHO. This is another fine example of MS releasing something that isn’t fully functional and is missing features that it should have had.
Not your fault but if you’re looking for proper functionality for the types of things I’ve stated and more, then stick with DBCC IND and DBCC PAGE.