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 content from blocked process report

By Darko Martinovic,

Added on 7.4.2017.

- .Net source code is available on

https://onedrive.live.com/?authkey=%21AAL%5Fg65wns6ZEdo&id=2FB47B8B4D56FEB9%215282&cid=2FB47B8B4D56FEB9

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.
Although many authors suggest that is not important what is content of blocking, in my opinion that is very important information.

This script serves to display the content of the waiting resource.

There are two parts. In first one, I will show how to accomplish this task using t-sql. In second part the same thing is accomplished using SQLCLR

--- 1.Part - using T-SQL

Usage

1. Example

-- @waitResource is written in blocked process report as a attribute of  blocked-process/proces element

DECLARE @waitResource nvarchar(128) = 'KEY: 10:72057594098286592 (b14200e25741)'

-- @resourceName is decoded using function GetResourceName ( see previous post )

DECLARE @resourceName = 'HumanResources.Employee' -- replace with the name of your resource

SELECT
        dbo.GetResourceContent(@waitResource, @resourceName, DEFAULT);

-- Will return t-sql to evaluate. After evaluation you get one or more records in form of xml document.

2. Example

-- @waitResource is written in blocked process report as a attribute of blocked-process/proces element

DECLARE @waitResource as nvarchar(256) = 'KEY: 41:72057594544062464 (b14200e25741)'

-- @sql t-sql code to evaluate
DECLARE @sql as nvarchar(max)

-- @tableName is decoded using function GetResourceName ( see previous post )
DECLARE @tableName as nvarchar(256) = 'HumanResource.Employee' --replace with the name of your resource

--output value in form of xml document
DECLARE @resCon as nvarchar(max)


SET @SQL = DBO.[GetResourceContent](@waitResource,@tableName,@tester);

EXEC sp_executesql @sql
    ,N'@tester nvarchar(max) output'
    ,@resCon OUTPUT;

SELECT
 @resCon

-- Will return one or more records in form of xml document.

--- 2.Part - using SQLCLR

DECLARE @waitResource as nvarchar(256) = 'PAGE: 25:1:6077390'; --Replace with your key code
DECLARE @tableName as nvarchar(256) = 'Employee.Employee' --Replace with your table name
SELECT
 [dbo].[GetResourceContentClr](@waitResource, @tableName);

SET @waitResource = 'KEY: 25:72057600909443072 (b14200e25741)'
SELECT
 [dbo].[GetResourceContentClr](@waitResource, @tableName);

Total article views: 352 | Views in the last 30 days: 28
 
Related Articles
FORUM

Blocked and Blocking process Help

Blocked and Blocking process Help

FORUM

Blocked Processes

Find Percentage of blocked processes

ARTICLE

Using the Blocked Process Report in SQL Server 2005/2008

When excessive blocking occurs in SQL, performance degrades. Learn how to use the Blocked Process R...

FORUM

Blocked Process Report - waitresource question

I'm seeing a waitresource of "OBJECT: 39:2000726180:15" in a blocked process report. I know that 39...

FORUM

waitresource field 4 blocked & blocking processes

Hi! I collected some stats for the applications running on a server. These stats show that about...

Tags
blocked process report    
sqlclr    
t-sql    
 
Contribute