locked
Can you guys help me presenting this in a table like format? RRS feed

  • Question

  • I would like to display the information captured on the query below in a table format, that will be used for a report, in SSRS:

    SELECT CAST (
        REPLACE (
            REPLACE (
                XEventData.XEvent.value (
       '(data/value)[1]', 'varchar(max)'),
                --'<victim-list>', '<deadlock><victim-list>'),
       '', ''),
            --'<process-list>', '</victim-list><process-list>')
      '', '')
        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(4000)') = 'xml_deadlock_report'


    --- Best Regards, Igor Santos Twitter: @sqlsantos Blog: sqlsantos.wordpress.com

    Thursday, September 6, 2012 2:59 PM

Answers

  • All deadlock graphs are different so this can be a bit tricky.  You can't always guarantee the graph will contain certain elements and there may be a number of participants involved.  Having said that, there are some common elements and attributes.

    Have a look at this example where I extract clean up the XML and extract the transaction startdate and inputbuffer:

    USE tempdb
    GO
    
    SET NOCOUNT ON
    GO
    
    IF OBJECT_ID('tempdb..#tmp' ) IS NOT NULL DROP TABLE #tmp
    GO
    CREATE TABLE #tmp ( rowId INT IDENTITY PRIMARY KEY, badXML VARCHAR(MAX), goodXML XML )
    GO
    
    INSERT INTO #tmp ( badXML )
    VALUES
    ( '<deadlock-list>  <victim-list>   <victimProcess id="process5258bc8"/>   <process-list>    <process id="process5258bc8" taskpriority="0" logused="0" waitresource="PAGE: 8:1:10027530" waittime="9695" ownerId="1055431021" transactionname="SELECT" lasttranstarted="2011-03-16T09:46:37.910" XDES="0x28fb7f760" lockMode="S" schedulerid="13" kpid="14184" status="suspended" spid="71" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2011-03-16T09:46:37.910" lastbatchcompleted="2011-03-16T09:46:37.907" clientapp=".Net SqlClient Data Provider" hostname="someHost301" hostpid="4300" loginname="SOMEDOMAIN\someHost304$" isolationlevel="read committed (2)" xactid="1055431021" currentdb="8" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">     <executionStack>      <frame procname="" line="7" stmtstart="486" sqlhandle="0x0200000061dca31412f16447ff510dcefb5b290062a425a1">      </frame>      <frame procname="" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">      </frame>     </executionStack>     <inputbuf> SELECT * FROM someTable </inputbuf>    </process>    <process id="process2b34922c8" taskpriority="0" logused="4168" waitresource="PAGE: 8:1:33495280" waittime="5006" ownerId="1055429440" transactionname="user_transaction" lasttranstarted="2011-03-16T09:46:37.110" XDES="0x5999603b0" lockMode="IX" schedulerid="16" kpid="7952" status="suspended" spid="171" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-03-16T09:46:37.127" lastbatchcompleted="2011-03-16T09:46:37.120" lastattention="2011-03-16T09:41:34.687" clientapp=".Net SqlClient Data Provider" hostname="someHost302" hostpid="3312" loginname="SOMEDOMAIN\someAccount" isolationlevel="read committed (2)" xactid="1055429440" currentdb="8" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">     <executionStack>      <frame procname="" line="1" stmtstart="5650" sqlhandle="0x020000000f7a153607f13c82e354ef716d3c1a913ed7a544">      </frame>      <frame procname="" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">      </frame>     </executionStack>     <inputbuf> SELECT * FROM someTable </inputbuf>    </process>   </process-list>   <resource-list>    <pagelock fileid="1" pageid="10027530" dbid="8" objectname="" id="lock5367aeb80" mode="IX" associatedObjectId="72057611960975360">     <owner-list>      <owner id="process2b34922c8" mode="IX"/>     </owner-list>     <waiter-list>      <waiter id="process5258bc8" mode="S" requestType="wait"/>     </waiter-list>    </pagelock>    <pagelock fileid="1" pageid="33495280" dbid="8" objectname="" id="lock58bc92580" mode="S" associatedObjectId="72057611961171968">     <owner-list>      <owner id="process5258bc8" mode="S"/>     </owner-list>     <waiter-list>      <waiter id="process2b34922c8" mode="IX" requestType="wait"/>     </waiter-list>    </pagelock>   </resource-list>  </deadlock> </deadlock-list> ' ),
    ( '<deadlock-list>  <victim-list>   <victimProcess id="process4ff1b88"/>   <process-list>    <process id="process4ff1b88" taskpriority="0" logused="0" waitresource="PAGE: 8:1:10039576" waittime="724" ownerId="1055766658" transactionname="SELECT" lasttranstarted="2011-03-16T09:47:53.240" XDES="0x375835ca0" lockMode="S" schedulerid="9" kpid="4796" status="suspended" spid="390" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2011-03-16T09:47:53.240" lastbatchcompleted="2011-03-16T09:47:53.240" lastattention="2011-03-16T09:41:42.427" clientapp=".Net SqlClient Data Provider" hostname="someHost303" hostpid="1196" loginname="SOMEDOMAIN\someAccount" isolationlevel="read committed (2)" xactid="1055766658" currentdb="8" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">     <executionStack>      <frame procname="" line="1" stmtstart="196" sqlhandle="0x020000009e0bac02ad90c01c3253ff7edf94b57f9fe25319">      </frame>      <frame procname="" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">      </frame>     </executionStack>     <inputbuf> SELECT * FROM someTable </inputbuf>    </process>    <process id="process5bbc4bb88" taskpriority="0" logused="4024" waitresource="PAGE: 8:1:10023394" waittime="722" ownerId="1055765593" transactionname="user_transaction" lasttranstarted="2011-03-16T09:47:52.920" XDES="0x6d6c15950" lockMode="IX" schedulerid="2" kpid="12044" status="suspended" spid="88" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-03-16T09:47:52.930" lastbatchcompleted="2011-03-16T09:47:52.927" clientapp=".Net SqlClient Data Provider" hostname="someHost301" hostpid="1076" loginname="SOMEDOMAIN\someAccount" isolationlevel="read committed (2)" xactid="1055765593" currentdb="8" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">     <executionStack>      <frame procname="" line="1" stmtstart="5830" sqlhandle="0x02000000e33d7729ddcae28f501fa401c5c4a2c85a5893ac">      </frame>      <frame procname="" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">      </frame>     </executionStack>     <inputbuf> SELECT * FROM someTable </inputbuf>    </process>   </process-list>   <resource-list>    <pagelock fileid="1" pageid="10039576" dbid="8" objectname="" id="lock6cf7afb00" mode="IX" associatedObjectId="72057611959664640">     <owner-list>      <owner id="process5bbc4bb88" mode="IX"/>     </owner-list>     <waiter-list>      <waiter id="process4ff1b88" mode="S" requestType="wait"/>     </waiter-list>    </pagelock>    <pagelock fileid="1" pageid="10023394" dbid="8" objectname="" id="lock52a041080" mode="S" associatedObjectId="72057611961040896">     <owner-list>      <owner id="process4ff1b88" mode="S"/>     </owner-list>     <waiter-list>      <waiter id="process5bbc4bb88" mode="IX" requestType="wait"/>     </waiter-list>    </pagelock>   </resource-list>  </deadlock> </deadlock-list> ' )
    GO
    
    UPDATE #tmp
    set badXML = REPLACE( REPLACE( badXML, '<victim-list>', '<deadlock><victim-list>' ), '<process-list>','</victim-list><process-list>' )
    GO
    
    
    DECLARE @i INT = 0
    
    WHILE 1=1
    BEGIN
    
    	SET @i += 1
    	
    	BEGIN TRY
    	
    		UPDATE top(1) #tmp
    		set goodXML = badXML 
    		WHERE goodXML IS NULL
    
    		IF @@rowcount = 0 BREAK
    
    	END TRY
    	BEGIN CATCH
    		
    		IF @@error = 9436	--XML parsing: line %d, character %d, end tag does not match start tag
    			UPDATE TOP(1) #tmp
    			SET goodXML = '<error/>' 
    			WHERE goodXML IS NULL
    		
    	END CATCH
    
    	IF @i > 999 BEGIN RAISERROR( 'Too many loops!', 16, 1 )  BREAK END
    	
    END
    
    SELECT @i loops
    GO
    
    SELECT *
    FROM #tmp
    GO
    
    
    -- Lose errors here ...
    SELECT
    	t.rowId,
    	t.goodXML,
    	x.y.query('data(//inputbuf)') inputbuf,
    	x.y.value('(//@lasttranstarted)[1]', 'VARCHAR(MAX)') lasttranstarted,
    	x.y.value('(//@lockTimeout)[1]', 'VARCHAR(MAX)') lockTimeout
    FROM #tmp t
    	CROSS APPLY t.goodXML.nodes( 'deadlock-list/*' ) x(y)
    GO
    

    • Marked as answer by sqlsantos Friday, September 7, 2012 6:52 PM
    Thursday, September 6, 2012 3:50 PM
    Answerer