Deadlock with no SQL Query Fragment

SQL Server performance monitoring and alerting

Moderators: eddie davis, priyasinha, Adam, chriskelly, Chris Lambrou, Chris Spencer

Deadlock with no SQL Query Fragment

Postby ginacresse » Thu Oct 13, 2011 8:24 pm

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.
ginacresse
 
Posts: 6
Joined: Thu Oct 13, 2011 7:10 pm

Postby priyasinha » Fri Oct 14, 2011 9:20 am

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
priyasinha
 
Posts: 532
Joined: Wed Jan 03, 2007 5:02 pm

Postby ginacresse » Sat Oct 15, 2011 1:13 am

Hi Priya,

When I run the sql from the post you directed me to:
Code: Select all
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:
Code: Select all
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?
ginacresse
 
Posts: 6
Joined: Thu Oct 13, 2011 7:10 pm

Postby priyasinha » Mon Oct 17, 2011 9:43 am

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
priyasinha
 
Posts: 532
Joined: Wed Jan 03, 2007 5:02 pm


Return to SQL Monitor 2

Who is online

Users browsing this forum: No registered users and 0 guests