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