SQL Server 2019 has some new and very interesting functions to identify information about pages. Many operations, such as analyzing current activities, locks or deadlocks, may result in some metadata pointing/blaming one page in the database.
However, how to translate this information to get a database and object name?
The image below is an example of information included in an XML_DEADLOCK_REPORT, only as an example. We can face information like this in other administrative tasks as well. How to translate this?
SQL Server 2019 brings the new function sys.dm_db_page_info that allow us to recover page information.
Take a look on this example:
Using sys.dm_db_page_info we can identify even the object which own the page.
SQL Server 2019 goes even beyond: sys.dm_exec_requests has a new field called page_resource with the binary identification of the page when the request is waiting/blocked by a page.
However, this identification is not on the format for the sys.dm_db_page_info, we need first to convert the format and we can do this using sys.fn_pagerescracker
Let’s see an example. We can create some locks to block a request and check the result.
Execute on one query window:
SELECT *
FROM customers WITH (updlock, paglock)
Execute on another query window:
SELECT *
FROM orders WITH (updlock, paglock)
SELECT *
FROM customers WITH (updlock, paglock)
The 2nd query window will be blocked by the customers lock on the first query window. The PAGLOCK hint forces the lock to be taken on the entire page only for this example, it’s not something to be used in production.
Use this query on a 3rd query window to confirm we have a request waiting for a page:
request_id,
page_resource
FROM sys.dm_exec_requests
WHERE page_resource IS NOT NULL
Now we can use the two other functions to identify the information about the page, including the object name:
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
WHERE d.page_resource IS NOT NULL
Load comments