Rajendra Gupta
Execute DMV query sys.dm_db_database_page_allocations to get page details

SQL Server 2019 – New DMF sys.dm_db_page_info

October 22, 2018 by

Microsoft released preview of SQL Server 2019 recently in Ignite 2018. With every release of SQL Server is enriched with new dynamic management view and functions along with enhancements to existing features.

In this article, we will view the newly introduced dynamic management function (DMF) sys.dm_db_page_info and explore the different scenarios around it.

Overview of sys.dm_db_page_info

SQL Server 2019 provides new database management function ‘sys. dm_db_page_info’ to get information about the pages in SQL Server. Prior to SQL Server 2019, we use undocumented dynamic management function sys.dm_db_database_page_allocations and DBCC Page to get details information about the page in SQL Server for the tables, indexes.

Dynamic Management function sys.dm_db_page_info DMF is introduced to replace DBCC Page function but this DMF returns only page header information while DBCC page shows complete details of the page. We will see this later in the demo section.

Before we move further, let us take a look of new dynamic management function (DMF) syntax and parameters.

Syntax for sys.dm_db_page_info

sys.dm_db_page_info ( DatabaseId, FileId, PageId, Mode )

Below are the parameters to pass into sys.dm_db_page_info.

Argument

Description

Null allowed

Allowed Values

DatabaseId

It is unique ID of the database

No

DBID, or we can use DB_ID() for the current database

FileId

It is ID of the database file

No

DB file id

PageId

It is ID of the page we want to examine

No

PageID

Mode

It shows the level of detail in the output of the function

No

LIMITED– No information about description columns

DETAILED– it gives information about detailed columns as well

We require the VIEW DATABASE STATE permission in the database. We can provide permissions to run with below command.

To move further, let us create the sample database and data.

Create database and table to prepare for the demo.

In this article, we will take an overview of new DMF sys.dm_db_page_info along and compare it with the previously used DMF sys.dm_db_database_page_allocations.

Now let us view the information about all the pages and extents allocated for the table using the DMF sys.dm_db_database_page_allocations. This is undocumented DMF introduced in SQL Server 2012.

Syntax for sys.dm_db_database_page_allocations:

sys.dm_db_database_page_allocations

(@DatabaseId , @TableId , @IndexId , @PartionID , @Mode)

Argument

Description

Null allowed

Allowed Values

DatabaseId

It is unique ID of the database.

No

DB ID

TableID

It is ID of the database file.

Yes

Table ID or Null

IndexID

It is ID of the page we want to examine

Yes

Index ID or Null

PartionID

We can pass PatitionID if we require.

Yes

partitionID or Null

Mode

It shows the level of detail in the output of the function

No

LIMITED– If we use this parameter, it does not show any information about description columns.

DETAILED– it gives information about detailed columns as well.

We cannot provide Null value in this parameter.

Now run the below query to get information about allocated page id and page type description.

In the above query, we can directly pass the DB ID as well. We can get the DB ID from the sp_helpdb or sys.sysdatabases.

Execute DMV query  sys.dm_db_database_page_allocations to get page details

Now we can view the information about any particular page from DBCC Page.

Below is the syntax for DBCC Page:

Printout values can be as:

  • 0 – Prints only page header related information
  • 1 – Prints page header and page slot array dump with hex dump for each row
  • 2 – Prints page header and whole page hex dump
  • 3 – Prints detailed information of per row along with page header

Note: We need to turn on trace flag 3604 for DBCC Page to show output in SQL Server Management Studio.

View page information using DBCC Page

We can see that DBCC Page gives detailed information about the page.

Now if we view the same thing from the newly introduced sys.dm_db_page_info. We will use the same page id as used before for a comparison purpose.

We can see here that sys.dm_db_page_info gives one row with all the page information for a given parameter in the detailed mode as specified by us.

Note here, we are using the mode as limited so we can see there are some columns showing null values (highlighted by blue arrow)

Execute and view output of new DMF sys.dm_db_page_info

Important columns in Sys.dm_db_page_info are:

Column

Description

database_id

Database ID

page_type_desc

Page type description such as data page, index page, IAM page

page_level

It shows the Level of the page in the index. For a leaf level, its value is 0

slot_count

It shows the total slot counts. It is number of rows for a data page,

ghost_rec_count

A number of ghost records that are marked for deletion.

is_iam_pg

If that particular page is IAM page, its value is 1

is_mixed_ext

This indicates whether the extent is mixed extent or not

free_bytes

Free bytes on the page

Lsn

Log sequence number

diff_map_page_id

It shows the Page ID of the corresponding differential bitmap page

header_version

Page header version

Now let us run the same query with mode ‘Detailed’. We can see here that the description columns also showing the data. Therefore, we can use the mode as per our requirement.

Execute and view output of new DMF with detailed mode

If it is a data page, it will show the contents of the page along with the values for the columns.

View page insights from DBCC Page for data pages

View the data page from new DMF sys.dn_db_page_info

Join sys.dm_db_page_info with DMVs

We can join the sys.dm_db_page_info with other dynamic management views to get information about the page. In SQL Server 2019, sys.dm_exec_processes and sys.sysprocesses contains a new column page_resource and we can use function sys.fn_PageResCracker to get information about the database id, file id along with the page id.

Let us create another table and index on our table in order to explore the join sys.dm_db_page_info.

Create table and cluster index on the table

Now we will use SqlQueryStress simulator created by Adam Machanic. We will run the query through this tool with multiple threads to put a load into our database. You can download this tool from the link.

SqlQueryStress Tool on Glithub

This downloads the SqlQueryStress.exe as shown below.

Downloaed SqlQueryStress.exe

Double click on the SqlQueryStress.exe. this launches the tool window as shown below.

Launch screen of SqlQueryStress tool

Next, click on Database to enter the connection details like instance name, authentication mode, database name etc.

SqlQueryStress tool configuration for Database details

Let us run the below query in multiple numbers of threads and interactions. This will create the wait resources in the database.

Run the load on SqlQueryStress with multple Iterations and threads

Now, we will join the sys.dm_db_page_info with the DMV sys.dm_exec_requests and the function sys.fn_PageResCracker. The function sys.fn_ PageResCracker takes input of page_resource from the sys.dm_exec_requests. page_resource is 8-byte hexadecimal representation of a database page.

We require the VIEW Server STATE permissions to run the sys.fn_PageResCracker. We can grant permission using below script.

Now let us run the below query in SQL Server Management Studio.

We can see below the output showing the one row per wait_resource from the sys.dm_exec_requests when the row contains a non-null page_resource.

Join DMF (sys.dm_db_page_info) with other DMV

Conclusion

SQL Server 2019 provides many enhancements and a new feature to troubleshoot the issues. We can use this DMF to get information about a specific page or to get information after joining from multiple DMV’s. Explore this dynamic management function to get a hands-on experience.


Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
503 Views