获取SQL Server阻塞显示阻塞链和阻塞资源并提供简单建议脚本原创
11人赞赏了该文章
364次浏览
未经作者许可,禁止转载编辑于2024年07月09日 10:06:32
在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
执行结果类似如下:
赞 11
11人点赞
还没有人点赞,快来当第一个点赞的人吧!
打赏
0人打赏
还没有人打赏,快来当第一个打赏的人吧!
推荐阅读