SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Getting resource name from blocked process report

By Darko Martinovic,

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.

I PART using T-SQL

--@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 @waitResource as nvarchar(128)
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')
EXEC sp_executesql @sql
,                  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

SELECT
 [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')
-- 'PAGE: 25:1:6077390
EXEC [dbo].[GetResourceNameFromPageClr] 25
        ,1
        ,6077390

Total article views: 713 | Views in the last 30 days: 35
 
Related Articles
FORUM

Blocked and Blocking process Help

Blocked and Blocking process Help

FORUM

How to get blocked objects?

How to get blocked objects?

FORUM

blocking

blocking

FORUM

Blocking Issue:Insert blocking select statements

Insert blocking select statements

FORUM

Blocked Processes

Find Percentage of blocked processes

Tags
blocked process report    
sqlclr    
t-sql    
 
Contribute