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.
|
1 |
GRANT VIEW DATABASE STATE TO [login] |
To move further, let us create the sample database and data.
|
1 2 3 4 5 6 7 8 9 10 11 |
Create Database SQL2019 Go Use SQL2019 Go Create table DemoSQL2019 ( ID int identity(1,1), Name varchar(10) ) Go Insert into DemoSQL2019 Values ('SqlShack') |
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.
|
1 2 3 |
Select database_id,DB_name(database_id) as [Database], allocated_page_page_id , page_type_desc from sys.dm_db_database_page_allocations(DB_ID(),null,null,null,'Detailed') |
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.
Now we can view the information about any particular page from DBCC Page.
Below is the syntax for DBCC Page:
|
1 |
dbcc page ( {‘dbname’ | dbid}, filenum, pagenum [, printopt={0|1|2|3} ]) |
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.
|
1 2 |
DBCC TRACEON(3604) DBCC page (6,1,157,3) WITH TABLERESULTS |
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)
|
1 |
Select * from sys.dm_db_page_info(6,1,157,’limited’) |
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.
|
1 |
Select * from sys.dm_db_page_info(6,1,157,’Detailed’) |
If it is a data page, it will show the contents of the page along with the values for the columns.
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.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE dbo.SQLShackDemo( ID int IDENTITY(1,1) NOT NULL , Name VARCHAR(100) NOT NULL) GO CREATE UNIQUE CLUSTERED INDEX CIX_SQLShackDemo ON dbo.SQLShackDemo (ID) GO |
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.
This downloads the SqlQueryStress.exe as shown below.
Double click on the SqlQueryStress.exe. this launches the tool window as shown below.
Next, click on Database to enter the connection details like instance name, authentication mode, database name etc.
Let us run the below query in multiple numbers of threads and interactions. This will create the wait resources in the database.
|
1 2 3 4 5 6 7 8 |
SET NOCOUNT ON; DECLARE @i int = 1 WHILE @i < 100000 BEGIN INSERT INTO dbo.SQLShackDemo (Name) VALUES ('Rajendra Gupta') SET @i += 1 END; |
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.
|
1 |
GRANT VIEW Server STATE to [Username] |
Now let us run the below query in SQL Server Management Studio.
|
1 2 3 4 |
SELECT DMF.* FROM sys.dm_exec_requests AS DM CROSS APPLY sys.fn_PageResCracker (DM.page_resource) AS fn CROSS APPLY sys.dm_db_page_info(fn.db_id, fn.file_id, fn.page_id, 'Detailed') AS DMF |
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.
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023