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.
http://www.sommarskog.se/sqlutil/beta_lockinfo.html

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], x1.id
FROM lock_waiters AS track
CROSS APPLY (SELECT info = CONVERT(XML, '<lockinfo type="' + REPLACE(REPLACE(REPLACE(REPLACE(resource_description,', ',','), ' = ','|'), ' ', '" '), '=', '="') + '"/>')) XML_table
CROSS APPLY
     (SELECT [type] = XML_table.info.value(N'(/lockinfo/@type)[1]', N'varchar(50)'),
             mode = XML_table.info.value(N'(/lockinfo/@mode)[1]', N'varchar(20)'),
             associatedObjectId = XML_table.info.value(N'(/lockinfo/@associatedObjectId)[1]', N'varchar(20)'),
             [dbid] = XML_table.info.value(N'(/lockinfo/@dbid)[1]', N'varchar(20)'),
             subresource = XML_table.info.value(N'(/lockinfo/@subresource)[1]', N'varchar(20)'),
             lockpartition = XML_table.info.value(N'(/lockinfo/@lockPartition)[1]', N'varchar(20)'),
             fileid = XML_table.info.value(N'(/lockinfo/@fileid)[1]', N'varchar(20)'),
             pageid = XML_table.info.value(N'(/lockinfo/@pageid)[1]', N'varchar(20)'),
             hobtid = XML_table.info.value(N'(/lockinfo/@hobtid)[1]', N'varchar(20)'),
             [hash] = XML_table.info.value(N'(/lockinfo/@hash)[1]', N'varchar(20)'),
             databasePrincipalId = XML_table.info.value(N'(/lockinfo/@databasePrincipalId)[1]', N'varchar(20)'),
             classid = XML_table.info.value(N'(/lockinfo/@classid)[1]', N'varchar(20)'),
             id = XML_table.info.value(N'(/lockinfo/@id)[1]', N'varchar(20)'),
             [objid] = XML_table.info.value(N'(/lockinfo/@objid)[1]', N'varchar(20)')) x1;

Here's the BOL link for SQL Server 2017  sys.dm_os_waiting_tasks.
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-waiting-tasks-transact-sql?view=sql-server-2017


No comments:

Post a Comment