Home Dashboard Directory Help
Search

objectname,indexname not populated in deadlock graph in SQL2008 SP1 and SQL2008 R2 by Rama-M


Status: 

Closed
 as Fixed Help for as Fixed


2
0
Sign in
to vote
Type: Bug
ID: 635391
Opened: 1/10/2011 11:11:21 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

Extended Event is very useful especially the xml_deadlock_report. However it does not capture the objectname, indexname which is essential to trouble shoot what table and index was involved. Even though the attributes are there in the xml graph it never populated. See example below. Please correct this. This problem is also there in SQL2008 SP1 and SQL2008 R2.

<deadlock><victim-list><victimProcess id="process43f708"/></victim-list><process-list><process id="process43f708" taskpriority="0" logused="0" waitresource="KEY: 7:72057653494677504 (ec8ac87374d0)" waittime="1037" ownerId="14697792244" transactionname="implicit_transaction" lasttranstarted="2010-07-20T12:44:44.090" XDES="0x23edcf590" lockMode="RangeS-U" schedulerid="1" kpid="4196" status="suspended" spid="53" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2010-07-20T12:44:44.090" lastbatchcompleted="2010-07-20T12:44:44.090" lastattention="2010-07-20T12:44:42.067" clientapp="Microsoft Dynamics NAV Classic client" hostname="CWREN" hostpid="2264" loginname="CHB\cwren" isolationlevel="serializable (4)" xactid="14697792244" currentdb="7" lockTimeout="40000" clientoption1="671156320" clientoption2="128058"><executionStack><frame procname="" line="1" stmtstart="66" sqlhandle="0x020000003eb2e91994a3ed56fe5c0a551ce1c9f4db586616"/><frame procname="" line="1" sqlhandle="0x0400ff7fb925310a01000000000000000000000000000000"/></executionStack><inputbuf> (@P1 int,@P2 varchar(20),@P3 int)SELECT TOP 1 * FROM "Navision_Live"."dbo"."CHB HO$Purchase Line" WITH (UPDLOCK) WHERE (("Document Type"=@P1)) AND (("Document No_"=@P2)) AND (("Type"<>@P3)) ORDER BY "Document Type","Document No_","Line No_"    </inputbuf></process><process id="process4ddb88" taskpriority="0" logused="97736" waitresource="KEY: 7:72057653494808576 (54a37961e09a)" waittime="660" ownerId="14697785872" transactionname="implicit_transaction" lasttranstarted="2010-07-20T12:44:43.710" XDES="0x1a588cc60" lockMode="RangeS-U" schedulerid="4" kpid="3460" status="suspended" spid="85" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2010-07-20T12:44:44.470" lastbatchcompleted="2010-07-20T12:44:44.470" lastattention="2010-07-20T12:44:40.550" clientapp="Microsoft Dynamics NAV Classic client" hostname="NCHAPMAN" hostpid="3720" loginname="CHB\nchapman" isolationlevel="serializable (4)" xactid="14697785872" currentdb="7" lockTimeout="40000" clientoption1="671156320" clientoption2="128058"><executionStack><frame procname="" line="1" stmtstart="50" sqlhandle="0x0200000058a72b2813e4c355b5d646c1b8eb98e847a0306d"/><frame procname="" line="1" sqlhandle="0x0400ff7fb925310a01000000000000000000000000000000"/></executionStack><inputbuf> (@P1 int,@P2 varchar(20))DELETE FROM "Navision_Live"."dbo"."CHB HO$Purchase Line" WHERE (("Document Type"=@P1)) AND (("Document No_"=@P2)) </inputbuf></process></process-list><resource-list><keylock hobtid="72057653494677504" dbid="7" objectname="" indexname="" id="lock285b38780" mode="RangeS-U" associatedObjectId="72057653494677504"><owner-list><owner id="process4ddb88" mode="RangeS-U"/></owner-list><waiter-list><waiter id="process43f708" mode="RangeS-U" requestType="wait"/></waiter-list></keylock><keylock hobtid="72057653494808576" dbid="7" objectname="" indexname="" id="lock2b718fd00" mode="RangeS-U" associatedObjectId="72057653494808576"><owner-list><owner id="process43f708" mode="RangeS-U"/></owner-list><waiter-list><waiter id="process4ddb88" mode="RangeS-U" requestType="wait"/></waiter-list></keylock></resource-list></deadlock>
Details
Sign in to post a comment.
Posted by k00lb0y on 5/21/2013 at 4:07 AM
MS Support: Please let us know if the issue has been identified and fixed in any of the SP or CU released for SQL Server 2008 R2 Version. We are seeing lot of deadlocks occurring in our Production environment, however with lack of data provided in the DEADLOCK EVENTS we are unable to identify the root cause and therefore fix the issue. This is causing lot impact to the customer.

Appreciate your response on this. Thanks for the help, in advance.
Posted by Rama-M on 2/18/2011 at 3:43 PM
Hi Microsoft,
Because it is a bug and resolving this will help a lot of trouble shooting, which the system health check extended event is designed for, I believe it is essencial to fix it sooner. Idealy in the next cumulative update, but as a bare minimum in the next service pack. I do not think the industry can wait for the next major release. So please consider this and bring the fix forward in SQL2008 and SQL2008 R2 versions.

Regards
Rama Mathanmohan
Posted by Microsoft on 2/18/2011 at 2:25 PM
Hi,

I'm glad to let you know that we are going to fix this issue in the next major release.

Thank you again for your report. We are always looking for a feedback which allows us to improve our products.

With best regards,
Evgeny Krivosheev
SQL Server Program Manager
Posted by Microsoft on 1/25/2011 at 1:18 PM
Thank you for your comment. We always looking for feedback which will allow us to improve our products.

We are discussing your request and I will let you know about the outcome.

Thank you again for providing this suggestion.

With best regards,
Evgeny Krivosheev
Sign in to post a workaround.