Troubleshooting page resource waits

A new and exciting feature in SQL Server 2019 is sys.dm_db_page_info. This new dynamic management function (DMF) retrieves useful page information, such as page_id, file_id, index_id, object_id, and page_type, that can be used for troubleshooting and debugging performance issues in SQL Server. Historically, troubleshooting has involved the use of DBCC Page and the undocumented DMF sys.dm_db_page_allocations.

Unlike DBCC Page, which provides the entire contents of a page, sys.dm_db_page_info only returns header information about pages. Fortunately, this will be sufficient for most troubleshooting and performance tuning scenarios.

This section will discuss the following topics:

• Database State permissions
• sys.dm_db_page_info parameters
• New column page_resource in (sys.dm_exec_requests, sys.processes)
• sys.fn_PageResCracker

sys.dm_db_page_info

First, to leverage this new DMF, we require the VIEW DATABASE STATE permission. The following code can be used to provide access:

GRANT VIEW DATABASE STATE TO [login]

There are four required parameters:

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

The following argument descriptions are provided by docs.microsoft.com:

Table 1.29: The description of the arguments

You can execute the function by itself if you have all the requisite parameters. The mode is set to Limited in this example, and this will return NULL values for all description columns:

SELECT OBJECT_NAME(object_id) as TableName,* 
FROM SYS.dm_db_page_info(6, 1, 1368, 'Limited')

The output is as follows:

Figure 1.30: Output with LIMITED mode

Using the Detailed mode, you will get much more descriptive information than provided in the previous example. In this example, you can see that the NULL values have been replaced with descriptive information.

SELECT OBJECT_NAME(object_id) as TableName,* 
FROM SYS.dm_db_page_info(6, 1, 1368, 'Detailed')

The output is as follows:

Figure 1.31: Output with Detailed mode

To see a full list of all the columns returned, go to https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-page-info-transact-sql?view=sqlallproducts-allversions.

sys.fn_pagerescracker

In the previous example, you saw how to pass parameters to this new function manually. Fortunately, the parameters can be directly retrieved from sys.dm_exec_requests or sys.processes. To make this work, we added a new column called page_resource. The page_resource column returns the page ID, the file ID, and the database ID. It is also important to highlight that the new page_resource column in sys.dm_exec_request will be NULL when WAIT_RESOURCE does not have a valid value.

However, the page_resource column stores the data as an 8-byte hexadecimal value that needs to be converted. Therefore, we have added a new function called sys.fn_pagerescracker. This function returns the page ID, the file ID, and the database ID for the given page_resource value.

It is important to note that we require the user to have VIEW SERVER STATE permission on the server to run sys.fn_PageResCracker.

In this example, the page_resource column is being passed into the sys.fn_PageResCracker function, and then the database ID, file ID, and Page ID are passed to sys.dm_db_page_info:

SELECT OBJECT_NAME(page_info.object_id) AS TableName,page_info.* 
FROM sys.dm_exec_requests AS d 
CROSS APPLY sys.fn_PageResCracker (d.page_resource) AS r 
CROSS APPLY sys.dm_db_page_info(r.db_id, r.file_id, r.page_id, 
'Detailed') AS page_info

The output is as follows:

Figure 1.32: Page resource column is being passed into a function

You can read more here: https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-pagerescracker-transact-sql?view=sql-server-2017.