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

原文地址:https://www.bbsmax.com/A/QV5ZxZZezy/

公司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 , )
NOT NULL
PRIMARY KEY ,
[BlockingSessesionId] [smallint] NULL ,
) NULL ,
) NULL ,
) NULL ,
[DatabaseName] [sysname] NOT NULL ,
) 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 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{ 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
)= 'db_mail'; --邮箱公用账户名称
)= '123@123.cn'; --收件人，以";"分隔
)= '数据库阻塞警报'; --主题
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;