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

Check the progress of backup/restore operations


SELECT
percent_complete,
start_time ,
command,
b.name,
DATEADD(ms,estimated_completion_time,GETDATE()) AS ‘EstimatedEndTime’,
(estimated_completion_time/1000/60) AS ‘EstimatedMinutesToEnd’
FROM sys.dm_exec_requests a
INNER JOIN sys.databases b ON a.database_id = b.database_id
WHERE command like ‘%RESTORE%’
AND estimated_completion_time > 0