DECLARE@ringAS[xml];

 

SET@ring= (SELECT     cast(xet.[target_data]AS[xml])

                        FROM       sys.dm_xe_session_targetsxet

                        INNERJOINsys.dm_xe_sessionsxe

                                        ONxe.[address]=xet.[event_session_address]

                        WHERE      xe.[name]=’system_health’);

 

SELECT      ”                                                                                                          AS’DEADLOCKS’,

                        row_number()

                                OVER (

                                        ORDERBYsyshealth.xevent.value(‘(@timestamp)’,’DATETIME’))           AS’Sequence’,

                        syshealth.xevent.value(‘(@timestamp)’,

                                                                   ‘DATETIME’)                                    AS’Deadlock time’,

                        –SysHealth.XEvent.query(‘.’) AS [DeadlockEvent],

 

                        cast(syshealth.xevent.value(‘data[1]’,

                                                                                ‘NVARCHAR(MAX)’)ASxml)         AS’Deadlock graph’

–SysHealth.XEvent.value(‘data[1]’,’NVARCHAR(MAX)’) AS DeadlockGraph

FROM        (SELECT@ringASring)ASbuffer

CROSSapplyring.nodes (‘//RingBufferTarget/event’)ASsyshealth (xevent)

WHERE       syshealth.xevent.value(‘(@name)[1]’,

                                                                   ‘varchar (100)’)=’xml_deadlock_report’

ORDER       BY[deadlock time]DESC;