Added on 10.4.2017.
- .Net source code is available on
https://1drv.ms/f/s!Arn-Vk2Le7QvqX7h8uzCrfWBlFGz
------------------------------------------------------------------------
When you analyzing blocking problems, first choice is that you look what sys.dm_os_waiting_tasks will display.
It means blocking occurs right now, and you know blocker and blocking spid's.
In that case it is easy to determine resource which is subject of blocking.
SELECT
DTL.[resource_type] AS [resource type]
,CASE
WHEN DTL.[resource_type] IN ('DATABASE', 'FILE', 'METADATA') THEN DTL.[resource_type]
WHEN DTL.[resource_type] = 'OBJECT' THEN OBJECT_NAME(DTL.resource_associated_entity_id)
WHEN DTL.[resource_type] IN ('KEY', 'PAGE', 'RID') THEN (SELECT
(CASE
WHEN s.name IS NOT NULL THEN s.name + '.'
ELSE ''
END) + OBJECT_NAME(p.[object_id])
FROM sys.partitions p
INNER JOIN sys.objects o
ON o.object_id = p.object_id
INNER JOIN sys.schemas s
ON o.schema_id = s.schema_id
WHERE p.[hobt_id] = DTL.[resource_associated_entity_id])
ELSE 'Unidentified'
END AS [Parent Object]
,DTL.[request_mode] AS [Lock Type]
,DTL.[request_status] AS [Request Status]
FROM sys.dm_os_waiting_tasks wt
INNER JOIN sys.dm_tran_locks DTL
ON DTL.lock_owner_address = WT.resource_address
WHERE wt.blocking_session_id IS NOT NULL;
But, if you analyzing blocking problems off-line, using trace or extended events, you have to count only on information found in blocked process report.
The resource name is most important information in the blocked process report. Unfortunately this information is only available as encoded.
The purpose of this script is to decode this information.
--@waitResource is written in blocked process report as a attribute of blocked-process/process element
DECLARE @waitResource as nvarchar(128) = 'KEY: 41:72057594544062464 (b14200e25741)' -- replace this string with your wait resource string
SELECT
DBO.[GetResourceName]( @waitResource, default);
-- Will return t-sql to evaluate
SELECT
sc.name + '.' + so.name
FROM MYDB_NAME.sys.partitions AS p
JOIN MYDB_NAME.sys.objects AS so
ON p.object_id = so.object_id
JOIN MYDB_NAME.sys.indexes AS si
ON p.index_id = si.index_id
AND p.object_id = si.object_id
JOIN MYDB_NAME.sys.schemas AS sc
ON so.schema_id = sc.schema_id
WHERE p.hobt_id = 72057594098286592
--2. Example. Evaluation
DECLARE @sql as nvarchar(max)
DECLARE @resCon as nvarchar(256)
SET @waitResource = 'KEY: 10:72057594098286592 (b14200e25741)'
--second parametar name should be the same as
--the name of first parametar in sp_executesql
SET @sql = DBO.GetResourceName(@waitResource, '@resourceName')
, N'@resourceName nvarchar(max) output'
, @resCon OUTPUT;
SELECT @resCon
-- Will return resource name
-- II . PART using SQLCLR
-- Although it seems that CLR is better solution, there is a limitation when processing PAGE information.
-- Clr function does not allwed using dbcc or create temp table. Workaround is to use CLR stored procedure to
-- determine resource name
[dbo].[GetResourceNameClr]('PAGE: 25:1:6077390')
--KEY: 25:72057600909443072 (8b56a42c5bc8)
SELECT
[dbo].[GetResourceNameClr]('KEY: 25:72057600909443072 (8b56a42c5bc8)')
--OBJECT: 25:1239779574:0
SELECT
[dbo].[GetResourceNameClr]('OBJECT: 25:1239779574:0')
,1
,6077390
