Deadlock with no SQL Query Fragment

ginacresseginacresse Posts: 10
edited October 17, 2011 4:43AM in SQL Monitor Previous Versions
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.

Comments

  • 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
    Priya Sinha
    Project Manager
    Red Gate Software
  • 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?
  • 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
    Priya Sinha
    Project Manager
    Red Gate Software
Sign In or Register to comment.