Deadlock with no SQL Query Fragment

ginacresse

I have a deadlock alert that provides the following:
Object blocked: Index/(Key) 72057595237695488

Deadlocked processes
SPID Victim Lock details Statement type
139 dbid: 10, object id: 72057595237695488, index id: (92f44a2c605d)
239 Victim process dbid: 10, object id: 72057595237957632, index id: (d757d3151bfd)

SQL query fragment:

There is no query fragment displayed, and when I query the hobt_id (mislabeled object id according to a previous post) I get the same table with all indexes for both 72057595237695488 and 72057595237957632.

Where can I get more helpful information about this deadlock? The application that executes the sql actually e-mails me an error message with the stored procedure name and the error line number for the victim process, so I'm not sure why SQL Monitor didn't provide at least as much information. I'm probably just not looking in the right place.

priyasinha

Hi,

Hope this post answers your question.

As far as SQL query fragment is concerned, SQL Monitor is able to capture full query only when you are running trace. Please note that trace has performance impact on your monitored server and should only be used for short period of time when diagnosing a problem.

Thanks,
Priya

ginacresse

Hi Priya,

When I run the sql from the post you directed me to:

SELECT 
   object_name(partitions.object_id)   AS [Table], 
   indexes.name            AS [Index] 

FROM 
   sys.partitions            AS partitions 

   INNER JOIN 
   sys.indexes            AS indexes 
   ON partitions.object_id         = indexes.object_id 

WHERE 
   hobt_id               = 72057595237695488

I get the following results:

Table Index InventoryTran cidx_InventoryTran InventoryTran PK_InventoryTran InventoryTran idx_InventoryTran InventoryTran idx_InventoryTran1 InventoryTran idx_InventoryTran2 InventoryTran idx_InventoryTran3 InventoryTran idx_TranDate InventoryTran idx_InventoryTran4 InventoryTran idx_InventoryTran5 InventoryTran idx_InventoryTran6 InventoryTran idx_InventoryTran7 InventoryTran idx_InventoryTran8 InventoryTran idx_InventoryTran9

When I run the same code with the victim hbid_id:

SELECT 
   object_name(partitions.object_id)   AS [Table], 
   indexes.name            AS [Index] 

FROM 
   sys.partitions            AS partitions 

   INNER JOIN 
   sys.indexes            AS indexes 
   ON partitions.object_id         = indexes.object_id 

WHERE 
   hobt_id               = 72057595237957632

I get identical results:

Table Index InventoryTran cidx_InventoryTran InventoryTran PK_InventoryTran InventoryTran idx_InventoryTran InventoryTran idx_InventoryTran1 InventoryTran idx_InventoryTran2 InventoryTran idx_InventoryTran3 InventoryTran idx_TranDate InventoryTran idx_InventoryTran4 InventoryTran idx_InventoryTran5 InventoryTran idx_InventoryTran6 InventoryTran idx_InventoryTran7 InventoryTran idx_InventoryTran8 InventoryTran idx_InventoryTran9

I'm not sure how this helps me. Am I missing something?

priyasinha

Hi,

Apologies but I have to look/ read myself first. I have never done deadlock decoding so won't be able to answer this quickly. SQL Monitor pulls this information directly from SQL Server and presents it on Alert Details. It doesn't manipulate the information in anyway.

I will post my findings here after I have looked at this.

Thanks,
Priya