Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Simplify SQL Server Database Development     ====>    Webcast Registration
 

New Function in SQL Server 2019 - sys.dm_db_page_info


By:   |   Read Comments   |   Related Tips: More > 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:

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:

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:

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.

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:



Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a Product Manager at SentryOne, 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 serves as a community moderator for the Database Administrators Stack Exchange.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools