About these ads

System Health Session: Part 4   23 comments


This is the last post for the System Health Session series. In this post, I shall show you how to extract deadlock related information from the deadlock graph captured by the System Health Session.

The deadlock graph captured typically has three distinct nodes:

victim-list – Deadlock victim’s process identifier
process-list – Information pertaining to all the processes involved in the deadlock
resource-list – Information about the resources involved in the deadlock

The query below will provide you with the time stamp when the deadlock was reported along with victim process identifier.

 -- Fetch the Health Session data into a temporary table

SELECT CAST(xet.target_data AS XML) AS XMLDATA
INTO #SystemHealthSessionData
FROM sys.dm_xe_session_targets xet
JOIN sys.dm_xe_sessions xe
ON (xe.address = xet.event_session_address)
WHERE xe.name = 'system_health'
-- Gets the Deadlock Event Time and Victim Process
SELECT C.query('.').value('(/event/@timestamp)[1]', 'datetime') as EventTime,
CAST(C.query('.').value('(/event/data/value)[1]', 'varchar(MAX)') AS XML).value('(<a>/deadlock/victim-list/victimProcess/@id)[1]','varchar(100)'</a>) VictimProcess
FROM #SystemHealthSessionData a
CROSS APPLY a.XMLDATA.nodes('/RingBufferTarget/event') as T(C)
WHERE C.query('.').value('(/event/@name)[1]', 'varchar(255)') = 'xml_deadlock_report'
-- Drop the temporary table
DROP TABLE #SystemHealthSessionData 

The next query (when provided with an event time from the above query output)
will provide you a parsed version of the process list in a tabular format which
can be easier to read when you have a large number of sessions involved in the
deadlock.

 -- Fetch the Health Session data into a temporary table

SELECT CAST(xet.target_data AS XML) AS XMLDATA
INTO #SystemHealthSessionData
FROM sys.dm_xe_session_targets xet
JOIN sys.dm_xe_sessions xe
ON (xe.address = xet.event_session_address)
WHERE xe.name = 'system_health'

-- Parses the process list for a specific deadlock once provided with an event time for the deadlock from the above output

;WITH CTE_HealthSession (EventXML) AS
(
SELECT CAST(C.query('.').value('(/event/data/value)[1]', 'varchar(MAX)') AS XML) EventXML
FROM #SystemHealthSessionData a
CROSS APPLY a.XMLDATA.nodes('/RingBufferTarget/event') as T(C)
WHERE C.query('.').value('(/event/@name)[1]', 'varchar(255)') = 'xml_deadlock_report'
AND C.query('.').value('(/event/@timestamp)[1]', 'datetime') = '2011-09-28 06:24:44.700' -- Replace with relevant timestamp
)
SELECT DeadlockProcesses.value('(@id)[1]','varchar(50)') as id
,DeadlockProcesses.value('(@taskpriority)[1]','bigint') as taskpriority
,DeadlockProcesses.value('(@logused)[1]','bigint') as logused
,DeadlockProcesses.value('(@waitresource)[1]','varchar(100)') as waitresource
,DeadlockProcesses.value('(@waittime)[1]','bigint') as waittime
,DeadlockProcesses.value('(@ownerId)[1]','bigint') as ownerId
,DeadlockProcesses.value('(@transactionname)[1]','varchar(50)') as transactionname
,DeadlockProcesses.value('(@lasttranstarted)[1]','varchar(50)') as lasttranstarted
,DeadlockProcesses.value('(@XDES)[1]','varchar(20)') as XDES
,DeadlockProcesses.value('(@lockMode)[1]','varchar(5)') as lockMode
,DeadlockProcesses.value('(@schedulerid)[1]','bigint') as schedulerid
,DeadlockProcesses.value('(@kpid)[1]','bigint') as kpid
,DeadlockProcesses.value('(@status)[1]','varchar(20)') as status
,DeadlockProcesses.value('(@spid)[1]','bigint') as spid
,DeadlockProcesses.value('(@sbid)[1]','bigint') as sbid
,DeadlockProcesses.value('(@ecid)[1]','bigint') as ecid
,DeadlockProcesses.value('(@priority)[1]','bigint') as priority
,DeadlockProcesses.value('(@trancount)[1]','bigint') as trancount
,DeadlockProcesses.value('(@lastbatchstarted)[1]','varchar(50)') as lastbatchstarted
,DeadlockProcesses.value('(@lastbatchcompleted)[1]','varchar(50)') as lastbatchcompleted
,DeadlockProcesses.value('(@clientapp)[1]','varchar(150)') as clientapp
,DeadlockProcesses.value('(@hostname)[1]','varchar(50)') as hostname
,DeadlockProcesses.value('(@hostpid)[1]','bigint') as hostpid
,DeadlockProcesses.value('(@loginname)[1]','varchar(150)') as loginname
,DeadlockProcesses.value('(@isolationlevel)[1]','varchar(150)') as isolationlevel
,DeadlockProcesses.value('(@xactid)[1]','bigint') as xactid
,DeadlockProcesses.value('(@currentdb)[1]','bigint') as currentdb
,DeadlockProcesses.value('(@lockTimeout)[1]','bigint') as lockTimeout
,DeadlockProcesses.value('(@clientoption1)[1]','bigint') as clientoption1
,DeadlockProcesses.value('(@clientoption2)[1]','bigint') as clientoption2
FROM (select EventXML as DeadlockEvent FROM CTE_HealthSession) T
CROSS APPLY DeadlockEvent.nodes('//deadlock/process-list/process') AS R(DeadlockProcesses)

-- Drop the temporary table
DROP TABLE #SystemHealthSessionData

The script file for the above queries can be downloaded from here.

A sample output of the above two queries is shown below:

The second dataset which shows the parsed process list from the deadlock graph is for the timestamp highlighted above.

As mentioned in one of my previous posts, the custom reports used in the previous posts can be downloaded from here (Filename: System_Health_Session_Custom_Reports.zip).

Modification April 20th, 2012: Just updated the .sql files and added the Deadlock Report to the SkyDrive location.

Note: To make use of the deadlock graph captured by the System Health Session, you need to have the required update applied to avoid the issue mentioned in KB978629. The issue is addressed in:
981355    Cumulative Update package 1 for SQL Server 2008 R2
http://support.microsoft.com/default.aspx?scid=kb;EN-US;981355
977443    Cumulative update package 6 for SQL Server 2008 Service Pack 1
http://support.microsoft.com/default.aspx?scid=kb;EN-US;977443

If you don’t have the updates installed, then Jonathan (Blog | Twitter) has shown in his article Retrieving Deadlock Graphs with SQL Server 2008 Extended Events how to workaround the issue. Michael Zilberstein’s (Blog) article, Parsing Extended Events xml_deadlock_report, has an updated/corrected version of the T-SQL to fetch the deadlock information.

References:

Bart Duncan’s Weblog:
Deadlock Troubleshooting, Part 1
Deadlock Troubleshooting, Part 2
Deadlock Troubleshooting, Part 3

About these ads

23 responses to “System Health Session: Part 4

Subscribe to comments with RSS.

  1. Hi Amit,

    Thank you for another great post!

    One small issue I noticed is that some extra characters seem to have crept into your first query– they’re tags. Guessing maybe your blog editor “fixed” something for you there? (Hopefully the characters post literally in this comment and aren’t stripped out– they’re “a” tags used for links in HTML.)

    Best,
    Kendra

  2. Thanks Kendra. That was the wordpress formatter which is messing it up. I need to figure out a way to correct that.

  3. Hi Amit,

    awesome reports. I cannot say it in other words. This is great stuff.
    One little thing:
    Starting from the system health session dashboard and going to xml_deadlock_report there´s an error due to a missing file named
    XEDashboard.rdl

    Regards
    Dirk

  4. Thanks Dirk. That is an oversight on my part. I forgot to upload the deadlock RDL file in the uploaded reports. Thanks for reporting the issue. I need to add the deadlock report. Will do that soon and send out a tweet about the same.

  5. I just uploaded the deadlock report.

  6. Pingback: Awesomesauce SQL Server 2012: System Health Session « TroubleshootingSQL

  7. Pingback: System Health Session and Deadlocks « TroubleshootingSQL

  8. Pingback: Troubleshooting deadlocks « sqlindian – On Locks and Deadlocks

  9. Pingback: System Health Session and beyond « TroubleshootingSQL

  10. Pingback: System Health Session and beyond « TroubleshootingSQL

  11. Pingback: SQL Bangalore UG Meet « TroubleshootingSQL

  12. Amit, awesome posts. Can you add the XEDashboard.rdl report? From the main Dashboard (XEHealthSessionDashboard), if I click on security_error_ring_buffer_recorded, I get a message about the XEDashboard.rdl being missing. I just downloaded the ZIP with the reports off of skydrive. Thanks!

  13. Are you using these reports for a SQL Server 2012 instance? If yes. then you need the 2012 version which works when deployed to a SSRS instance. They can be downloaded from http://sdrv.ms/QPLiW0

  14. Amit, apologies for the newbie questions. I uploaded it to my Reporting Services server, but I’m getting an error because I don’t have DataSource1. If I create the datasource, it looks like I have to tie the report to just one machine. Am I doing something wrong? Help greatly appreciated.

  15. (apologies is this is a dupe)
    Amit, apologies for the newbie questions. I uploaded it to my Reporting Services server, but I’m getting an error because I don’t have DataSource1. If I create the datasource, it looks like I have to tie the report to just one machine. Am I doing something wrong? Help greatly appreciated.

  16. That is an oversight on my part. You can remove the Datasource1. I was using that for my development environment. The report uses the ServerName datasource which is a dynamic datasource. Hope this clarifies your question.

  17. Ever noticed how much an extra XMLIndex cuts of subsequent queries processing time :-)

    /* Fetch the Health Session data into a temporary table */
    if object_id(‘tempdb..#SystemHealthSessionData’) is null
    begin
    /* ALZDBA: add XMLIndex for faster consumption ! */
    Create table #SystemHealthSessionData
    ( PK int not null primary key clustered,
    XMLDATA xml not null
    );
    CREATE PRIMARY XML INDEX PXML_SystemHealthSessionData
    ON #SystemHealthSessionData (XMLDATA);

    insert INTO #SystemHealthSessionData

    SELECT cast( 1 as int ) as PK, CAST(xet.target_data AS XML) AS XMLDATA
    FROM sys.dm_xe_session_targets xet
    INNER JOIN sys.dm_xe_sessions xe
    ON ( xe.address = xet.event_session_address )
    WHERE xe.name = ‘system_health';

    end ;

    Nice reports !

  18. Thanks. Yes, the XML index helps for larger datasets. For the smaller datasets, it did not help much.

  19. Amit, have you thought about how in SQL 2012 we have two targets for the system_health, a file and the ring buffer. When you don’t define which target to use, it seems to magically get to the file target, but does it query the ring buffer also? I have a system that fills the ring buffer easily, so I would like to skip it if ultimately not useful. Thanks for the great post!

  20. Well, I was wrong, it seems to not get the file target information, it is just a pointer to the file, so I am off to find out how to consume the file_target or create a dedicated dead_lock ring_buffer session to monitor this server.

  21. In my query, I filter and pick the ring buffer target to query the data.

  22. You can get the information from the XE file target using the function sys.fn_xe_file_target_read_file function. I showed how to do this in http://troubleshootingsql.com/2012/12/11/handling-deadlocked-schedulers-is-a-piece-of-cake-now/

  23. Also, I have shown how to extract deadlock information from the System Health Session in this post: http://troubleshootingsql.com/2012/09/06/system-health-session-and-deadlocks/

It is always good to hear from you! :)

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,291 other followers

%d bloggers like this: