Oct 242011
 

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

  2 Responses to “Locking and Blocking (5) – Lock Resources in Extended Events”

  1. How to interpret resource_0 when resource_type = ‘METADATA’ ?
    Thanks.

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

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="">

(required)

(required)

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

This site uses Akismet to reduce spam. Learn how your comment data is processed.