How to identify the waitresource during blocking and deadlocks

Identify blocking processes

SELECT spid,blocked,waittime,waitresource from master..sysprocesses where blocked>0

Deciphering waitresource column
http://support.microsoft.com/kb/224453

Waitresource
This field indicates the resource that a SPID is waiting on. Attached table lists common waitresource formats and their meaning:

Identify the corresponding index for a given hobt_id

SELECT o.name, i.name
FROM sys.partitions p
JOIN sys.objects o ON p.object_id = o.object_id
JOIN sys.indexes i ON p.object_id = i.object_id
AND p.index_id = i.index_id
WHERE p.hobt_id = 72057594065256448

https://littlekendra.com/2016/10/17/decoding-key-and-page-waitresource-for-deadlocks-and-blocking/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s