sys.fn_PageResCracker (Transact-SQL)

THIS TOPIC APPLIES TO:yesSQL Server noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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

Topic link icon Transact-SQL Syntax Conventions

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, 1) AS page_info

See Also

sys.dm_db_page_info (Transact-SQL)
sys.sysprocesses (Transact-SQL)
sys.dm_exec_requests (Transact-SQL)