is this query a known way of explaining the culprit(s) in a deadlock?

Question

hi, we have deadlocks occurring in our oltp presumably because of one or more indexes i asked them to add over a row version stamp in various tables. its actually one very volatile status table index that seems to come up most of the time. out load testing didnt catch the issue before it went to production. i have some other ideas for that index but want to first understand this...

...our oltp guy uses this query to point at the culprit. I trust but want to verify that this query is an appropriate tool for identifying more than our spotfire tool tells us about deadlocks.

what is it about hobt_id or the rest of this query that assists in answering deadlock mysteries? he ran the query twice for 2 different hobt ids. the first showed my index. the second , same table but a different index, not one i have a hand in. I'm not sure why one paste spanned multiple code blocks but i have been having trouble here under chrome lately. and in this case copied it from an email which might have had its own new line etc characters.

SELECT

sc.name as schema_name, 

so.name as object_name, 

si.name as index_name

FROM sys.partitions AS p

JOIN sys.objects as so on

p.object_id=so.object_id

JOIN sys.indexes as si on

p.index_id=si.index_id and 

p.object_id=si.object_id

JOIN sys.schemas AS sc on

so.schema_id=sc.schema_id

WHERE hobt_id = xxxxxxxxxxxxxxxxx;

Answer 1

That query alone cannot explain a deadlock, but it can be part of the analysis. If you have a pagelock in a deadlock, you may see something like this in the <resource-list>:

   <pagelock fileid="1" pageid="2547120" dbid="9" subresource="FULL" objectname="BigDB.dbo.TallOrders" id="lock27086af3400" mode="S" associatedObjectId="72057594049134592">

We see the table name, but we don't see the index name. But by feeding the associatedObjectId to the query above, we can find out in which index the lock is taken.