sqlserver 通过作业警报监控堵塞

Love The Way You Lie 2021-12-12 06:01 262阅读 0赞

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

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

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

查询堵塞sql:

WITH sess AS
    (
        SELECT         DATEDIFF(S,er.start_time,GETDATE()) as duration,
            es.session_id,
            database_name = DB_NAME(er.database_id),
            er.cpu_time,
            er.reads,
            er.writes,
            er.logical_reads,
            login_name,
    
            er.status,
            blocking_session_id,
            wait_type,
            wait_resource,
            wait_time,
            individual_query = SUBSTRING (qt.text, (er.statement_start_offset/2)+1, ((CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2)+1),
            parent_query = qt.text,
            program_name,
            host_name,
            nt_domain,
            start_time,
            (SELECT query_plan FROM sys.dm_exec_query_plan(er.plan_handle)) AS query_plan
        FROM
            sys.dm_exec_requests er
            INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id
            CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
        WHERE
            es.session_id > 50
            AND es.session_Id NOT IN (@@SPID)
    		AND qt.text <> 'sp_server_diagnostics'
    )
    SELECT
        *
    FROM
        sess WHERE individual_query NOT LIKE 'waitfor%'
    UNION ALL SELECT  DATEDIFF(S,es.last_request_start_time,GETDATE()) as duration,
        es.session_id,
        database_name = '',
        0,
        0,
        0,
        0,
        login_name,
        es.status,
        0,
        '',
        '',
        '',
        qt.text,
        parent_query = qt.text,
        program_name,
        host_name,
        nt_domain,
        es.last_request_start_time,
        NULL AS query_plan
    FROM
        sys.dm_exec_sessions es
        INNER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
        CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle)as qt
    WHERE
        ec.most_recent_session_id IN
        (
            SELECT blocking_session_id FROM sess WHERE blocking_session_id NOT IN(SELECT DISTINCT session_id FROM sess)
        ) AND  qt.text NOT in ('waitfor delay @waittime    ','sp_server_diagnostics')
    	
    ORDER BY 1, 2

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

CREATE TABLE [test].[dbo].[BlockLog]
        (
           duration [bigint] ,
           session_id [smallint] ,
            database_name [sysname]  NULL,
            cpu_time INT  null,
            reads [BIGINT] null,
            writes [BIGINT] null,
            logical_reads [BIGINT] null,
            login_name [nvarchar](80)  NULL,
            status [nvarchar](20)  NULL,
            blocking_session_id [smallint] NULL,
            wait_type [nvarchar](60) NULL,
            wait_resource [nvarchar](60)  NULL,
            wait_time INT  null,
            individual_query  [nvarchar](MAX) NULL,
            parent_query  [nvarchar](MAX) NULL,
            program_name [nchar](128) NULL,
            host_name [nchar](128) NULL,
            nt_domain [nvarchar](60) NULL,
            start_time [datetime] NOT NULL,
    		[dt] [datetime] NOT NULL
    		)

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

![797406-20150901180016731-1868451367.png][]

![797406-20150901180439060-113232035.png][]

在新建作业步骤中，选择数据库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  
        WITH sess AS
    (
        SELECT         DATEDIFF(S,er.start_time,GETDATE()) as duration,
            es.session_id,
            database_name = DB_NAME(er.database_id),
            er.cpu_time,
            er.reads,
            er.writes,
            er.logical_reads,
            login_name,
    
            er.status,
            blocking_session_id,
            wait_type,
            wait_resource,
            wait_time,
            individual_query = SUBSTRING (qt.text, (er.statement_start_offset/2)+1, ((CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2)+1),
            parent_query = qt.text,
            program_name,
            host_name,
            nt_domain,
            start_time,
           @dt dt
        FROM
            sys.dm_exec_requests er
            INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id
            CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
        WHERE
            es.session_id > 50
            AND es.session_Id NOT IN (@@SPID)
    		AND qt.text <> 'sp_server_diagnostics'
    )
    SELECT
        *
    	INTO #BlockLog
    FROM
        sess WHERE individual_query NOT LIKE 'waitfor%'
    UNION ALL SELECT  DATEDIFF(S,es.last_request_start_time,GETDATE()) as duration,
        es.session_id,
        database_name = '',
        0,
        0,
        0,
        0,
        login_name,
        es.status,
        0,
        '',
        '',
        '',
        qt.text,
        parent_query = qt.text,
        program_name,
        host_name,
        nt_domain,
        es.last_request_start_time,
    	@dt dt
    FROM
        sys.dm_exec_sessions es
        INNER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
        CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle)as qt
    WHERE
        ec.most_recent_session_id IN
        (
            SELECT blocking_session_id FROM sess WHERE blocking_session_id NOT IN(SELECT DISTINCT session_id FROM sess)
        ) AND  qt.text NOT in ('waitfor delay @waittime    ','sp_server_diagnostics')
    	
    ORDER BY 1, 2
    
    --将临时表数据插入日志表
        INSERT  INTO  test.dbo.BlockLog
                (  duration ,
           session_id ,
            database_name ,
            cpu_time ,
            reads ,
            writes ,
            logical_reads ,
            login_name ,
            status ,
            blocking_session_id ,
            wait_type ,
            wait_resource ,
            wait_time ,
            individual_query ,
            parent_query  ,
            program_name ,
            host_name ,
            nt_domain ,
            start_time ,
    		dt
                )
                SELECT   duration,
           session_id,
            database_name,
            cpu_time,
            reads,
            writes,
            logical_reads,
            login_name,
            status,
            blocking_session_id,
            wait_type,
            wait_resource,
            wait_time,
            individual_query ,
            parent_query ,
            program_name,
            host_name,
            nt_domain,
            start_time,
    		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>duration </th>
            <th>session_id </th>
            <th>database_name </th>
            <th>cpu_time </th>
            <th>reads </th>
            <th>writes </th>
            <th>logical_reads </th>
            <th>login_name </th>
            <th>status </th>
            <th>blocking_session_id </th>
            <th>wait_type </th>
            <th>wait_resource </th>
            <th>wait_time </th>
            <th>individual_query </th>
            <th>parent_query  </th>
            <th>program_name </th>
            <th>host_name </th>
            <th>nt_domain </th>
            <th>start_time </th>
    		<th>dt</th>
                    </tr>' + CAST(( SELECT          duration as TD,
    		'' ,
            session_id as TD,
    		'' ,
            database_name as TD,
    		'' ,
            cpu_time as TD,
    		'' ,
            reads as TD,
    		'' ,
            writes as TD,
    		'' ,
            logical_reads as TD,
    		'' ,
            login_name as TD,
    		'' ,
            status as TD,
    		'' ,
            blocking_session_id as TD,
    		'' ,
            wait_type as TD,
    		'' ,
            wait_resource as TD,
    		'' ,
            wait_time as TD,
    		'' ,
            individual_query as TD,
    		'' ,
            parent_query  as TD,
    		'' ,
            program_name as TD,
    		'' ,
            host_name as TD,
    		'' ,
            nt_domain as TD,
    		'' ,
            start_time 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)= 'test'; --邮箱公用账户名称
                DECLARE @RecipientsLst VARCHAR(100)= '11111111@qq.com'  --收件人，以";"分隔
                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;

注意:邮箱公用账户名称要和sqlserver邮箱配置文件名称一致，具体配置方法在我之前的转载博客中。

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

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

![797406-20150901182817935-631057925.png][]

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

![797406-20150901183258560-1158559786.png][]

四、测试

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

邮箱收到报警：

![797406-20150901185327481-1274094222.png][]

结果表已经插入数据：

![797406-20150901185436419-104859898.png][]

[797406-20150901180016731-1868451367.png]: /images/20211211/3e30f7dc7ecf4b54a4b0ab0094b799b7.png
[797406-20150901180439060-113232035.png]: /images/20211211/ed65e09632394e559b10fb4b1693009e.png
[797406-20150901182817935-631057925.png]: /images/20211211/f0cca8e679cd47b2a51d2ae7ee17e4ed.png
[797406-20150901183258560-1158559786.png]: /images/20211211/7a17d8f1b17a4a04babb7bc46d599101.png
[797406-20150901185327481-1274094222.png]: /images/20211211/3430efc3597149e7a3b8edc313e57b2f.png
[797406-20150901185436419-104859898.png]: /images/20211211/8e39c8871bb649bf84224c92518e05b6.png