objectname,indexname not populated in deadlock graph in SQL2008 SP1 and SQL2008 R2

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

Closed

as Fixed

Type:	Bug
ID:	635391
Opened:	1/10/2011 11:11:21 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

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 (expand)

English

SQL Server 2008 SP1

SQL Engine

Windows Server 2008 R2

English

Create a deadlock and examile the XML graph produced by excuting the following code and you will find objectname,indexname will be blank under <deadlock><resource-list>

Select    XEventData.XEvent.value('@timestamp', 'datetime') as [Timestamp]
        ,XEventData.XEvent.value('@timestamp', 'datetime') + cast(GETDATE() - GETUTCDATE() as time) as [DeadlockTime]
        ,Cast(XEventData.XEvent.value('(data/value)[1]','varchar(max)') as xml) as DeadlockGraph

    FROM
        (select CAST(target_data as xml) as TargetData
        from sys.dm_xe_session_targets st
        join sys.dm_xe_sessions s on s.address = st.event_session_address
        where name = 'system_health') AS Data
        CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
        where XEventData.XEvent.value('@name', 'varchar(max)') = 'xml_deadlock_report'
        Order By [Timestamp]

objectname and indexname is blank

objectname and indexname should be populated with relevant object name (such as table name) and index name respectively

X64

0 attachments

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