By: Aaron Bertrand | Updated: 2018-10-08 | Comments | Related: > SQL Server 2019
Problem
Back when SQL Server 2012 was released, Microsoft introduced a new dynamic management
function (DMF), called sys.dm_db_database_page_allocations. The purpose
of this DMF is to help replace DBCC PAGE and DBCC IND –
undocumented commands designed to inspect the pages of an index or a table. This
is something you might need when you know about a resource by its page number, but
you don’t know the object it belongs to; for example, reviewing suspect pages,
deadlocks, or blocked processes.
There are two problems with this DMF, however. One is that, like the DBCC commands, it is both undocumented and unsupported, which means its behavior could change, or it could be removed from the product altogether, without warning. The other is that it reads all of the page structure for the entire table or index, even when you are filtering for a single page; as databases get larger, this can become problematic.
Solution
In SQL Server 2019, we have a new and improved DMF, called sys.dm_db_page_info.
This new DMF takes a similar set of arguments as the allocations DMF, still allowing
you to identify an object by its page, but can be persuaded to do so more efficiently.
Let’s take a quick look at how it works. We’ll create a simple table
with one row:
CREATE TABLE dbo.Users ( UserID int IDENTITY(1,1) PRIMARY KEY, name sysname ); INSERT dbo.Users(name) SELECT TOP (1) name FROM sys.all_objects;
Now, this might seem a little backward at first, but we’re going to use the old DMF to find the pages associated with this object. The output of the allocations DMF looks like this:
name data type --------------------------- ------------- database_id int object_id int index_id int partition_id int rowset_id bigint allocation_unit_id bigint allocation_unit_type int allocation_unit_type_desc nvarchar(60) data_clone_id int clone_state int clone_state_desc nvarchar(9) extent_file_id smallint extent_page_id int allocated_page_iam_file_id smallint allocated_page_iam_page_id int allocated_page_file_id smallint allocated_page_page_id int is_allocated tinyint is_iam_page tinyint is_mixed_page_allocation tinyint page_free_space_percent int page_type int page_type_desc nvarchar(256) page_level tinyint next_page_file_id smallint next_page_page_id int previous_page_file_id smallint previous_page_page_id int is_page_compressed tinyint has_ghost_records tinyint
Lots of interesting things to potentially look at there, but today we only need a few of those columns. The arguments here are database id, object id, index id, partition id, and mode:
SELECT page_id = allocated_page_page_id, index_id, page_type_desc FROM sys.dm_db_database_page_allocations ( DB_ID(), OBJECT_ID(N'dbo.Users'), NULL, NULL, N'DETAILED' ) WHERE is_allocated = 1;
Results:
page_id index_id page_type_desc -------- --------- -------------- 237 1 IAM_PAGE 304 1 DATA_PAGE
There are eight data pages, but the ones filtered out have not been allocated. So now we know the pages we’re after, and in fact only one page is important here (304).
Next, we can pass this page into the new DMF. The output from the new function looks like this:
name data type ------------------------------ ------------- database_id int file_id int page_id int page_header_version int page_type int page_type_desc nvarchar(256) page_type_flag_bits nvarchar(65) page_type_flag_bits_desc nvarchar(257) page_flag_bits nvarchar(65) page_flag_bits_desc nvarchar(257) page_lsn nvarchar(65) page_level tinyint object_id int index_id int partition_id bigint alloc_unit_id bigint is_encrypted bit has_checksum bit checksum int is_iam_page bit is_mixed_extent bit has_ghost_records bit has_version_records bit has_persisted_version_records bit pfs_page_id int pfs_is_allocated bit pfs_alloc_percent int pfs_status nvarchar(65) pfs_status_desc nvarchar(257) gam_page_id int gam_status bit gam_status_desc nvarchar(65) sgam_page_id int sgam_status bit sgam_status_desc nvarchar(65) diff_map_page_id int diff_status bit diff_status_desc nvarchar(65) ml_map_page_id int ml_status bit ml_status_desc nvarchar(65) prev_page_file_id smallint prev_page_page_id int next_page_file_id smallint next_page_page_id int fixed_length smallint slot_count smallint ghost_rec_count smallint free_bytes smallint free_bytes_offset smallint reserved_bytes smallint reserved_bytes_by_xdes_id smallint xdes_id nvarchar(65)
That’s a big list, mimicking a lot of the data that is written directly to the page structure. But again, we’re typically only going to be interested in a few columns (namely object_id and index_id). The arguments you pass in to the function are database id, file id, page id, and mode.
SELECT [schema] = OBJECT_SCHEMA_NAME([object_id]), [object] = OBJECT_NAME([object_id]) index_id FROM sys.dm_db_page_info ( DB_ID(), 1, 304, N'LIMITED' );
Results:
schema object index_id ------- ------- -------- dbo Users 1
You just need to know the database id, the file id, and the page number, and then you can derive other information like object and index.
In current CTPs, you can’t use the new DMF to build a list of pages for a database. If you try:
SELECT * FROM sys.dm_db_page_info ( DB_ID(), 1, NULL, N'LIMITED' );
Results:
Msg 2561, Level 16, State 1, Line 34 Parameter 3 is incorrect for this statement.
Maybe this was intentional, or maybe it will be fixed by RTM.
Performance
The big difference you’ll notice on larger tables is how long the DMF takes. Well, you’ll need to follow some guidance in order to get the biggest performance benefit. Let’s say we have a table with 10,000 pages:
SET NOCOUNT ON; GO CREATE TABLE dbo.Bane ( filler char(7000) NOT NULL DEFAULT '' ); GO INSERT dbo.Bane DEFAULT VALUES; GO 10000
Now, pretending we already know a specific page due to a deadlock or a suspect page, let’s just grab a random page id using tricks we already know:
SELECT TOP (1) allocated_page_page_id FROM sys.dm_db_database_page_allocations ( DB_ID(), OBJECT_ID(N'dbo.Bane'), NULL, NULL, N'DETAILED' ) WHERE page_type = 1 -- data page ORDER BY NEWID() DESC;
The result in this case was 3836 (if you’re trying this at home, you may get a different page). Don’t worry, I’m not measuring performance yet; this is going to set us up to show the difference that can happen when predicates are pushed down as far as possible.
Given a page id of 3836, we can compare the performance of these two queries:
DECLARE @dbid int = DB_ID(), @fileid int = 1, @pageid int = 3836, @objid int = OBJECT_ID(N'dbo.Bane'); SELECT /* old DMF */ object_id, index_id FROM sys.dm_db_database_page_allocations(@dbid, NULL, NULL, NULL, N'LIMITED') WHERE allocated_page_page_id = @pageid; SELECT /* new DMF */ object_id, index_id FROM sys.dm_db_page_info(@dbid, 1, @pageid, N'LIMITED');
Results, as shown in SentryOne Plan Explorer:
You can see that the old DMF takes 10 times as long and has almost 500 times as many reads. And you might think, well, it’s kind of unfair – since we already know the object we’re after, couldn’t we pass the object id into the function and give it a better chance to seek to the right page + object data? Well, I tried that:
It eliminated a few reads, but didn’t really change the outcome:
DECLARE @dbid int = DB_ID(), @fileid int = 1, @pageid int = 3836, @objid int = OBJECT_ID(N'dbo.Bane'); SELECT /* old DMF */ object_id, index_id FROM sys.dm_db_database_page_allocations(@dbid, NULL, NULL, NULL, N'LIMITED') WHERE allocated_page_page_id = @pageid; SELECT /* old DMF with object */ object_id, index_id FROM sys.dm_db_database_page_allocations(@dbid, @objid, NULL, NULL, N'LIMITED') WHERE allocated_page_page_id = @pageid; SELECT /* new DMF */ object_id, index_id FROM sys.dm_db_page_info(@dbid, 1, @pageid, N'LIMITED');
Still doesn’t look great for the old DMF, and this wouldn’t be a very feel-good solution anyway, since we usually need this function precisely because we don’t know the object:
Clearly the new DMF is still on top. One thing that bothered me, though, is that wacky 1,000-row estimate. I wondered if there was any way to make that more accurate, so I took a look at the plan:
Not much I can do about indexes or statistics on system objects, never mind totally-off-limits internal table-valued functions. But I tried a few things on the query, and adding a redundant predicate seemed to do the trick:
SELECT /* new DMF with filter */ object_id, index_id FROM sys.dm_db_page_info(@dbid, 1, @pageid, N'LIMITED') WHERE page_id = @pageid; -- redundant
Now the results looked like this:
This is simply because the WHERE clause added a filter that the argument to the function couldn’t:
If you mouse over the filter you can see that the filter comes up with a much more accurate estimate:
At this case small scale, the better estimate didn’t yield an observable change in runtime, but in larger environments this may work out differently.
Summary
SQL Server 2019 offers a new (and hopefully documented and supported) way to troubleshoot issues where you don’t know what object is involved. As I’ve shown here, though, at least in current CTP builds, you will be best served by adding a WHERE clause that specifies the page id you’re looking for, when you know it. There are other ways to derive info from this DMF even when you don’t know a single specific page, and also other purposes to use the output, which I’ll explore in a future tip.
Next Steps
Read on for related tips and other resources:
- My overview of features in SQL Server 2019 CTP 2.0
- All SQL Server 2019 Tips
- Finding a table name from a page ID
- Using DBCC PAGE to Examine SQL Server Table and Index Data
- Understanding how SQL Server stores data in data files
- The sys.dm_db_database_page_allocations DMF
- Troubleshooting and Fixing SQL Server Page Level Corruption
- Identify and Correct SQL Server Forwarded Records
About the author
Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2018-10-08
