SQL Server 2008 has presented us a couple of options to aid in becoming better DBA’s. You can see this evidenced in many ways in the product. A couple of the things that make me think this is the case boils down to two functions that are new in SQL 2008. I learned about these while trying to learn how to do something else. It just so happens that these functions could possibly help me in the other process (I’ll write more about that later when I have finished it).
These new functions are: sys.fn_PhysLocFormatter and sys.fn_PhysLocCracker. The two functions are really very similar. The first of the two does as the name implies and formats the physical location, while the second of the two provides a table output of the location. If you look at the sp_helptext of both, you can see that they only have minor differences.
-------------------------------------------------------------------------------
-- Name: sys.fn_PhysLocFormatter
--
-- Description:
-- Formats the output of %%physloc%% virtual column
--
-- Notes:
-------------------------------------------------------------------------------
CREATE FUNCTION sys.fn_PhysLocFormatter (@physical_locator BINARY (8))
RETURNS VARCHAR (128)
AS
BEGIN
DECLARE @page_id BINARY (4)
DECLARE @FILE_ID BINARY (2)
DECLARE @slot_id BINARY (2)
-- Page ID is the first four bytes, then 2 bytes of page 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 @slot_id = CONVERT (BINARY (2), REVERSE (SUBSTRING (@physical_locator, 7, 2)))
RETURN '(' + CAST (CAST (@FILE_ID AS INT) AS VARCHAR) + ':' +
CAST (CAST (@page_id AS INT) AS VARCHAR) + ':' +
CAST (CAST (@slot_id AS INT) AS VARCHAR) + ')'
ENDand
-------------------------------------------------------------------------------
-- Name: sys.fn_PhysLocCracker
--
-- Description:
-- Cracks the output of %%physloc%% virtual column
--
-- Notes:
-------------------------------------------------------------------------------
CREATE FUNCTION sys.fn_PhysLocCracker (@physical_locator BINARY (8))
RETURNS @dumploc_table TABLE
(
[FILE_ID] INT not null,
[page_id] INT not null,
[slot_id] INT not null
)
AS
BEGIN
DECLARE @page_id BINARY (4)
DECLARE @FILE_ID BINARY (2)
DECLARE @slot_id BINARY (2)
-- Page ID is the first four bytes, then 2 bytes of page 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 @slot_id = CONVERT (BINARY (2), REVERSE (SUBSTRING (@physical_locator, 7, 2)))
INSERT INTO @dumploc_table VALUES (@FILE_ID, @page_id, @slot_id)
RETURN
END
When you look at these two functions, you can easily say that they are similar right up until the end where they diverge in functionality. The first casts the data into the “formatted” version, while the cracker simply outputs to a table.
Use of these functions is also quite easy.
SELECT TOP (10)
yt.*,
pl.FILE_ID, pl.page_id, pl.slot_id
FROM yourtable AS yt
cross apply sys.fn_PhysLocCracker(%%physloc%%) AS pl;
SELECT TOP 10
sys.fn_PhysLocFormatter (%%physloc%%) AS [Physical RID], yt.*
FROM yourtable yt;
These functions can prove to be very helpful in your troubleshooting or dives into Internals. Check them out and enjoy.
