October 17, 2022 at 9:30 pm
Hi all,
I am in a process of creation of Extended Events for blockings and deadlocks and reading information from respective .xel files in XML format.
One of returned tag values is waitresource. In my cases it comes in variety of formats:
KEY: 12:72057594052870144 (ce35eeb1d081)
RID: 8:1:16771856:0
OBJECT: 8L1918629878
I need to find out the table name from them. Does anybody has by any chance a code for it?
Thanks
October 18, 2022 at 12:42 pm
Here is what I use:
-- Deadlock information analysis
Declare @Debug bit
Set @Debug = 1
Set @Debug = 0
Declare @waitresource varchar(128)
set @waitresource = 'KEY: 7:72057600361103360 (97e5c9409bc8)'
set @waitresource = 'RID: 7:1:33656785:0'
set @waitresource = 'TAB: 7:28643991'
set @waitresource = 'PAG: 7:1:168'
set @waitresource = 'KEY: 11:72057596479733760 (f5c0e3b21ae4)'
-- set @waitresource = 'PAGE: 11:1:177471'
Set @waitresource = 'OBJECT: 7:1959014060:46'
Set @waitresource = 'PAGE: 7:1:48965674'
Set @waitresource = 'KEY: 8:327680 (af54faec4a10)'
--------------------------------------------------------------------------------------------------------
DECLARE @databaseName varchar( 128 )
if @waitresource like 'KEY%'
begin
/* Translate deadlock key: to row
process id="process981f048" taskpriority="0" logused="45028" waitresource="KEY: 7:72057600226492416 (63e6956da625)" ....
*/
DECLARE @keyValue varchar( 128 )
SET @keyValue = @waitresource ; --Output from deadlock graph
DECLARE @DbNo int
SET @DbNo = substring( @keyValue, 5, charindex(':', @keyValue, 5) - 5) ;
set @databaseName = quotename(db_name(@DbNo)) ; --DatabaseName
DECLARE @lockres varchar( 128 );
DECLARE @hobbitID bigint;
SELECT @hobbitID = CONVERT( bigint , RTRIM( SUBSTRING( @keyValue , CHARINDEX( ':' , @keyValue , CHARINDEX( ':' , @keyValue ) + 1 ) + 1 ,
CHARINDEX( '(' , @keyValue ) - CHARINDEX( ':' , @keyValue , CHARINDEX( ':' , @keyValue ) + 1 ) - 1 )));
SELECT @lockRes = RTRIM( SUBSTRING( @keyValue , CHARINDEX( '(' , @keyValue )
+ 1 , CHARINDEX( ')' , @keyValue )
- CHARINDEX( '(' , @keyValue )
- 1 ));
DECLARE @objectName sysname;
DECLARE @ObjectLookupSQL AS nvarchar( max )
Set @ObjectLookupSQL = '
SELECT @objectName = quotename(s.name) + ''.'' + quotename(o.name)
FROM ' + @databaseName + '.sys.partitions p
JOIN ' + @databaseName + '.sys.indexes i ON p.index_id = i.index_id AND p.[object_id] = i.[object_id]
JOIN ' + @databaseName + '.sys.objects o on o.object_id = i.object_id
JOIN ' + @databaseName + '.sys.schemas s ON s.schema_id = o.schema_id
WHERE hobt_id = ' + CONVERT( nvarchar( 50 ) , @hobbitID ) + '
';
EXEC sp_executesql @ObjectLookupSQL , N'@objectName sysname OUTPUT' , @objectName = @objectName OUTPUT;
SELECT @objectName;
DECLARE @finalResult nvarchar( max )
Set @finalResult = N'select %%lockres%% as [Deadlock LockedResource],*
from ' + @databaseName + '.' + @objectName + '
where %%lockres%% = ''(' + @lockRes + ')''
';
if @Debug = 1
begin
select convert(xml, @ObjectLookupSQL) ObjectLookupSQL, convert(xml, @finalResult ) finalResult_LockRes
end
else
begin
EXEC sp_executesql @finalResult;
end
end
else
-- http://technet.microsoft.com/en-us/library/aa937573%28v=sql.80%29.aspx
begin
if @waitresource like 'RID%'
begin
-- RID: db_id:file_id:page_no:row_no;
DECLARE @RIDValue varchar( 128 )
SET @RIDValue = @waitresource ; --Output from deadlock graph
DECLARE @DbID int
Set @DbID = convert(int, substring( @RIDValue, 5, charindex(':', @RIDValue, 5) - 5) );
set @databaseName = quotename(db_name(@DbID)) ; --DatabaseName
DECLARE @FileID int;
DECLARE @Page bigint;
DECLARE @RID int;
declare @wrk varchar(128)
SET @wrk = replace(SUBSTRING( @RIDValue , CHARINDEX( ':' , @RIDValue ) + 2, datalength(@RIDValue)),':','.')
select @RID = PARSENAME(@wrk,1)
, @Page = PARSENAME(@wrk,2)
, @FileID =PARSENAME(@wrk,3) ;
Select @databaseName as DatabaseName, @FileID as FileId, @Page as PageId, @RID as RowId --, @wrk
dbcc traceon (3604);
DBCC page ( @DbID, @FileID, @Page, printopt= 1 ) -- [, printopt={0|1|2|3} ][, cache={0|1} ])
-- -- You can use this command to view the data page structure.
-- -- http://support.microsoft.com/kb/83065
dbcc traceoff (3604) ;
end
else
begin
if @waitresource like 'TAB%'
begin
-- TAB: db_id:object_id
DECLARE @TABValue varchar( 128 )
SET @TABValue = @waitresource ; --Output from deadlock graph
set @DbID = convert(int, substring( @TABValue, 5, charindex(':', @TABValue, 5) - 5) );
set @databaseName = quotename(db_name(@DbID)) ; --DatabaseName
DECLARE @TableId int
SELECT @TableId = CONVERT( bigint , RTRIM( SUBSTRING( @TABValue , CHARINDEX( ':' , @TABValue , CHARINDEX( ':' , @TABValue ) + 1 ) + 1 ,
DATALENGTH( @TABValue ) )
)
);
Declare @DynSQL nvarchar(max)
SET @DynSQL = 'use ' + @databaseName + ';
Select db_name(' + cast( @DbID as varchar(25)) + ') as DatabaseName, OBJECT_SCHEMA_NAME(' + cast( @TableId as varchar(25)) + ') as SchemaName, OBJECT_NAME(' + cast( @TableId as varchar(25)) + ') as TableName ;
'
if @Debug = 1
begin
select convert(xml, @DynSQL) DynSQL
end
else
begin
EXEC sp_executesql @DynSQL;
end
end
else
begin
if @waitresource like 'Pag%'
begin
-- PAG Identifies the page resource on which a lock is held or requested.
-- PAG is represented in Trace Flag 1204 as PAG: db_id:file_id:page_no; for example, PAG: 7:1:168.
DECLARE @PAGValue varchar( 128 )
SET @PAGValue = @waitresource ; --Output from deadlock graph
set @wrk = replace(SUBSTRING( @PAGValue , CHARINDEX( ':' , @PAGValue ) + 2, datalength(@PAGValue)),':','.')
select @Page = PARSENAME(@wrk,1)
, @FileID = PARSENAME(@wrk,2)
, @DbID =PARSENAME(@wrk,3) ;
set @databaseName = quotename(db_name(@DbID)) ; --DatabaseName
Select @DbID as DbID, @databaseName as DatabaseName, @FileID as FileId, @Page as PageId, 'LOOK IN THE Query Messages for DBCC OUTPUT' Remark --, @wrk
dbcc traceon (3604);
DBCC page ( @DbID, @FileID, @Page, printopt= 1 ) -- [, printopt={0|1|2|3} ][, cache={0|1} ])
-- -- You can use this command to view the data page structure.
-- -- http://support.microsoft.com/kb/83065
dbcc traceoff (3604) ;
end
ELSE
begin
if @waitresource like 'OBJECT:%'
begin
DECLARE @ObjectID int ;
set @wrk = replace(SUBSTRING( @waitresource , CHARINDEX( ':' , @waitresource ) + 2, datalength(@waitresource)),':','.')
select @Page = PARSENAME(@wrk,1)
, @ObjectID = PARSENAME(@wrk,2)
, @DbID =PARSENAME(@wrk,3) ;
set @databaseName = quotename(db_name(@DbID)) ; --DatabaseName
Select @databaseName as DatabaseName, @ObjectID as ObjectID, OBJECT_SCHEMA_NAME(@ObjectID, @dbid) as ObjSchema, OBJECT_NAME(@ObjectID, @dbid) as ObjName --, @wrk
END
Else
begin
Print 'waitresource not expected! [' + @waitresource + ']';
end
END
end
end
end
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 18, 2022 at 3:07 pm
Thanks a lot Johan. This is exactly what I was looking for.
December 23, 2022 at 2:04 am
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply