EXEC sp_configure 'show advanced options', 1;  
 
        GO  
 
        RECONFIGURE;  
 
        GO  
 
        EXEC sp_configure 'blocked process threshold', 10;  
 
        GO  
 
        RECONFIGURE;  
 
        GO
 
        EXEC sp_configure 'blocked process threshold'

SELECT t1.resource_type AS [鎖類型], DB_NAME(resource_database_id) AS [數(shù)據(jù)庫名],
 
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 [等待會話執(zhí)行的批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 [等待會話執(zhí)行的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 [阻塞會話執(zhí)行的批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);