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

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

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


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s