如何抓取SQL Server死锁信息

        有时候系统提示会话被牺牲或者牺牲品的关键字信息，说明数据库端发生了死锁。对于已经发生的死锁，在SQL Server中是否可以查询到死锁对应信息呢？当然是可以的，SQL Server提供多种方式来获取。

•        基于通过视图来获取死锁，针对不同的SQL Server版本，会有所不同。某些情况下语句可能无法执行出结果，这种情况需要杀掉会话，采用其他方式查询。
  

      SQL Server 2008

SELECT  XEventData.XEvent.value('@timestamp', 'datetime2(3)'),	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' AND st.target_name = 'ring_buffer') AS Data CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)    WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report' option(maxdop 1,RECOMPILE);

      SQL Server 2012

SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph, XEvent.value('(/event/@timestamp)[1]','DATETIMEOFFSET') AS BeginTime    FROM ( SELECT XEvent.query('.') AS XEvent        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 s.name = 'system_health'                AND st.target_name = 'ring_buffer'               ) AS Data               CROSS APPLY                  TargetData.nodes                     ('RingBufferTarget/event[@name="xml_deadlock_report"]')              AS XEventData ( XEvent )       ) AS src	;

        类似结果    

    但是上面的脚本，在SQL Server 后续的版本上有时候并不可靠，查询不到结果，这个时候，我们可以通过下面的语句在SQL Server服务器上执行，通过分析system_health*文件来获取死锁信息。

DECLARE @SessionName SysName SELECT @SessionName = 'system_health'IF OBJECT_ID('tempdb..#Events') IS NOT NULL BEGIN	DROP TABLE #EventsENDDECLARE @Target_File NVarChar(1000)	, @Target_Dir NVarChar(1000)	, @Target_File_WildCard NVarChar(1000)SELECT @Target_File = CAST(t.target_data as XML).value('EventFileTarget[1]/File[1]/@name', 'NVARCHAR(256)')FROM sys.dm_xe_session_targets t	INNER JOIN sys.dm_xe_sessions s ON s.address = t.event_session_addressWHERE s.name = @SessionName	AND t.target_name = 'event_file'SELECT @Target_Dir = LEFT(@Target_File, Len(@Target_File) - CHARINDEX('\', REVERSE(@Target_File))) SELECT @Target_File_WildCard = @Target_Dir + '\'  + @SessionName + '_*.xel'SELECT DeadlockGraph = CAST(event_data AS XML)	, DeadlockID = Row_Number() OVER(ORDER BY file_name, file_offset)INTO #EventsFROM sys.fn_xe_file_target_read_file(@Target_File_WildCard, null, null, null) AS FWHERE event_data like '<event name="xml_deadlock_report%';WITH Victims AS(	SELECT VictimID = Deadlock.Victims.value('@id', 'varchar(50)')		, e.DeadlockID 	FROM #Events e		CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/victim-list/victimProcess') as Deadlock(Victims)), DeadlockObjects AS(	SELECT DISTINCT e.DeadlockID		, ObjectName = Deadlock.Resources.value('@objectname', 'nvarchar(256)')	FROM #Events e		CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/resource-list/*') as Deadlock(Resources))SELECT *FROM(	SELECT e.DeadlockID		, TransactionTime = Deadlock.Process.value('@lasttranstarted'