Monday, September 9, 2019

Decoding sys.dm_os_waiting_tasks.resource_description column info for lock waiters

I've been looking at some locking and blocking scenarios lately, and wanted to do some real-time investigation as well as some logging over time.

Have you seen Erland Sommarskog's beta_lockinfo?  It's pretty nice.  But it didn't quite fit my needs.

I went with this instead.  With each iteration, grab just the lock waiters from dm_os_waiting_tasks - and just those with waits longer than 1000 ms.  Convert resource_description to XML, and decode the attributes so I can potentially manipulate it.

The REPLACE occurrences below are to correct for formatting issues in the resource_description text which keep it from converting to conforming XML.  Hope I got them all.

;WITH lock_waiters AS
(SELECT waiting_task_address, session_id, exec_context_id, wait_duration_ms, wait_type, 
        resource_address, blocking_task_address, blocking_session_id, 
        blocking_exec_context_id, resource_description
 FROM sys.dm_os_waiting_tasks owt 
 WHERE owt.wait_type LIKE 'LCK%' AND wait_duration_ms > 1000)
SELECT track.session_id, track.blocking_session_id, track.wait_duration_ms,
       x1.[type], x1.mode, x1.associatedObjectId, x1.[dbid], x1.subresource, x1.lockPartition, 
       x1.fileid, x1.pageid, x1.hobtid, x1.[hash], x1.databasePrincipalId, x1.classid, x1.[objid],
FROM lock_waiters AS track
CROSS APPLY (SELECT info = CONVERT(XML, '<lockinfo type="' + REPLACE(REPLACE(REPLACE(REPLACE(resource_description,', ',','), ' = ','|'), ' ', '" '), '=', '="') + '"/>')) XML_table
     (SELECT [type] ='(/lockinfo/@type)[1]', N'varchar(50)'),
             mode ='(/lockinfo/@mode)[1]', N'varchar(20)'),
             associatedObjectId ='(/lockinfo/@associatedObjectId)[1]', N'varchar(20)'),
             [dbid] ='(/lockinfo/@dbid)[1]', N'varchar(20)'),
             subresource ='(/lockinfo/@subresource)[1]', N'varchar(20)'),
             lockpartition ='(/lockinfo/@lockPartition)[1]', N'varchar(20)'),
             fileid ='(/lockinfo/@fileid)[1]', N'varchar(20)'),
             pageid ='(/lockinfo/@pageid)[1]', N'varchar(20)'),
             hobtid ='(/lockinfo/@hobtid)[1]', N'varchar(20)'),
             [hash] ='(/lockinfo/@hash)[1]', N'varchar(20)'),
             databasePrincipalId ='(/lockinfo/@databasePrincipalId)[1]', N'varchar(20)'),
             classid ='(/lockinfo/@classid)[1]', N'varchar(20)'),
             id ='(/lockinfo/@id)[1]', N'varchar(20)'),
             [objid] ='(/lockinfo/@objid)[1]', N'varchar(20)')) x1;

Here's the BOL link for SQL Server 2017  sys.dm_os_waiting_tasks.

No comments:

Post a Comment