sqlserver监控阻塞（死锁）具体情况

2024-06-01 11:10| 来源: 网络整理| 查看: 265

　　公司sqlserver的监控系统主要是采用zabbix监控，但是zabbix的监控只能通过性能计数器给出报警，而无法给出具体的阻塞情况，比如阻塞会话、语句、时间等，所以需要配合sqlserver的一些特性来进行监控，这里给出一个方案：

　　1.创建阻塞日志表，用于记录阻塞情况

　　2.新建作业，用于将阻塞情况记录到阻塞日志表中，并发送邮件（如果没有配置邮件，或者不需要发送邮件，可以忽略此步骤）

　　3.创建警报，当阻塞大于阈值时，触发上面作业

　　在数据库阻塞值大于阈值时，在原有zabbix的监控上，将阻塞报警以短信和邮件方式发送给dba，同时将阻塞信息记录到阻塞记录表中，将阻塞的具体信息通过邮件形式发送给aba，帮助dba进行系统诊断。

　　查询阻塞情况依赖于以下sql：

--查询阻塞 SELECT R.session_id AS BlockedSessionID , S.session_id AS BlockingSessionID , Q1.text AS BlockedSession_TSQL , Q2.text AS BlockingSession_TSQL , C1.most_recent_sql_handle AS BlockedSession_SQLHandle , C2.most_recent_sql_handle AS BlockingSession_SQLHandle , S.original_login_name AS BlockingSession_LoginName , S.program_name AS BlockingSession_ApplicationName , S.host_name AS BlockingSession_HostName FROM sys.dm_exec_requests AS R INNER JOIN sys.dm_exec_sessions AS S ON R.blocking_session_id = S.session_id INNER JOIN sys.dm_exec_connections AS C1 ON R.session_id = C1.most_recent_session_id INNER JOIN sys.dm_exec_connections AS C2 ON S.session_id = C2.most_recent_session_id CROSS APPLY sys.dm_exec_sql_text(C1.most_recent_sql_handle) AS Q1 CROSS APPLY sys.dm_exec_sql_text(C2.most_recent_sql_handle) AS Q2

对sql进行测试，表t中只有一条数据。会话1中执行以下sql

会话2执行sql后产生阻塞

用该sql查询的结果：

　　对于该sql的字段很简单，blocked开头的表示被阻塞的，blocking表示阻塞的。

一.创建阻塞日志表，用于记录阻塞情况

USE etcp_alert GO CREATE TABLE [dbo].[BlockLog] ( Id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY , [BlockingSessesionId] [smallint] NULL , [ProgramName] [nchar](128) NULL , [HostName] [nchar](128) NULL , [ClientIpAddress] [varchar](48) NULL , [DatabaseName] [sysname] NOT NULL , [WaitType] [nvarchar](60) NULL , [BlockingStartTime] [datetime] NOT NULL , [WaitDuration] [bigint] NULL , [BlockedSessionId] [int] NULL , [BlockedSQLText] [nvarchar](MAX) NULL , [BlockingSQLText] [nvarchar](MAX) NULL , [dt] [datetime] NOT NULL ) ON [PRIMARY] GO

二、新建作业，用于将阻塞情况记录到阻塞日志表中，并发送邮件

在新建作业步骤中，选择数据库tempdb，并插入代码：

SET NOCOUNT ON; DECLARE @dt DATETIME= GETDATE(); -- 阻塞时间 DECLARE @HtmlContent NVARCHAR(MAX); --邮件发送的阻塞日志（表格形式） IF OBJECT_ID('tempdb.dbo.#BlockLog') IS NOT NULL DROP TABLE #BlockLog; --将当前日志记录插入临时表 BEGIN SELECT wt.blocking_session_id AS BlockingSessesionId , sp.program_name AS ProgramName , COALESCE(sp.LOGINAME, sp.nt_username) AS HostName , ec1.client_net_address AS ClientIpAddress , db.name AS DatabaseName , wt.wait_type AS WaitType , ec1.connect_time AS BlockingStartTime , wt.WAIT_DURATION_MS / 1000 AS WaitDuration , ec1.session_id AS BlockedSessionId , h1.TEXT AS BlockedSQLText , h2.TEXT AS BlockingSQLText , @dt dt INTO #BlockLog FROM sys.dm_tran_locks AS tl INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id LEFT OUTER JOIN master.dbo.sysprocesses sp ON SP.spid = wt.blocking_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; --将临时表数据插入日志表 INSERT INTO etcp_alert.dbo.BlockLog ( BlockingSessesionId , ProgramName , HostName , ClientIpAddress , DatabaseName , WaitType , BlockingStartTime , WaitDuration , BlockedSessionId , BlockedSQLText , BlockingSQLText , dt ) SELECT BlockingSessesionId , ProgramName , HostName , ClientIpAddress , DatabaseName , WaitType , BlockingStartTime , WaitDuration , BlockedSessionId , BlockedSQLText , BlockingSQLText , dt FROM #BlockLog; END; --以html表格方式发送邮件，如果不发送邮件，则删除以下代码 BEGIN SET @HtmlContent = N'' + N'h2, body {font-family: Arial, verdana;} table{font-size:11px; border-collapse:collapse;} td{background-color:#F1F1F1; border:1px solid black; padding:3px;} th{background-color:#99CCFF;}' + N'' + N' BlockingSessesionId ProgramName HostName ClientIpAddress DatabaseName WaitType BlockingStartTime WaitDuration BlockedSessionId BlockedSQLText BlockingSQLText dt ' + CAST(( SELECT BlockingSessesionId AS TD , '' , ProgramName AS TD , '' , HostName AS TD , '' , ClientIpAddress AS TD , '' , DatabaseName AS TD , '' , WaitType AS TD , '' , BlockingStartTime AS TD , '' , WaitDuration AS TD , '' , BlockedSessionId AS TD , '' , BlockedSQLText AS TD , '' , BlockingSQLText AS TD , '' , dt AS Td , '' FROM #BlockLog FOR XML PATH('tr') , TYPE ) AS NVARCHAR(MAX)) + N''; IF @HtmlContent IS NOT NULL BEGIN DECLARE @ProfileName VARCHAR(100)= 'db_mail'; --邮箱公用账户名称 DECLARE @RecipientsLst VARCHAR(100)= '[email protected]'; --收件人，以";"分隔 DECLARE @subject VARCHAR(100)= '数据库阻塞警报'; --主题 EXEC msdb.dbo.sp_send_dbmail @profile_name = @ProfileName, @recipients = @RecipientsLst, @subject = @subject, @body = @HtmlContent, @body_format = 'HTML'; END; begin DROP TABLE #BlockLog; END; END;

注意，如果没有配置邮箱账号，需要配置邮箱功能，如下：

三、创建警报，当阻塞大于阈值时，触发上面作业

名称：可根据实际自行命名，这里我用数据库阻塞报警类型：选择"SQL Server性能条件警报"对象：SQLServer:General Statistics计数器：Processes blocked计数器满足以下条件时触发警报：高于值：2，根据系统具体定

在"响应"中配置，一定将执行作业指向上面创建的job

四、测试

为了测试方便，我将报警阈值调整为高于0个，即当1个阻塞发生时就会触发对应的job，还是采用之前的两个会话，查看报警。

邮箱收到报警：

结果表已经插入数据：

【本文地址】

公司简介

联系我们