sys.fn_PageResCracker (Transact-SQL)

Artikel
02/28/2023

Applies to: SQL Server 2019 (15.x)

Returns the db_id, file_id, and page_id for the given page_resource value.

Syntax

sys.fn_PageResCracker ( page_resource )

Arguments

page_resource
Is the 8-byte hexadecimal format of a database page resource.

Tables Returned

Column name	Data type	Description
db_id	int	Database ID
file_id	int	File ID
page_id	int	Page ID

Remarks

sys.fn_PageResCracker is used to convert the 8-byte hexadecimal representation of a database page to a rowset that contains the database ID, file ID and page ID of the page.

You can obtain a valid page resource from the page_resource column of the sys.dm_exec_requests (Transact-SQL) dynamic management view or the sys.sysprocesses (Transact-SQL) system view. If an invalid page resource is used then the return is NULL.
The primary use of sys.fn_PageResCracker is to facilitate joins between these views and the sys.dm_db_page_info (Transact-SQL) dynamic management function in order to obtain information about the page, such as the object to which it belongs.

Permissions

The user needs VIEW SERVER STATE permission on the server.

Examples

The sys.fn_PageResCracker function can be used in conjunction with sys.dm_db_page_info (Transact-SQL) to troubleshoot page related waits and blocking in SQL Server. The following script is an example of how you can use these functions to gather database page information for all active requests that are currently waiting on some type of page resource.

SELECT 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

Delen via