Function Fn_PageResCracker Internals

In a previous blog post, I discussed two new methods to determine exactly which page a request might be waiting for when there is contention.  One of these new methods involves a new function, fn_pagerescracker.   Naturally, I wanted to see how this function operates.  Let’s look at the Master database to investigate how it works!

The function consumes a BINARY(8) parameter and returns a table.   This parameter represents a hexadecimal value that is constructed from the database ID, page ID, and file ID.

-------------------------------------------------------------------------------
-- Name: sys.fn_PageResCracker
--
-- Description:
--	Cracks the output of wait_resource page info
--
-- Notes:
-------------------------------------------------------------------------------
create function [sys].[fn_PageResCracker] (@physical_locator binary (8))
returns @dumploc_table table
(
	[db_id]	int not null,
	[file_id]	int not null,
	[page_id]	int not null
)
as
begin

	if @physical_locator is not null
	begin
		declare @db_id	binary (2)
		declare @file_id	binary (2)
		declare @page_id	binary (4)

		-- Page ID is the first four bytes, then 2 bytes of file ID, then 2 bytes of slot
		--
		select @page_id = convert (binary (4), reverse (substring (@physical_locator, 1, 4)))
		select @file_id = convert (binary (2), reverse (substring (@physical_locator, 5, 2)))
		select @db_id = convert (binary (2), reverse (substring (@physical_locator, 7, 2)))

		insert into @dumploc_table values (@db_id, @file_id, @page_id)
	end
	return
end
GO

You can see from the code that the parameter is then “diced” up with SUBSTRING commands and then converted into a small binary value.  Also, using SUBSTRING against a binary value (which is the parameter that is passed in) will return the number of bytes specified rather than the number of characters.  In this case, the Page ID is the first 4 bytes, the file ID is the following 2 bytes and finally the last 2 bytes represents the database ID.  Furthermore, when they are inserted into the table (which is returned to the calling query) it is then converted into an integer value.

Shown above, the page_resource value is 0x5001000001000600.  Now that we know how the function is going to work, we can do the math ourselves and confirm things.  Keep in mind that SQL Server utilizes big endians, so we will have to do some byte reversing.

Page ID = 0x50010000, byte reversed is 0x00000150 (or 0x150), which when converted to an integer = 336

File ID = 0x0100, again byte reversed is 0x0001 (or 0x1), which when converted to an integer is 1.

Database ID = 0x0600, reversed once again, 0x0006 (or 0x6), which converted to an integer is 6.

We can see below that we have now confirmed that the function is working as expected!

Summary

One of things I enjoy the most is digging into the internals on how SQL Server works.  It continues to teach me new and exciting things.  It also helps to make me a better DBA in my opinion.  In this case, the value add isn’t super steep but it’s still fun tearing apart the logic.

Enjoy!

© 2019, John Morehouse. All rights reserved.