New Function in SQL Server 2019 - sys.dm_db_page_info

By:   |   Comments   |   Related: > SQL Server 2019


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.


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:

  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:

  page_id = allocated_page_page_id,
FROM sys.dm_db_database_page_allocations
WHERE is_allocated = 1;			


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.

  [schema] = OBJECT_SCHEMA_NAME([object_id]),
  [object] = OBJECT_NAME([object_id])
FROM sys.dm_db_page_info


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:

FROM sys.dm_db_page_info


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.


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:


filler char(7000) NOT NULL DEFAULT ''

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
WHERE page_type = 1 -- data page

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:

  @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:

Comparison of old and new DMF

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:

  @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:

Giving the old DMF a fighting chance

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:

Plan for new DMF

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:

Redundant filter leads to better estimate for new DMF

This is simply because the WHERE clause added a filter that the argument to the function couldn’t:

Plan for new DMF showing filter

If you mouse over the filter you can see that the filter comes up with a much more accurate estimate:

Tooltip confirms the effect of the filter

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.


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:

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Aaron Bertrand 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,, and also blogs at

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms