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

　　公司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{ 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;

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