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', '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
ORDER BY DeadlockID DESC

    查询慢语句：

SELECT top 10
s.[session_id],
r.[start_time],
DATEDIFF(SECOND,r.start_time,GETDATE()) AS elapsed_seconds,
r.[status] AS RequestStatus,
DB_NAME(r.database_id) AS DatabaseName,
--r.[wait_type],
--r.[wait_resource],
--r.[wait_time],
r.[reads],
r.[writes],
r.[logical_reads],
r.cpu_time,
r.total_elapsed_time,
s.[status] AS SessionStatus,
--s.[host_name],
--s.[original_login_name],
--s.[nt_user_name],
--s.[program_name],
--s.[client_interface_name],
--c.[client_net_address],
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 
- r.statement_start_offset) / 2) AS ExecutingSQL,
qp.query_plan
FROM sys.dm_exec_requests (nolock) r
INNER JOIN sys.dm_exec_sessions (nolock) s ON r.session_id=s.session_id
LEFT JOIN sys.dm_exec_connections (nolock) c ON c.session_id=s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) as qp

    查询索引碎片信息/重建索引：

DBCC SHOWCONTIG(T_AR_RECEIVABLE)  --碎片信息
DECLARE @DBCCStringNVARCHAR(1000)
DECLARE @TableNameVARCHAR(100)
DECLARE Cur_IndexCURSOR FOR
SELECT Name AS TblName 
FROM sysobjects 
WHERE xType = 'U' AND (NAME LIKE '%T_SAL_ORDERENTRY_R%')
AND (name NOT LIKE 'TMP%') 
ORDER BY TblName
FOR READ ONLY
OPEN Cur_Index
FETCH NEXT FROM Cur_Index INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @DBCCString = 'DBCC DBREINDEX(@TblName,'''')WITH NO_INFOMSGS'
  EXEC SP_EXECUTESQL  @DBCCString, N'@TblName VARCHAR(100)', @TableName
  PRINT '重建表' + @TableName + '的索引........OK!'
  FETCH NEXT FROM Cur_Index INTO @TableName
END
CLOSE Cur_Index
DEALLOCATE Cur_Index
PRINT '操作完成！'
--2  更新统计信息
DECLARE @SqlStr1 NVARCHAR(MAX)
SET @SqlStr1 = ''
SELECT  @SqlStr1 = @SqlStr1 + 'UPDATE STATISTICS '+ name + ';'  
FROM sysobjects 
WHERE xtype = 'U' AND (name LIKE 'T_SAL_ORDERENTRY_R%') AND (name NOT LIKE 'TMP%') 
EXEC (@SqlStr1)

脚本开销/被阻塞线程id查询：

SELECT  spid AS 进程号, blocked AS 被阻塞, name AS 数据库名称, cpu  AS  CPU开销, physical_io AS  IO开销, 
memusage AS 内存开销, loginame AS 登录账号, login_time AS 登录时间, last_batch AS 最后执行时间,  
hostname AS 客户端计算机名, program_name AS 应用程序名称,  sqlbatchs.text AS 最后执行的语句 
FROM sys.sysprocesses  
JOIN sys.sysdatabases  ON sys.sysprocesses.dbid = sys.sysdatabases.dbid
CROSS APPLY   fn_get_sql(sql_handle ) as sqlbatchs
WHERE spid > 50 --50以下为系统进程