October 15, 2024 at 11:06 am
Hi all,
I'm on a sql server 2019 standard edition (15.0.4375.4) where I've a XE session configured to capture blocked process events.
All works fine (it's the very same configuration I have an many other clients) except that on this instance sometimes I get blocked-process-report where the blocker have a stack trace that ends with the same query as the blocked. A query that does not appear in the code of the blocker (I'm sure about that).
How can this happen ? Is there something I'm missing ? Is a there a bug on that specific version of sql server ?
Thanks in advance for any help!
October 15, 2024 at 11:28 am
can you share the xml?
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 15, 2024 at 12:23 pm
Sure, here it is with sensitive parte anonimized
Thanks!
<blocked-process-report id="116d394e-ac5c-4519-ba1c-1f637f5a9181" monitorLoop="544980" monitorDate="2024-10-15T10:49:38.31+02:00">
<blocked-process>
<process id="process16c893b1088" taskpriority="0" logused="0" waitresource="PAGE: 14:1:5803256 " waittime="5494" ownerId="6431344971" transactionname="user_transaction" lasttranstarted="2024-10-15T10:49:32.437" XDES="0x1610d03c040" lockMode="S" schedulerid="6" kpid="8784" status="suspended" spid="93" sbid="1" ecid="0" priority="0" trancount="1" lastbatchstarted="2024-10-15T10:49:32.440" lastbatchcompleted="2024-10-15T10:49:32.437" lastattention="1900-01-01T00:00:00.437" clientapp="Microsoft SQL Server" hostname="HOST_NAME" hostpid="3704" loginname="LOGIN_NAME" isolationlevel="read committed (2)" xactid="6431344971" currentdb="1" currentdbname="master" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame line="1" stmtend="5352" sqlhandle="0x02000000a285431d91f73721de4d0942f37294301ac2b3720000000000000000000000000000000000000000">
SELECT FROM <TABLE NAME> "Tbl1005"</frame>
<frame line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
SELECT FROM <TABLE NAME> "Tbl1005"</frame>
</executionStack>
<inputbuf>
SELECT FROM <TABLE NAME> "Tbl1005"</inputbuf>
</process>
</blocked-process>
<blocking-process>
<process status="suspended" waittime="7421" spid="298" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2024-10-15T10:49:30.353" lastbatchcompleted="2024-10-15T10:49:30.357" lastattention="1900-01-01T00:00:00.357" clientapp="CLIENT_NAME" hostname="HOST_NAME" hostpid="11464" loginname="LOGIN_NAME" isolationlevel="read committed (2)" xactid="6431334205" currentdb="14" currentdbname="DB_NAME" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
<executionStack>
<frame line="104" stmtstart="7856" stmtend="23044" sqlhandle="0x03000e00c4347f7deb8d8d0004b2000001000000000000000000000000000000000000000000000000000000">
SELECT FROM <TABLE NAME> "Tbl1005"</frame>
<frame line="1998" procname="[DATABASENAME].[dbo].[SP_C_NAME]" stmtstart="211676" stmtend="211758" sqlhandle="0x03000e00c9c6714c91edad0005b2000000000000000000000000000000000000000000000000000000000000">
EXEC SP_D_NAME</frame>
<frame line="502" procname="[DATABASENAME].[dbo].[SP_B_NAME]" stmtstart="45816" stmtend="46368" sqlhandle="0x03000e0092e2df0b3ce38e0009b2000001000000000000000000000000000000000000000000000000000000">
UPDATE TABLE_NAME SET
<...>
FROM TABLE_NAME
WHERE ID_TABLE_NAME = @id_param</frame>
<frame line="375" procname="[DATABASENAME].[dbo].[SP_A_NAME]" stmtstart="34154" stmtend="34612" sqlhandle="0x03000e00974a7771c841360103b2000001000000000000000000000000000000000000000000000000000000">
EXEC SP_B_NAME
</frame>
</executionStack>
<inputbuf>
Proc: [DATABASENAME].[dbo].[SP_A_NAME]</inputbuf>
</process>
</blocking-process>
</blocked-process-report>
October 15, 2024 at 1:22 pm
You've messed up the xml to much !
It doesn't produce a garph anymore !
Use dummy names ( T123 / t124 / ... ) instead of "<table name>" as < and > are special characters in xml
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 15, 2024 at 1:41 pm
Sorry, I've checked it with np++ now it's a valid XML.
The latest frame in blocking process stacktrace (SELECT COLUMN_LIST FROM TABLE_NAME "Tbl1005") it's the wrong one as that query is not in the code.
<blocked-process-report id="116d394e-ac5c-4519-ba1c-1f637f5a9181" monitorLoop="544980" monitorDate="2024-10-15T10:49:38.31+02:00">
<blocked-process>
<process id="process16c893b1088" taskpriority="0" logused="0" waitresource="PAGE: 14:1:5803256 " waittime="5494" ownerId="6431344971" transactionname="user_transaction" lasttranstarted="2024-10-15T10:49:32.437" XDES="0x1610d03c040" lockMode="S" schedulerid="6" kpid="8784" status="suspended" spid="93" sbid="1" ecid="0" priority="0" trancount="1" lastbatchstarted="2024-10-15T10:49:32.440" lastbatchcompleted="2024-10-15T10:49:32.437" lastattention="1900-01-01T00:00:00.437" clientapp="Microsoft SQL Server" hostname="HOST_NAME" hostpid="3704" loginname="LOGIN_NAME" isolationlevel="read committed (2)" xactid="6431344971" currentdb="1" currentdbname="master" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame line="1" stmtend="5352" sqlhandle="0x02000000a285431d91f73721de4d0942f37294301ac2b3720000000000000000000000000000000000000000">
SELECT COLUMN_LIST FROM TABLE_NAME "Tbl1005"</frame>
<frame line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
SELECT COLUMN_LIST FROM TABLE_NAME "Tbl1005"</frame>
</executionStack>
<inputbuf>
SELECT COLUMN_LIST FROM TABLE_NAME "Tbl1005"</inputbuf>
</process>
</blocked-process>
<blocking-process>
<process status="suspended" waittime="7421" spid="298" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2024-10-15T10:49:30.353" lastbatchcompleted="2024-10-15T10:49:30.357" lastattention="1900-01-01T00:00:00.357" clientapp="CLIENT_NAME" hostname="HOST_NAME" hostpid="11464" loginname="LOGIN_NAME" isolationlevel="read committed (2)" xactid="6431334205" currentdb="14" currentdbname="DB_NAME" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
<executionStack>
<frame line="104" stmtstart="7856" stmtend="23044" sqlhandle="0x03000e00c4347f7deb8d8d0004b2000001000000000000000000000000000000000000000000000000000000">
SELECT COLUMN_LIST FROM TABLE_NAME "Tbl1005"</frame>
<frame line="1998" procname="[DATABASENAME].[dbo].[SP_C_NAME]" stmtstart="211676" stmtend="211758" sqlhandle="0x03000e00c9c6714c91edad0005b2000000000000000000000000000000000000000000000000000000000000">
EXEC SP_D_NAME</frame>
<frame line="502" procname="[DATABASENAME].[dbo].[SP_B_NAME]" stmtstart="45816" stmtend="46368" sqlhandle="0x03000e0092e2df0b3ce38e0009b2000001000000000000000000000000000000000000000000000000000000">
UPDATE TABLE_NAME SET
COLUMN_LIST
FROM TABLE_NAME
WHERE ID_TABLE_NAME = @id_param</frame>
<frame line="375" procname="[DATABASENAME].[dbo].[SP_A_NAME]" stmtstart="34154" stmtend="34612" sqlhandle="0x03000e00974a7771c841360103b2000001000000000000000000000000000000000000000000000000000000">
EXEC SP_B_NAME
</frame>
</executionStack>
<inputbuf>
Proc: [DATABASENAME].[dbo].[SP_A_NAME]</inputbuf>
</process>
</blocking-process>
</blocked-process-report>
October 16, 2024 at 1:32 pm
waitresource="PAGE: 14:1:5803256 "
Blocking spid="298"
Blocked spid="93"
use SP_WHO2 to figure out these connections
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 17, 2024 at 7:32 am
I've already verified the two spid, one of those was call a stored procedure that have nothing to do with that SELECT that you can see in
<frame line="104" stmtstart="7856" stmtend="23044" sqlhandle="0x03000e00c4347f7deb8d8d0004b2000001000000000000000000000000000000000000000000000000000000">
SELECT COLUMN_LIST FROM TABLE_NAME "Tbl1005"</frame>
It seems like that the frame was reported in both stacks by mistake.
That's why I'm asking if anyone have an idea of why this could happen.
Thanks
October 17, 2024 at 9:33 am
FWIW
This is the sql script I always use to detangle my wait resource:
(as the script serves me for ages by now, the originatin utl's no longer work )
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 coded for ! [' + @waitresource + ']';
end
END
end
end
end
If you are having mixed extends, pages may contain data of multiple objects, hence lock escallation to page may block multiple objects
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply