如何抓取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', 'datetime')		, DeadlockGraph		, DeadlockObjects = substring((SELECT (', ' + o.ObjectName)							FROM DeadlockObjects o							WHERE o.DeadlockID = e.DeadlockID							ORDER BY o.ObjectName							FOR XML PATH ('')							), 3, 4000)		, Victim = CASE WHEN v.VictimID IS NOT NULL 							THEN 1 						ELSE 0 						END		, SPID = Deadlock.Process.value('@spid', 'int')		, ProcedureName = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)')		, LockMode = Deadlock.Process.value('@lockMode', 'char(1)')		, Code = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)')		, ClientApp = CASE LEFT(Deadlock.Process.value('@clientapp', 'varchar(100)'), 29)						WHEN 'SQLAgent - TSQL JobStep (Job '							THEN 'SQLAgent Job: ' + (SELECT name FROM msdb..sysjobs sj WHERE substring(Deadlock.Process.value('@clientapp', 'varchar(100)'),32,32)=(substring(sys.fn_varbintohexstr(sj.job_id),3,100))) + ' - ' + SUBSTRING(Deadlock.Process.value('@clientapp', 'varchar(100)'), 67, len(Deadlock.Process.value('@clientapp', 'varchar(100)'))-67)						ELSE Deadlock.Process.value('@clientapp', 'varchar(100)')						END 		, HostName = Deadlock.Process.value('@hostname', 'varchar(20)')		, LoginName = Deadlock.Process.value('@loginname', 'varchar(20)')		, InputBuffer = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)')	FROM #Events e		CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/process-list/process') as Deadlock(Process)		LEFT JOIN Victims v ON v.DeadlockID = e.DeadlockID AND v.VictimID = Deadlock.Process.value('@id', 'varchar(50)')) X --In a subquery to make filtering easier (use column names, not XML parsing), no other reasonORDER BY DeadlockID DESC

   类似的结果如下：

  点击DeadlockGraph字段下的超链接就能获取死锁信息

   这样就可以看到对应的SQL语句了。

•    开启跟踪标记来获取

     如果通过上面的方式，如果还是无法查询到，那么可以开启1204，1222跟踪标记。在发生死锁的时候，由SQL Server将死锁信息写入到SQL Server错误日志中，然后通过SP_READERRORLOG查找死锁信息。

    脚本：

    dbcc traceon(1204,1222,3605,-1) 

   类似查询结果如下：

•         创建Extended Events   

    上面为SQL Server死锁信息获取的方式。如果碰到有死锁时，并且需要提单分析的时候，最好能附上抓到的死锁信息，更加快速的分析问题。

注意：本文为死锁信息抓取，而非阻塞，阻塞和死锁并不相同，抓取的方式也不一样。