Three columns resource_0, resource_1, and resource_2, returned from Extended Events which monitors the locking behavior,lock_acquired and lock_released, are always puzzling people. Actually, information of it already exposed in page 264, book Microsoft SQL Server 2008 Internals, wirtten by Kalen Delaney. In my first post of this series, I’ve also talked little bit about it. I decided to write a function to help people translate them into a human readable form. This function now can recognize lock resource for Object, HOBT, Page, Extent, RID, and KEY. for others, such as DATABASE and FILE, the formats are very straight forward, I did not include them in the function.
Resource_0, 1, and 2 have different meaning for different resource type. Each of which is an unsigned integer value. The parameteres of the function passes them as bigint into the function. This is just in case the value passing in exceeds the value an regular integer type variable can handle. Meaning of the returning value depends on the value in ResourceType.
- OBJECT: return Object ID
- PAGE/EXETNT: return Page or Extent address
- HOBT: return partition id of the object
- RID: return address of the row, format is file:page:slot
- KEY: return (key hash)/parition_id, for instance, (992da965bcee)/72057594038779904, it means partition_id = 72057594038779904, key hash = (992da965bcee). You can use key hash value to position a record. for instance
select * from SimpleTable where %%lockres%% = '(992da965bcee)'
Code is below. In the future posts, I will use this function frequently but I will not re-post it event I reference this function in my code.
USE [master]
GO
create function [dbo].[ConvertedLockResource](@ResourceType sysname, @res0 bigint, @res1 bigint, @res2 bigint)
returns varchar(60)
as
begin
if @ResourceType = 'OBJECT'
return cast(@res0 as varchar(20));
else if @ResourceType in ('PAGE', 'EXTENT')
begin
return cast(@res1 as varchar(10)) + ':' + cast(@res0 as varchar(20))
end
else if @ResourceType = 'RID'
begin
return cast(cast(cast(right(cast(@res1 as binary(8)),2) as binary(2)) as smallint) as varchar(10))+ ':'
+ cast(@res0 as varchar(20))+':'
+ cast(cast(cast(left(right(cast(@res1 as binary(8)),4), 2) as binary(2)) as smallint) as varchar(10))
end
else if @ResourceType = 'HOBT'
begin
return cast(cast(
cast(right(cast(right(cast(@res1 as binary(8)),4) as binary(4)), 2) as binary(2))
+cast(0x0000 as binary(2))
+ cast(right(cast(right(cast(@res0 as binary(8)),4) as binary(4)), 2) as binary(2))
+ cast(left(cast(right(cast(@res0 as binary(8)),4) as binary(4)), 2) as binary(2))
as bigint)
as varchar(20))
end
else if @ResourceType = 'KEY'
begin
return '('
+ lower(convert( varchar(20),
cast(substring(cast(@res1 as binary(8)), 6, 1) as binary(1))
+ cast(substring(cast(@res1 as binary(8)), 5, 1) as binary(1))
+ cast(substring(cast(@res2 as binary(8)),8, 1) as binary(1))
+ cast(substring(cast(@res2 as binary(8)),7, 1) as binary(1))
+ cast(substring(cast(@res2 as binary(8)),6, 1) as binary(1))
+ cast(substring(cast(@res2 as binary(8)),5, 1) as binary(1))
,2))
+')/'
+ cast(cast(
cast(right(cast(right(cast(@res1 as binary(8)),4) as binary(4)), 2) as binary(2))
+cast(0x0000 as binary(2))
+ cast(right(cast(right(cast(@res0 as binary(8)),4) as binary(4)), 2) as binary(2))
+ cast(left(cast(right(cast(@res0 as binary(8)),4) as binary(4)), 2) as binary(2))
as bigint)
as varchar(20))
end
return null
end