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

How to interpret resource_0 when resource_type = ‘METADATA’ ?

Thanks.

Hello Mr. John Huang ^^

Thank you for really good information.

However, this function needs to be modified a bit, I guess.

I have an issue right function.

So I think you need to use substring, by replacing.

I think the issue with the following statement:

If you use the right binary function. The binary is converted into a varchar or nvarchar and is calculated as the number of characters.

If you use a binary function substing. The length is calculated in bytes.

DECLARE @RES1 BIGINT = 3588799347, @BIN BINARY(8);

SELECT @BIN = CONVERT(BINARY(8),@RES1);

SELECT @BIN;

SELECT CONVERT(BINARY(2),RIGHT(@BIN,2)); — Wrong

SELECT CONVERT(BINARY(1),SUBSTRING(@BIN,7,1))+CONVERT(BINARY(1),SUBSTRING(@BIN,8,1)); — Correct

I think last modified the function is shown below:

CREATE FUNCTION DBO.CONVERTEDLOCKRESOURCE (@RESOURCETYPE SYSNAME, @RES0 BIGINT, @RES1 BIGINT, @RES2 BIGINT)

RETURNS VARCHAR(60)

AS

BEGIN

IF @RESOURCETYPE = ‘OBJECT’

RETURN CONVERT(VARCHAR(20),@RES0);

ELSE IF @RESOURCETYPE IN (‘PAGE’, ‘EXTENT’)

BEGIN

RETURN CONVERT(VARCHAR(10),@RES1) + ‘:’ + CONVERT(VARCHAR(20),@RES0)

END

ELSE IF @RESOURCETYPE = ‘RID’

BEGIN

RETURN CONVERT(VARCHAR(10),CONVERT(SMALLINT,CONVERT(BINARY(1),SUBSTRING(CONVERT(BINARY(8),@RES1),7,1))+CONVERT(BINARY(1),SUBSTRING(CONVERT(BINARY(8),@RES1),8,1)))) + ‘:’

+ CONVERT(VARCHAR(20),@RES0)+’:’

+ CONVERT(VARCHAR(10),CONVERT(SMALLINT,CONVERT(BINARY(1),SUBSTRING(CONVERT(BINARY(8),@RES1),5,1))+CONVERT(BINARY(1),SUBSTRING(CONVERT(BINARY(8),@RES1),6,1))))

END

ELSE IF @RESOURCETYPE = ‘HOBT’

BEGIN

RETURN CONVERT(VARCHAR(20),CONVERT(BIGINT,

CONVERT(BINARY(1),SUBSTRING(CONVERT(BINARY(8),@RES1),7,1))

+CONVERT(BINARY(1),SUBSTRING(CONVERT(BINARY(8),@RES1),8,1))

+CAST(0X0000 AS BINARY(2))

+CONVERT(BINARY(1),SUBSTRING(CONVERT(BINARY(8),@RES0),7,1))

+CONVERT(BINARY(1),SUBSTRING(CONVERT(BINARY(8),@RES0),8,1))

+CONVERT(BINARY(1),SUBSTRING(CONVERT(BINARY(8),@RES0),5,1))

+CONVERT(BINARY(1),SUBSTRING(CONVERT(BINARY(8),@RES0),6,1))

)

)

END

ELSE IF @RESOURCETYPE = ‘KEY’

BEGIN

RETURN ‘(‘

+ LOWER(CONVERT( VARCHAR(20),

CONVERT(BINARY(1),SUBSTRING(CONVERT(BINARY(8),@RES1),6,1))

+ CONVERT(BINARY(1),SUBSTRING(CONVERT(BINARY(8),@RES1),5,1))

+ CONVERT(BINARY(1),SUBSTRING(CONVERT(BINARY(8),@RES2),8,1))

+ CONVERT(BINARY(1),SUBSTRING(CONVERT(BINARY(8),@RES2),7,1))

+ CONVERT(BINARY(1),SUBSTRING(CONVERT(BINARY(8),@RES2),6,1))

+ CONVERT(BINARY(1),SUBSTRING(CONVERT(BINARY(8),@RES2),5,1))

,2))

+’)/’

+ CONVERT(VARCHAR(20),CONVERT(BIGINT,

CONVERT(BINARY(1),SUBSTRING(CONVERT(BINARY(8),@RES1),7,1))

+CONVERT(BINARY(1),SUBSTRING(CONVERT(BINARY(8),@RES1),8,1))

+CAST(0X0000 AS BINARY(2))

+CONVERT(BINARY(1),SUBSTRING(CONVERT(BINARY(8),@RES0),7,1))

+CONVERT(BINARY(1),SUBSTRING(CONVERT(BINARY(8),@RES0),8,1))

+CONVERT(BINARY(1),SUBSTRING(CONVERT(BINARY(8),@RES0),5,1))

+CONVERT(BINARY(1),SUBSTRING(CONVERT(BINARY(8),@RES0),6,1))

)

)

END

RETURN NULL

END

And perhaps, if you are allowed to put in my book happens to a function?

Note that the course will be a link.

Thank you reply.