获取SQL Server阻塞显示阻塞链和阻塞资源并提供简单建议脚本

     在SQL Server中通过脚本获取阻塞后，如何找到等待的源会话，分析阻塞等待资源(OBJECT,KEY,RID,PAGE)具体是哪个对象，以及对应的等待类型下需要去检查什么，同时数据库的事务隔离级别是否正确，是否阻塞源正在执行DBCC相关的SQL，涉及的内容就比较多了。本文基于上面的内容，编写下面的SQL，执行后，能够获取上述的内容，来更好的分析问题。

set nocount off;
if object_id('tempdb..#blockinfo') <>0  drop table #blockinfo
if object_id('tempdb..#restypeinfo') <>0  drop table #restypeinfo

go
SELECT IDENTITY(int,1,1) fseq, cast(t1.resource_type as varchar) as  resource_type, t3.name as dbname,
t1.resource_associated_entity_id AS blockobject,
t1.resource_description , t1.request_mode , 
t1.request_session_id AS waitingsession, 
(select transaction_isolation_level from sys.dm_exec_sessions where session_id=t1.request_session_id) as waitingsession_isolation_level,
t2.wait_duration_ms ,       
(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 waitingsessionbatchsql,
(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 watingsessionshortsql,                    
t2.blocking_session_id AS blockingsession, 
(select transaction_isolation_level from sys.dm_exec_sessions where session_id=t2.blocking_session_id) as blockingsession_isolation_level,
(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 blockingsessionsql
into #blockinfo
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 
join sys.databases t3 on t1.resource_database_id=t3.database_id
---transaction_isolation_level 0:Unspecified 1:ReadUncommitted;2:ReadCommitted;3:RepeatableRead;4:Serializable;5:Snapshot

if @@ROWCOUNT<=0
begin
   select N'*************当前没有阻塞*************'
   return
end

select N'*************阻塞详情*************'
select dbname N'数据库名',resource_type N'等待资源类型',blockobject N'阻塞对象',resource_description N'等待资源描述',
	request_mode N'请求类型', waitingsession N'等待会话',waitingsessionbatchsql N'等待会话执行SQL',
	case  when waitingsession_isolation_level=0 then 'Unspecified' 
		  when waitingsession_isolation_level=1 then 'ReadUncommitted'
		  when waitingsession_isolation_level=2 then 'ReadCommitted'
		  when waitingsession_isolation_level=3 then 'RepeatableRead'
		  when waitingsession_isolation_level=4 then 'Serializable'
		  when waitingsession_isolation_level=5 then 'Snapshot'
	end N'等待会话事务隔离级别',wait_duration_ms N'等待会话等待时间',
	blockingsession N'阻塞会话',blockingsessionsql N'阻塞会话执行SQL',
	case  when blockingsession_isolation_level=0 then 'Unspecified' 
		  when blockingsession_isolation_level=1 then 'ReadUncommitted'
		  when blockingsession_isolation_level=2 then 'ReadCommitted'
		  when blockingsession_isolation_level=3 then 'RepeatableRead'
		  when blockingsession_isolation_level=4 then 'Serializable'
		  when blockingsession_isolation_level=5 then 'Snapshot'
	end N'阻塞会话事务隔离级别'
from #blockinfo

declare @message as nvarchar(max)
set @message=N''

---判断请求类型是否为S,并且事务隔离级别设置为2时，如果存在，检测数据库读快照隔离级别是否开启
select @message=@message+name+','  from sys.databases where name in(
select distinct dbname from #blockinfo where request_mode='S' and (waitingsession_isolation_level=2 or blockingsession_isolation_level=2))
and is_read_committed_snapshot_on=0 

if len(@message)>0 
begin
	set @message=@message+N'数据库隔离级别需要设置为：读提交快照隔离级别';
	select @message
end;

--判断阻塞会话是否包含dbcc 命令，如果有先停止
set @message=''
select @message=@message+N'会话：'+cast(blockingsession as varchar)+N';正在执行：'+blockingsessionsql+';'  from (
select distinct blockingsession,blockingsessionsql from #blockinfo where blockingsessionsql like 'DBCC%' )
t

if len(@message)>0 
begin
	set @message=@message+N'数据库正在执行DBCC操作，需要停止执行DBCC的会话才能解决阻塞';
	select @message
end;

--获取阻塞语句源头,blockingsession不在waitingsession中即为源头
--获取阻塞链和执行SQL语句
select N'*************阻塞链和执行SQL语句*************'
;WITH allsessions as
(select distinct  blockingsession as sessionid from #blockinfo
 union 
 select distinct  waitingsession as sessionid  from #blockinfo),
sessioninfo as(
select t1.sessionid,t2.waitingsession,t2.blockingsession from allsessions t1
left join #blockinfo t2 on t1.sessionid=t2.waitingsession),
blockchain AS (  
    SELECT sessionid,waitingsession,blockingsession, 1 AS Level  
    FROM sessioninfo  
    WHERE blockingsession IS NULL  
  
    UNION ALL  
  
    SELECT e.sessionid,e.waitingsession,e.blockingsession, ecte.Level + 1  
    FROM sessioninfo e  
    INNER JOIN blockchain ecte ON e.blockingsession = ecte.sessionid  
),allsql as
(select blockingsession as sessionid,blockingsessionsql as sqltext from #blockinfo
union 
select waitingsession as sessionid,waitingsessionbatchsql as sqltext from #blockinfo)
SELECT t0.sessionid as N'会话ID',isnull(cast(t0.blockingsession as nvarchar(8)),N'阻塞源头') N'阻塞会话ID',Level,t1.sqltext N'会话执行SQL'
FROM blockchain t0
left join allsql  t1 on t0.sessionid=t1.sessionid
ORDER BY Level;

--分析阻塞资源
declare @i as int
declare @databaseName nvarchar(100)  
declare @keyValue nvarchar(100) 
declare @lockres nvarchar(100)
declare @hobbitID bigint
declare @objectName sysname
declare @ObjectLookupSQL as nvarchar(max)
declare @rowcount as int
set @ObjectLookupSQL=''

;with t as(select distinct dbname,blockobject,resource_description ,resource_type from #blockinfo)
select dbname,blockobject,resource_description ,resource_type,IDENTITY(int,1,1) fseq
into #restypeinfo
from t

set @rowcount=@@ROWCOUNT
set @i=1
while @i<=@rowcount
begin
    ----阻塞资源为KEY,blockobject 为hobbitID
	select @databaseName=dbname,@hobbitID=blockobject,@lockres=resource_description from #restypeinfo where fseq=@i and resource_type='KEY'		
	if @@ROWCOUNT>0
	begin	    
		select N'*************解析KEY对象:hobt_id='+convert(nvarchar(50), @hobbitID)
		set @ObjectLookupSQL= '
		SELECT @objectName = o.name
		FROM ' + quotename(@databaseName) + '.sys.partitions p
		JOIN ' + quotename(@databaseName) + '.sys.indexes i ON p.index_id = i.index_id AND p.[object_id] = i.[object_id]
		join ' + quotename(@databaseName)+ '.sys.objects o on o.object_id = i.object_id
		WHERE hobt_id = ' + convert(nvarchar(50), @hobbitID) + ''

		exec sp_executesql @ObjectLookupSQL
			,N'@objectName sysname OUTPUT'
			,@objectName = @objectName output
   
	    select N'*************阻塞对象为：'+@objectName+N',检查是否定期执行了dbcc dbreindex进行优化'

		select N'*************解析KEY资源:lockres='+convert(nvarchar(50), @lockRes)
		declare @finalResult nvarchar(max) = N'select %%lockres%% hashvalue,'''+@objectName+''' tabname,SYS.fn_PhysLocFormatter(%%PHYSLOC%%) page,* 
		from ' + quotename(@databaseName) + '.dbo.' + @objectName + ' with(nolock)
		where %%lockres%% = ''' + @lockRes+''''
	
		exec sp_executesql @finalResult
	end

	--阻塞资源为Object
	select @databaseName=dbname,@hobbitID=blockobject,@lockres=resource_description from #restypeinfo where fseq=@i and resource_type='OBJECT'
	if @@ROWCOUNT>0
	begin
		select N'*************解析OBJECT对象:OBJECT='+convert(nvarchar(50), @hobbitID)
		set @ObjectLookupSQL  = '
		SELECT @objectName = o.name
		FROM ' + quotename(@databaseName) + '.sys.objects o		
		WHERE o.object_id = ' + convert(nvarchar(50), @hobbitID) + ''

		exec sp_executesql @ObjectLookupSQL
			,N'@objectName sysname OUTPUT'
			,@objectName = @objectName output
   
	    select N'*************阻塞对象为：'+@objectName+N',检查是否需要更新整表数据以及是否定期执行了dbcc dbreindex进行优化' 
	end

	--阻塞资源为PAGE
	select @databaseName=dbname,@hobbitID=blockobject,@lockres=resource_description from #restypeinfo where fseq=@i and resource_type='PAGE'
	if @@ROWCOUNT>0
	begin
		select N'*************解析PAGE对象:hobt_id='+convert(nvarchar(50), @hobbitID)
		set @ObjectLookupSQL= '
		SELECT @objectName = o.name
		FROM ' + quotename(@databaseName) + '.sys.partitions p
		JOIN ' + quotename(@databaseName) + '.sys.indexes i ON p.index_id = i.index_id AND p.[object_id] = i.[object_id]
		join ' + quotename(@databaseName)+ '.sys.objects o on o.object_id = i.object_id
		WHERE hobt_id = ' + convert(nvarchar(50), @hobbitID) + ''

		exec sp_executesql @ObjectLookupSQL
			,N'@objectName sysname OUTPUT'
			,@objectName = @objectName output
   
	    select N'*************阻塞对象为：'+@objectName +N',检查关联字段是否存在索引，wehre 条件中过滤条件是否影响多条，同时检查是否定期执行了dbcc dbreindex进行优化'
		select N'可以通过执行下面的SQL，获取PAGE中的详细内容：'+char(13)+char(10)+'dbcc traceon(3604);dbcc page('+quotename(@databaseName)+','+trim(REPLACE(@lockres,':',','))+',3)'
	end


	--阻塞资源为RID
	select @databaseName=dbname,@hobbitID=blockobject,@lockres=resource_description from #restypeinfo where fseq=@i and resource_type='RID'
	if @@ROWCOUNT>0
	begin
		select N'*************解析RID对象:hobt_id='+convert(nvarchar(50), @hobbitID)
		set @ObjectLookupSQL= '
		SELECT @objectName = o.name
		FROM ' + quotename(@databaseName) + '.sys.partitions p
		JOIN ' + quotename(@databaseName) + '.sys.indexes i ON p.index_id = i.index_id AND p.[object_id] = i.[object_id]
		join ' + quotename(@databaseName)+ '.sys.objects o on o.object_id = i.object_id
		WHERE hobt_id = ' + convert(nvarchar(50), @hobbitID) + ''

		exec sp_executesql @ObjectLookupSQL
			,N'@objectName sysname OUTPUT'
			,@objectName = @objectName output
   
	    select N'*************阻塞对象为：'+@objectName +N',检查表是否有聚集索引'
		set @lockres=replace(left(@lockres,len(@lockres)-charindex(N':',trim(reverse(@lockres)))),':',',')
		select N'可以通过执行下面的SQL，获取PAGE中的详细内容：'+char(13)+char(10)+'dbcc traceon(3604);dbcc page('+quotename(@databaseName)+','+@lockres+',3)'
	end

	set @i=@i+1
end

执行结果类似如下：