sqlserver 通过作业警报监控堵塞

最新推荐文章于 2021-02-07 12:44:03 发布

羽之大公公

最新推荐文章于 2021-02-07 12:44:03 发布

阅读量735

点赞数

分类专栏： sqlserver

sqlserver 专栏收录该内容

39 篇文章 3 订阅

订阅专栏

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
		)

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

在新建作业步骤中，选择数据库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，根据系统具体定

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

四、测试

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

邮箱收到报警：

结果表已经插入数据：

羽之大公公

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
sqlserver 通过作业警报监控堵塞

1.创建阻塞日志表，用于记录阻塞情况2.新建作业，用于将阻塞情况记录到阻塞日志表中，并发送邮件（如果没有配置邮件，或者不需要发送邮件，可以忽略此步骤）3.创建警报，当阻塞大于阈值时，触发上面作业查询堵塞sql:WITH sess AS( SELECT DATEDIFF(S,er.start_time,GETDATE()) as duration,...
复制链接

扫一扫