Identifying Page Information in SQL Server 2019

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:

select objecT_name(objecT_id),* from sys.dm_db_page_info(9,1,392,‘DETAILED’)

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:

BEGIN TRANSACTION
SELECT *
FROM   customers WITH (updlock, paglock) 

Execute on another query window:

BEGIN TRANSACTION
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:

SELECT session_id,
       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:

SELECT Object_name(page_info.object_id) AS object_name,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
WHERE  d.page_resource IS NOT NULL 

 

 

References

sys.fn_PageResCraker

sys.dm_db_page_info

 

The post Identifying Page Information in SQL Server 2019 appeared first on Simple Talk.

from Simple Talk https://ift.tt/2ToisGa
via