Feeds:
Posts
Comments

Posts Tagged ‘SQL SERVER 2012 – sys.dm_db_database_page_allocations – dynamic management function’

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.

dm_db_database_page_allocation

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

Read Full Post »