监控-阻塞检查
原始脚本来自TG,自己对部分脚本做了调整,分享出来仅供参考,请勿整篇Copy!
在文章如何使用Profiler跟踪阻塞信息,提到可以使用[SQL Server警报+作业]来自动化侦测和收集阻塞信息。但在测试后发现警报触发条件不好设置,计数器Average Wait Time (ms)/Lock Wait Time (ms)的值无法反映出具体的某组阻塞与被阻塞的关系。累计值超过阈值可能是多条阻塞语句造成,也有可能是一个长时间阻塞的语句造成。在累计值超过阈值之后,可能没有阻塞,但警报却依旧会被触发。
使用以下语句获取【阻塞信息】,语句从sys.dm_os_waiting_tasks视图获取阻塞,结合其他相关DMV扩展阻塞与被阻塞信息
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
USE [DBA_Monitor] GO /****** 对象: StoredProcedure [dbo].[DBA_Pro_Get_BlockedInfo] 脚本日期: 12/14/2016 09:19:20 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /****************************************************************** 【概要说明】 <作 者> DBA <创建时间> 2016/11/22 16:40 <中文名称> 获取阻塞信息 <功能说明> 采集当前SQL实例下面的阻塞信息 <调用方式> 作业方式定时调用为主;错误处理后,维护人员手工调用为辅 <执行说明> 先决条件:正确的传递实例编号参数@InstanceID 注意事项:暂无 <调用示例> exec DBA_Pro_Get_BlockedInfo 100233 【修订记录】 ------------------------------------------------------------------- <2016/11/22 16:40> <DBA>: 创建 ******************************************************************/ ALTER proc [dbo].[DBA_Pro_Get_BlockedInfo] @InstanceID INT AS BEGIN SET NOCOUNT ON IF (@InstanceID < 100000 OR @InstanceID > 999999) BEGIN PRINT '数据库实例编号参数错误!' RETURN END BEGIN TRY --得到阻塞对应关系 SELECT distinct wt.session_id AS BlockedSessionId, DB_NAME(tl.resource_database_id) AS DatabaseName, wt.wait_type AS WaitType, tl.request_mode+':'+tl.resource_type+':'+tl.resource_description AS WaitResource, es1.last_request_start_time AS BlockedTime, wt.wait_duration_ms AS WaitDuration_ms, ec1.client_net_address AS BlockedClientAddress, --h1.text AS BlockedStmt, SUBSTRING( h1.text, (er1.statement_start_offset / 2) + 1, ( ( CASE WHEN er1.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), h1.text)) * 2 ELSE er1.statement_end_offset END - er1.statement_start_offset ) / 2 ) + 1 ) AS BlockedStmt, wt.blocking_session_id AS BlockingSessionId, es2.transaction_isolation_level AS BlockingIsolationLevel, wt.resource_description AS BlockingHoldResource, --h2.text AS BlockingStmt, SUBSTRING( h2.text, (ISNULL(er2.statement_start_offset, 0) / 2) + 1, ( ( CASE WHEN ISNULL(er2.statement_end_offset, 0) <= 0 THEN LEN(CONVERT(NVARCHAR(MAX), h2.text)) * 2 ELSE er2.statement_end_offset END - ISNULL(er2.statement_start_offset, 0) ) / 2 ) + 1 ) AS BlockingStmt, ec2.client_net_address AS BlockingClientAddress, es2.program_name AS BlockingProgramName, es2.login_name AS BlockingLoginame, GETDATE() AS InsertTime INTO #BlockInfo FROM sys.dm_tran_locks AS tl WITH(NOLOCK) --会过滤并行问题CXPACKET INNER JOIN sys.dm_os_waiting_tasks AS wt WITH(NOLOCK) ON wt.resource_address = tl.lock_owner_address INNER JOIN sys.dm_exec_connections ec1 WITH(NOLOCK) ON ec1.session_id = wt.session_id INNER JOIN sys.dm_exec_connections ec2 WITH(NOLOCK) ON ec2.session_id = wt.blocking_session_id INNER JOIN sys.dm_exec_sessions AS es1 WITH(NOLOCK) ON es1.session_id = ec1.session_id INNER JOIN sys.dm_exec_sessions AS es2 WITH(NOLOCK) ON es2.session_id = ec2.session_id INNER JOIN sys.dm_exec_requests er1 WITH(NOLOCK) ON er1.session_id = ec1.session_id LEFT JOIN sys.dm_exec_requests er2 WITH(NOLOCK) ON er2.session_id = ec2.session_id CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1 CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2 --获取spid对应的批处理语句 DECLARE @count INT, @s NVARCHAR(MAX), @i INT SELECT id = IDENTITY(INT, 1, 1), SessionId INTO #SessionId FROM ( SELECT BlockedSessionId AS SessionId FROM #BlockInfo UNION SELECT BlockingSessionId FROM #BlockInfo ) a SELECT @count = @@rowcount, @i = 1 CREATE TABLE #EventInfo ( id INT IDENTITY(1, 1), EventType NVARCHAR(MAX), Parameters INT, EventInfo NVARCHAR(MAX) ) IF @count > 0 BEGIN WHILE @i <= @count BEGIN SELECT @s = 'dbcc inputbuffer(' + CAST(SessionId AS VARCHAR) + ')' FROM #SessionId a INNER JOIN sys.dm_exec_connections b ON a.SessionId=b.session_id WHERE id = @i INSERT #EventInfo(EventType,Parameters,EventInfo) EXEC (@s) SET @i = @i + 1 END END INSERT INTO Info_Blocked ( InstanceID, BlockedSessionId, DatabaseName, WaitType, WaitResource, BlockedTime, WaitDuration_ms, BlockedClientAddress, BlockedStmt, BlockingSessionId, BlockingIsolationLevel, BlockingHoldResource, BlockingStmt, BlockingClientAddress, BlockingProgramName, BlockingLoginame, InsertTime, BlockedBatch, BlockingBatch ) SELECT @InstanceID, a.*, c.EventInfo, c1.EventInfo FROM #BlockInfo a INNER JOIN #SessionId b ON a.BlockedSessionId = b.SessionId INNER JOIN #EventInfo c ON b.id = c.id INNER JOIN #SessionId b1 ON a.BlockingSessionId = b1.SessionId INNER JOIN #EventInfo c1 ON b1.id = c1.id IF OBJECT_ID('tempdb..#BlockInfo')IS NOT NULL DROP TABLE #BlockInfo IF OBJECT_ID('tempdb..#SessionId')IS NOT NULL DROP TABLE #SessionId IF OBJECT_ID('tempdb..#EventInfo')IS NOT NULL DROP TABLE #EventInfo END TRY BEGIN CATCH insert into ExecErrorLog(InstanceID,Error_Procname,Error_Numbers,Error_Messages,Error_Serverity,Error_States,Error_Lines) SELECT @InstanceID,ERROR_PROCEDURE(),ERROR_NUMBER(),ERROR_MESSAGE(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_LINE() END CATCH END GO
几处改进的地方:
1、master.dbo.sysprocesses,对于并行操作,此视图同一spid会存在多个kpid。不加区分的话,会得到很多重复的阻塞信息
2、master.dbo.sysprocesses,拆分成sys.dm_exec_connections、sys.dm_exec_sessions、sys.dm_exec_requests三个DMV代替
3、更新各字段的取值,分清主次。例如sys.dm_os_waiting_tasks、sys.dm_tran_locks得到阻塞与被阻塞会话ID、阻塞时长、等待资源、持有资源;sys.dm_exec_connections、sys.dm_exec_requests、sys.dm_exec_sql_text得到阻塞与被阻塞运行的语句、连接IP;sys.dm_exec_sessions得到上次请求时间、事务隔离级别、程序名称、登录名。
4、根据会话ID,使用DBCC INPUTBUFFER()获得从客户端发送到SQLServer实例的最后一个批处理语句
5、当阻塞发生在Object时,WaitResource不会有值
创建作业,设定调度。定时执行存储过程,检测是否有阻塞信息。
测试样例
使用下面脚本创建阻塞
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
use test go create table waiting_tasks1(id int,rundate datetime) create table waiting_tasks2(id int,rundate datetime) --生成连续数字 ;with GenerateHelpData as ( select 1 as id,getdate() rundate union all select id+1,dateadd(ss,1,rundate) from GenerateHelpData where id<5000 ) insert into waiting_tasks1 --分别往waiting_tasks1、waiting_tasks2表插入数据 select id,rundate from GenerateHelpData option (maxrecursion 5000); --会话1,开启事务更新 并且不提交 begin tran update waiting_tasks1 set rundate = getdate() --rollback --会话2,做一个查询 并且开启并行 select * from waiting_tasks1 a inner join waiting_tasks2 b on a.id = b.id option (querytraceon 8649)
查看连接信息
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
--查看连接信息 select spid,kpid,blocked,waittype,waittime,lastwaittype,waitresource,dbid,cpu,login_time,last_batch,ecid,open_tran,status from sys.sysprocesses where spid in(52,59) select session_id,most_recent_session_id,connect_time,last_read,client_net_address,most_recent_sql_handle from sys.dm_exec_connections where session_id in(52,59) select session_id,login_time,last_request_start_time,status,transaction_isolation_level,login_name,program_name from sys.dm_exec_sessions where session_id in(52,59) select session_id,start_time,status,database_id,blocking_session_id,wait_type,wait_time,last_wait_type,wait_resource ,statement_start_offset,statement_end_offset from sys.dm_exec_requests where session_id in(52,59)
sys.dm_os_waiting_tasks
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
--sys.dm_os_waiting_tasks select * from sys.dm_os_waiting_tasks wt where wt.session_id in(52,59) order by wt.exec_context_id,wt.blocking_exec_context_id
得到阻塞对应关系
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
--得到阻塞对应关系 SELECT wt.session_id AS BlockedSessionId, DB_NAME(tl.resource_database_id) AS DatabaseName, wt.wait_type AS WaitType, tl.request_mode + ':' + tl.resource_type + ':' + tl.resource_description AS WaitResource, wt.wait_duration_ms AS WaitDuration_ms, wt.resource_address AS BlockingResourceAddress, wt.blocking_session_id AS BlockingSessionId, wt.resource_description AS BlockingHoldResource FROM sys.dm_tran_locks AS tl WITH(NOLOCK) --会过滤并行问题CXPACKET INNER JOIN sys.dm_os_waiting_tasks AS wt WITH(NOLOCK) ON wt.resource_address = tl.lock_owner_address
综合以上脚本,就是文章开篇给出的存储过程。执行存储过程可以获取阻塞信息
|
【作者】: 醒嘞 |
【出处】: http://www.cnblogs.com/Uest/ | |
【声明】: 本文内容仅代表个人观点。如需转载请保留此段声明,且在文章页面明显位置给出原文链接! |