Published on

October 23, 2014

Exploring SQL Server Page Allocation

Understanding how SQL Server manages page allocation is crucial for optimizing database storage and resolving space-related issues. In this article, we will explore the various ways to find and analyze page allocation using SQL Server’s built-in commands and dynamic management views.

One commonly used method to examine page allocation in older versions of SQL Server was through the undocumented commands DBCC IND and DBCC EXTENTINFO. However, in SQL Server 2012, a new dynamic management function called Sys.dm_db_database_page_allocations() was introduced to replace these commands.

The Sys.dm_db_database_page_allocations() function allows us to retrieve information about the pages and extents allocated for a specific table, index, or partition. By supplying the appropriate arguments, such as the database ID, object ID, and index ID, we can obtain detailed information about the allocation status of pages.

For example, to retrieve information about all pages and extents allocated to the “HumanResources.Department” table in the “AdventureWorks2012” database, we can use the following code:

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

This query will return a result set containing information about the allocated pages and extents for the specified table. By changing the mode argument to ‘DETAILED’, we can obtain additional details such as the page type and description:

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

It’s important to note that the Sys.dm_db_database_page_allocations() function also includes information about unallocated pages, system pages (e.g., PFS, GAM, IAM, and SGAM), and pages allocated to a table or index. This provides a comprehensive view of the page allocation within the database.

Compared to the DBCC IND command, which only shows pages allocated to a table, the Sys.dm_db_database_page_allocations() function offers a more comprehensive and flexible approach to analyzing page allocation. Additionally, the function allows for advanced filtering and grouping without the need to import the output into a temporary table.

If you encounter situations where pages are not yet allocated but the extent is allocated to an object, you can check the “is_allocated” column to identify these cases.

In conclusion, understanding SQL Server’s page allocation mechanisms is essential for efficient database management. By utilizing the Sys.dm_db_database_page_allocations() function, you can gain valuable insights into the allocation status of pages and optimize your database storage effectively.

Have you ever used the Sys.dm_db_database_page_allocations() function before? Share your experiences and insights in the comments below!

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.