Sql Server死锁阻塞查询语句

    在处理星空提单时经常遇到各种Sql Server性能问题，这时经常要查询出Sql死锁或阻塞语句，客户在自行查询相关性能报告时可参照此帖子。注本文例举的所有Sql语句最初来至星空DBA或运维人员。

    查询阻塞脚本Sql如下：

SELECT t1.resource_type AS [锁类型], 
DB_NAME(resource_database_id) AS [数据库名],
t1.resource_associated_entity_id AS [阻塞资源对象],
t1.resource_description as [资源描述信息], 
t1.request_mode AS [请求的锁], 
t1.request_session_id AS [等待会话], 
t2.wait_duration_ms AS [等待时间],       
(
    SELECT [text] 
    FROM sys.dm_exec_requests AS r WITH (NOLOCK)                      
    CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) 
    WHERE r.session_id = t1.request_session_id
) AS [等待会话执行的批SQL],
(
    SELECT SUBSTRING(qt.[text], r.statement_start_offset / 2, (CASE WHEN r.statement_end_offset = -1 THEN                 LEN(CONVERT(nvarchar(max), qt.[text])) * 2 ELSE r.statement_end_offset END) / 2) 
    FROM sys.dm_exec_requests AS r WITH (NOLOCK)
    CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS qt
    WHERE r.session_id = t1.request_session_id
) AS [等待会话执行的SQL],                    
t2.blocking_session_id AS [阻塞会话],                                        
(
    SELECT [text] FROM sys.sysprocesses AS p                                       
    CROSS APPLY sys.dm_exec_sql_text(p.[sql_handle]) 
    WHERE p.spid = t2.blocking_session_id
) AS [阻塞会话执行的批SQL]
FROM sys.dm_tran_locks AS t1 WITH (NOLOCK)
INNER JOIN sys.dm_os_waiting_tasks AS t2 WITH (NOLOCK) ON t1.lock_owner_address = t2.resource_address OPTION (RECOMPILE);

    查询死锁信息语句(针对SQL Server 2008 R2)：

SELECT XEventData.XEvent.value('@timestamp', 'datetime2(3)'),  
cast(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as xml) 
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 = N'ring_buffer'
) AS Data 
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)     
WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report';

    查询死锁信息语句(针对高版本SQL Server)：

DECLARE @SessionName SysName 
SELECT @SessionName = 'system_health'
IF OBJECT_ID('tempdb..#Events') IS NOT NULL 
BEGIN
    DROP TABLE #Events
END
DECLARE @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_address
WHERE 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'
--Keep this as a separate table because it's called twice in the next query.  You don't want this running twice.
SELECT DeadlockGraph = CAST(event_data AS XML), DeadlockID = Row_Number() OVER(ORDER BY file_name, file_offset)
INTO #Events
FROM sys.fn_xe_file_target_read_file(@Target_File_WildCard, null, null, null) AS F
WHERE 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', 'varcha