公司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'<head>' + N'<style type="text/css">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;}</style>' + N'<table border="1">' + N'<tr> <th>BlockingSessesionId</th> <th>ProgramName</th> <th>HostName</th> <th>ClientIpAddress</th> <th>DatabaseName</th> <th>WaitType</th> <th>BlockingStartTime</th> <th>WaitDuration</th> <th>BlockedSessionId</th> <th>BlockedSQLText</th> <th>BlockingSQLText</th> <th>dt</th> </tr>' + 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'</table>'; IF @HtmlContent IS NOT NULL BEGIN DECLARE @ProfileName VARCHAR(100)= 'db_mail'; --邮箱公用账户名称 DECLARE @RecipientsLst VARCHAR(100)= '123@123.cn'; --收件人,以";"分隔 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,还是采用之前的两个会话,查看报警。
邮箱收到报警:
结果表已经插入数据: