SQL SERVER – Table Space Allocation Details using DMV

Understanding SQL Server from inside-out is always something I have loved to learn from time to time. Recently, I was asking my good friend Balmukund on how are pages allocated inside SQL Server and what are the various ways to find the same. He was quick to bounce and say have you ever checked DBCC IND and EXTENTINFO commands. These undocumented commands have been there for a while and I have seen usage of these commands at a number of sessions.

In the evening, I was pleasantly surprised to see Balmukund call me. This doesn’t happen quite often and I was eager to know what the context was. He had called me to talk about a DMV (dm_db_database_page_allocations) and if I had seen. Many a times I have seen as techies, it is difficult to sleep if there is a problem at hand. And my friend was no different, he has given me a learning path to look at. I started to dig into this DMV and was pleasantly surprised to see various details.

dm_db_database_page_allocations

SQL Server 2012 introduces a new dynamic management function that replaces the old and undocumented DBCC IND and DBCC EXTENTINFO commands. DBCC IND and DBCC EXTENTINFO were widely used by the Microsoft Support team when working on customer issues on database storage or space issue. Examples are database shrink, excessive space usage by a table or index, know the linkages of pages and so on.

DBCC IND and DBCC EXTENTINFO had several limitations. Since it was a DBCC command, in order to perform filtering and advanced grouping, you had to first import the entire output into a temporary table and then perform processing. The use of the new dynamic management function solves all those limitations posed. To recollect, the name of the new dynamic management function is: Sys.dm_db_database_page_allocations()

There are a number of arguments that need to be passed as part of this function. These include:

1. databaseId – database id [not null]
2. tableId – object id or NULL
3. indexId – index id or NULL
4. partitionId – partition id or NULL
5. Mode – LIMITED or DETAILED

The output of this dynamic management function includes all the pages and extents allocated for table, index or partition. If we supply NULL for the indexId and partitionId arguments, information about all pages and extents allocated to the table is returned.

The last argument “mode” determines if extended information is returned in the output. The output of this function includes information from the page header. Obtaining that information involves extra processing and consumption of resources. In order to keep the execution time within reasonable limits, the default for this argument is LIMITED. If you specifically need information for any of those columns, you need to use the DETAILED option for this argument.

The sample code to use this new dynamic management function is provided, as follows:

SELECT *
FROM sys.dm_db_database_page_allocations(
DB_ID('AdventureWorks2012'),
OBJECT_ID('HumanResources.Department'),
NULL , NULL , 'LIMITED')
GO

Sample part output from this command looks like below. Here since we have made the Index as NULL, we get records for both the indexes defined on this table.

SELECT *
FROM sys.dm_db_database_page_allocations(
DB_ID('AdventureWorks2012'),
OBJECT_ID('HumanResources.Department'),
NULL , NULL , 'DETAILED')
GO


Here is a detailed dump and partial additional fields like the Page_Type and page_type_desc which we can see. The sys.dm_db_database_page_allocations DMV also shows unallocated pages, system pages like PFS, GAM, IAM, and SGAM pages, as well as pages allocated to a table or index. DBCC IND only shows pages allocated to the table, so the results will not be one-to-one many-a-times if we were to compare. Also, this DMV returns information about the extent and can include pages still not allocated but the extent is allocated to this object. The best way is to check if the additional entries are there for is_allocated = 0 column.

Let me know if you have ever used this DMV before.

Reference: Pinal Dave (https://blog.sqlauthority.com)