Friday, 14 October 2011

Decipher blocked process waitresource

When attempting to identify blocking issues, you may find yourself looking in sysprocesses at the waitresource column, and trying to decipher the meaning of something along the lines of:

KEY: 32:72057594097827840 (f5593d0e605b)

Thanks - you couldn't just tell me the actual object could you?!

But help is at hand - apparently this key consists of the database id, followed by a a hobt_id - no, not a small person with large feet from New Zealand, but a "Heap or B-Tree" id.

This id value can be found in the sys.partitions table, so a quick join from that to sys.indexes, and back to sys.obejcts finally reveals the index we are blocked on.

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 = 72057594097827840