How to use the SQL Server sys.fn_PhysLocFormatter undocumented function
Written By: Matteo Lorini -- 1/21/2010
-- read/post comments
-- print --
Rating:
(not rated yet)
Rate
Problem
Suppose that my SELECT statement returns some corrupted data or some values are not stored correctly; for example, a varchar field that contains some Unicode characters. It would be nice if we could use the DBCC PAGE command to see exactly what it is stored at the page level, however, in order to do this we need to find a way to correlate the results returned from a SELECT statement with the physical data location.
Is that possible? The answer is: YES. All we need to do is use the sys.fn_physLocFormatter function. In this tip, I will cover how to use this undocumented function.
Solution
SQL 2008 comes with an undocumented function called sys.fn_physLocFormatter that helps us correlate the results returned by a SELECT statement with the physical location of the data.
To see what the function does, I ran the following:
sp_helptext sys.fn_physLocFormatter |
Below is the output from the above command.
-------------------------------------------------------------------------------
-- 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) + ')'
end |
To better understand how it can be used I wrote the following example on SQL 2008.
CREATE DATABASE MYTEST
GO
USE MYTEST
GO
CREATE TABLE MYTestTable(MYID INT IDENTITY, NAME CHAR(500), LNAME CHAR(500))
GO
INSERT INTO MYTestTable VALUES ('NAME-1','LNAME-1');
GO
INSERT INTO MYTestTable VALUES ('NAME-2','LNAME-2');
GO
INSERT INTO MYTestTable VALUES ('NAME-3','LNAME-3');
GO
INSERT INTO MYTestTable VALUES ('NAME-4','LNAME-4');
GO
INSERT INTO MYTestTable VALUES ('NAME-5','LNAME-5');
GO
INSERT INTO MYTestTable VALUES ('NAME-6','LNAME-6');
GO
INSERT INTO MYTestTable VALUES ('NAME-7','LNAME-7');
GO
INSERT INTO MYTestTable VALUES ('NAME-8','LNAME-8');
GO
INSERT INTO MYTestTable VALUES ('NAME-9','LNAME-9');
GO |
In order to see the physical location of my data, I ran the following:
SELECT sys.fn_PhysLocFormatter(%%physloc%%) as [Physical RID], *
FROM MYTestTable |
The values displayed in the Physical RID column represent the physical location of my data.
For example (1:1265:0) stands for:
- 1 is database file,
- 1265 is the page within the file and
- 0 is the slot number.
From the above example we can see that all my inserted data has been placed in two distinct pages 1237 and 1265.
To verify it, we can use the DBCC PAGE command as follows. This should be run in the database you are examining.
DBCC TRACEON(3604)
DBCC PAGE (MYTEST,1,1237,1) |
Below is the output from the above commands.
The above screenshot shows that page 1237 contains our values NAME-8, LNAME-8, NAME-9 and LNAME-9
To verify that all the other data is stored inside page 1265 I ran:
DBCC PAGE (MYTEST,1,1265,1) |
Below is the output from the above commands.
The above screenshot shows that page 1265 contains all our values from NAME-1 to LNAME-6.
Conclusion
I think that sys.fn_physLocFormatter offers nice functionality that allows us to investigate to the page level any kind of data inconsistency that we may come across.
Next Steps
- Since the sys.fn_physLocFormatter function is undocumented, one of the best sources for finding additional information on this function is MSDN blogs
- To learn more about DBCC PAGE, check http://www.mssqltips.com/tip.asp?tip=1578
Readers Who Read This Tip Also Read
Free Live Webcast
Comment or Ask Questions About This Tip
|
