Deadlock with no SQL Query Fragment
ginacresse
Posts: 10
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.
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
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
Project Manager
Red Gate Software
When I run the sql from the post you directed me to: 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:
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?
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
Project Manager
Red Gate Software