在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执行结果类似如下：