%%lockres% and %%physloc%%

These two can be seen as a scalar function return different values ??in different rows. The following description of the test environment are as follows: 

if object_id ('ta') is not null 
drop table ta 
if object_id ('tb') is not null 
drop table tb 
go 
create table ta (id int, col1 varchar (20)) 
create table tb (id int, col2 varchar (20)) 
create unique clustered index c1 on tb (id) 
create nonclustered index nc1 on tb (col2) 
go 
insert into ta values (1, 'a') 
insert into ta values (2, 'b') 
insert into ta values (3, 'c') 
go 
insert into tb values (1, 'a') 
insert into tb values (2, 'b') 
insert into tb values (3, 'c') 

Go 

A. %% lockres% 

Appears in the SQL Server 2005 and above versions.

Observed: only leaf nodes (clustered index and non-clustered index) and data pages (heap, no clustered index) need to lock. Non-leaf node is not locked (why?) 
How to lock said key lock (KEY LOCK) key? Your initial answer of course is the key, but the number of columns in the index, but also different types, so he did not fit the representation. SQL SERVER using a hash algorithm, a 6-byte string generated by the key, key lock locked by the 6-byte key. 
It was found that: the algorithm should be used only with the key value. 
We can of%% lockres% to view the hash string. The string value and key, so multiple index uses different keys (leaf nodes) are not the same, so the same data line of%% lockres%% value. 

select *,%% lockres%% from tb with (index = c1) 
Results: 
id col2 
-------------------------------------------------- ------------- 
1 a (010086470766) 
2 b (020068e8b274) 
3 c (03000d8f0ecc) 

select *,%% lockres%% from tb with (index = nc1) 
Results: 
id col2 
-------------------------------------------------- ------------- 
1 a (62007a313325) 
2 b (640044e42670) 
3 c (660091aafaf5) 

Note: When the plan for the heap scan (ie, without using an index), of%% lockres%% of the value of RID format file ID: page ID: line ID. 
select *,%% lockres%% from ta 
Results: 
id col1 
-------------------------------------------------- ------------- 
1 a 1:77:0 
2 b 1:77:1 
3 c 1:77:2 

II. %% physloc% 
Appears in the SQL Server 2008 and above. 

It is noted that the physical address of the data row, the format is binary (8),

1 to 4-byte pageno (page ID),

5 to 6 bytes the fileno (File ID),

7 to 8 bytes slotno (line ID)

the different from %%lockres%, regardless of whether the clustered index, the %%physloc%% always indicate the physical address,

if you want to display common file / data page / line format

used the following function: 

1) use sys.fn_physlocformatter function%% physloc%% converted to a (file: Page: slot) format string. 

2) If you need to file / page / slot can be used on a the fn_PhysLocCracker function. 

select *,%% physloc%% as phy from ta 

Results: 

id col1 phy 
------------------------------------------------- 
1 a 0x5900000001000000 
2 b 0x5900000001000100 
3 C 0x5900000001000200 

select *,%% physloc%% as phy from tb 

Results: id col2 phy ------------------------------------------------- 

1 a 0x5D00000001000000 

2 b 0x5D00000001000100 

Of c 0x5D00000001000200 3 

After conversion: 
select *,%% physloc%% as phy, sys.fn_physlocformatter (%% physloc%%) as phy1 
from ta 

Results: 
id col1 phy phy1 
-------------------------------------------------- --------- 
1 a 0x5900000001000000 (1:89:0) 
2 b 0x5900000001000100 (1:89:1) 
3 c 0x5900000001000200 (1:89:2) 

select a. *,%% physloc%% as phy, b. * 
from ta a 
cross apply fn_PhysLocCracker (%% physloc%%) b 

Results: 
id col1 phy file_id page_id slot_id 
-------------------------------------------------- ---------------------- 
1 a 0x5900000001000000 1 89 0 
2 b 0x5900000001000100 1 89 1 
3 c 0x5900000001000200 1 89 2 

III. References 
1. Microsoft Sql server 2008 internals, p264, p626 
2. http://www.sqlskills.com/blogs/paul/post/SQL-Server-2008-New-%28undocumented%29-physical-row-locator-function.aspx
3. http://sqlfool.com/2009/09/undocumented-function-in-sql-2008/

from http://www.databaseskill.com/4350894/

 

IV. More about this

-- 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

 

 

 

-- 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

 

the %%physloc%% and %%lockres%% can have pre-qaulifier

SELECT sys.fn_PhysLocFormatter (%%physloc%%) AS [Physical RID], * FROM TEST;
GO
SELECT b.* FROM TEST a inner join t1 on a.c1 = t1.c1 cross apply sys.fn_PhysLocCracker(a.%%physloc%%) b;
GO
SELECT b.*, a.%%lockres%% as lockres FROM TEST a inner join t1 on a.c1 = t1.c1 cross apply sys.fn_PhysLocCracker(t1.%%physloc%%) b;
GO
select b.*, a.%%lockres%% from TEST a inner join t1 on a.c1 = t1.c1 cross apply sys.fn_PhysLocCracker(t1.%%physloc%%) b
where a.%%lockres%% ='1:114:0'
GO