• 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{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,还是采用之前的两个会话,查看报警。

    邮箱收到报警:

    结果表已经插入数据:

  • 相关阅读:
    海驾学车过程全揭秘——第六篇:辛苦的学车全过程
    择偶
    海驾学车过程全揭秘——第八篇:科目二集训及考试
    海驾学车过程全揭秘——第四篇:正式练车第一段
    痛苦的相对论
    不犹豫不后悔
    海驾学车过程全揭秘——第十篇:领驾照、办牡丹卡、陪练
    海驾学车过程全揭秘——第五篇:网上约车(电话约车)
    海驾学车过程全揭秘——第一篇:总述
    海驾学车过程全揭秘——第九篇:科目三集训及考试
  • 原文地址:https://www.cnblogs.com/datazhang/p/4773823.html
Copyright © 2020-2023  润新知