• “两学一做”在山西——黄河新闻网 2019-07-13
  • 内地生报读香港高校本科人数持续下跌 2019-07-13
  • 【学习时刻】人民大学王义桅:金砖合作的“自信”与“自觉” 2019-07-12
  • 女子请“私家侦探”被骗3万 警方循线捣毁诈骗团伙 2019-07-11
  • 【学习时刻】北交大马院院长韩振峰:高校思想政治工作必须牢牢把握三大根本问题 2019-07-11
  • 全国“非遗”保护工作先进名单公布 2019-07-01
  • 紫光阁中共中央国家机关工作委员会 2019-06-25
  • 杭州控烟令修改草案拟允许室内设吸烟区,控烟专家:跌破眼镜 2019-06-25
  • 挪用近30万报纸征订款赌博 河南一报社聘用制干部获刑 2019-06-23
  • 2016年,有1145家上市公司大小非减持了3600亿元,还有210名上市公司高管减持了1400亿元。IPO已成了造就成千上万个十亿、百亿富豪的捷径, 2019-06-21
  • 专家“把脉”中国电影市场:提升品质方能逆袭 2019-06-21
  • “善款资助副局长儿子留学”真相须尽快落地 2019-06-19
  • 21岁女护士失联2天后确认遇害 嫌疑人为其前男友 2019-06-19
  • 中国地质公园名录旅行地中国国家地理网 2019-06-13
  • 玄关运用有四大原则 用的好才能财旺挡煞聚财 ——凤凰网房产 2019-06-10
  • SQLServer性能优化之---数据库级日记监控

    广东十一选5一定牛 www.aavbg.com 上节回顾://www.aavbg.com/dotnetcrazy/p/11029323.html

    4.6.6.SQLServer监控

    脚本示意:https://github.com/lotapp/BaseCode/tree/master/database/SQL/SQLServer

    PS:这些脚本都是我以前用SQLServer手写的,参考即可(现在用MySQL,下次也整理一下)

    之前写SQLServer监控系列文章因为换环境断篇了,只是简单演示了下基础功能,现在准备写MySQL监控相关内容了,于是补了下:

    SQLServer性能优化之---数据库级日记监控//www.aavbg.com/dunitian/p/6022967.html

    在说监控前你可以先看下数据库发邮件//www.aavbg.com/dunitian/p/6022826.html

    应用:一般就是设置个定时任务,把耗时SQL信息或者错误信息通过邮件的方式及时预警

    好处就太多了,eg:客户出错如果是数据库层面,那瞬间就可以场景重放(PS:等客户找会降低业绩)

    以往都是程序的try+catch来捕获错误,但数据库定时任务之类的出错程序是捕获不到的,所以就需要数据库层面的监控了

    PS:开发的时候通过SQLServer Profiler来监控

    先说说本质吧:SQLServer2012的XEVENT机制已经完善,eg:常用的扩展事件error_reported就可以在错误的时候通过邮件来通知管理员了

    PS:扩展事件性能较高,而且比较轻量级

    PS:SQLServer的监控大体思路三步走:发邮件,事件监控,定时执行

    4.6.6.1 发送邮件

    这个之前讲过,这边就再说下SQL的方式:

    1.配置发件人邮箱

    这个配置一次即可,以后使用就可以直接通过配置名发邮件

    --开启发邮件功能
    exec sp_configure 'show advanced options',1
    reconfigure with override
    go
    exec sp_configure 'database mail xps',1
    reconfigure with override
    go
    
    --创建邮件帐户信息
    exec msdb.dbo.sysmail_add_account_sp
      @account_name ='dunitian',                     -- 邮件帐户名称
      @email_address ='[email protected]',                 -- 发件人邮件地址
      @display_name ='SQLServer2014_192.168.36.250', -- 发件人姓名
      @MAILSERVER_NAME = 'smtp.163.com',             -- 邮件服务器地址
      @PORT =25,                                     -- 邮件服务器端口
      @USERNAME = '[email protected]',                     -- 用户名
      @PASSWORD = '邮件密码或授权码'                 -- 密码(授权码)
    GO
    
    --数据库配置文件
    exec msdb.dbo.sysmail_add_profile_sp
      @profile_name = 'SQLServer_DotNetCrazy',       -- 配置名称
      @description = '数据库邮件配置文件'            -- 配置描述
    go
    
    --用户和邮件配置文件相关联
    exec msdb.dbo.sysmail_add_profileaccount_sp
      @profile_name = 'SQLServer_DotNetCrazy',     -- 配置名称
      @account_name = 'dunitian',                  -- 邮件帐户名称
      @sequence_number = 1                         -- account 在 profile 中顺序(默认是1)
    go
    
    2.发生预警邮箱

    同样我只演示SQL的方式,图形化的方式可以看我以前写的文章:

    -- 发邮件测试
    exec msdb.dbo.sp_send_dbmail
    @profile_name = 'SQLServer_DotNetCrazy',         --配置名称
    @recipients = '[email protected]',                   --收件邮箱
    @body_format = 'HTML',                         --内容格式
    @subject = '文章标题',                         --文章标题
    @body = '邮件内容<br/><h2>This is Test</h2>...' --邮件内容

    效果:

    06-10/1.mail.png

    3.邮件查询相关

    主要用途其实就是出错排查:

    -- 查询相关
    select * from msdb.dbo.sysmail_allitems     --查看所有邮件消息
    select * from msdb.dbo.sysmail_mailitems    --查看邮件消息(更多列)
    
    select * from msdb.dbo.sysmail_sentitems    --查看已发送的消息
    select * from msdb.dbo.sysmail_faileditems  --失败状态的消息
    select * from msdb.dbo.sysmail_unsentitems  --看未发送的消息
    
    select * from msdb.dbo.sysmail_event_log    --查看记录日记

    4.6.6.2.监控实现

    会了邮件的发送,那下面就是监控了

    1.图形化演示

    不推荐使用图形化的方式,但可以来理解扩展事件的监控

    1.新建一个会话向导(熟悉后可以直接新建会话)

    1.新建会话向导.png

    1.新建会话向导2.png

    2.设置需要捕获的扩展事件

    2.设置需要捕获的扩展事件.png

    3.这边捕获的全局字段和左边SQL是一样的(截图全太麻烦了,所以偷个懒,后面会说怎么生成左边的核心SQL)

    3.捕获的全局字段.png

    4.自己根据服务器性能设置一个合理的值(IO、内存、CPU)

    4.设置.png

    5.生成核心SQL(我们图形化的目的就是生成核心SQL,后面可以根据这个SQL自己扩展)

    5.生成核心SQL.png

    6.核心代码如下

    6.核心代码.png

    7.启动会话后一个简单的扩展事件监控就有了

    7.启动会话.png

    8.SQLServer提供了查看方式

    8.查看.png

    9.日志可以自己查下xxx\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log

    9.日志.png


    2.SQL的方式

    上面只是过家家,主要目的就是让大家知道核心SQL是怎么来的,凭什么这么写

    下面就来个制定化监控:

    先截图演示下各个核心点,然后贴一个我封装的存储过程附件

    1.扩展事件相关的核心代码

    1.扩展事件相关的核心代码.png

    2.内存中数据存储到临时表

    2.内存中数据存储到临时表.png

    3.临时表中的数据存储到自己建立的表中

    我抛一个课后小问给大家:为什么先存储在临时表中?(提示:效率)

    3.临时表中的数据存储到自己建立的表中.png

    4.发送监控提醒的邮件

    4.发送监控提醒的邮件.png

    5.看看数据库层面多了什么:

    5.看看数据库层面.png

    6.来个测试

    6.测试.png

    7.效果(可以自己美化)

    7.效果.png

    SQL附录
    -- 切换到需要监控的数据库
    USE [dotnetcrazy]
    GO
    
    --收集服务器上逻辑错误的信息
    SET QUOTED_IDENTIFIER ON
    SET ANSI_NULLS ON
    GO
    
    -- 自定义的错误信息表
    IF OBJECT_ID('log_error_message') IS NULL
    BEGIN
        CREATE TABLE [dbo].[log_error_message]
        (
        [login_message_id] [uniqueidentifier] NULL CONSTRAINT [DF__PerfLogic__Login__7ACA4E21] DEFAULT (newid()),
        [start_time] [datetime] NULL,
        [database_name] [nvarchar] (128) COLLATE Chinese_PRC_CI_AS NULL,
        [message] [nvarchar] (max) COLLATE Chinese_PRC_CI_AS NULL,
        [sql_text] [nvarchar] (max) COLLATE Chinese_PRC_CI_AS NULL,
        [alltext] [nvarchar] (max) COLLATE Chinese_PRC_CI_AS NULL,
        -- [worker_address] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NULL,
        [username] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NULL,
        [client_hostname] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NULL,
        [client_app_name] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NULL
        ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    END
    GO
    
    -- 创建存储过程
    CREATE PROCEDURE [dbo].[event_error_monitor]
    AS
        IF NOT EXISTS( SELECT 1 FROM sys.dm_xe_sessions dxs(NOLOCK) WHERE name = 'event_error_monitor') -- 不存在就创建EVENT
            -- 创建扩展事件,并把数据放入内存中
            BEGIN
                CREATE EVENT session event_error_monitor on server
                ADD EVENT sqlserver.error_reported -- error_reported扩展事件
                (
                ACTION -- 返回结果
                (
                sqlserver.session_id, -- 会话id
                sqlserver.plan_handle, -- 计划句柄,可用于检索图形计划
                sqlserver.tsql_stack, -- T-SQ堆栈信息
                package0.callstack, -- 当前调用堆栈
                sqlserver.sql_text, -- 遇到错误的SQL查询
                sqlserver.username, -- 用户名
                sqlserver.client_app_name, -- 客户端应用程序名称
                sqlserver.client_hostname, -- 客户端主机名
                -- sqlos.worker_address, -- 当前任务执行时间
                sqlserver.database_name -- 当前数据库名称
                )
                WHERE severity >= 11 AND Severity <=16 -- 指定用户级错误
                )
                ADD TARGET package0.ring_buffer -- 临时放入内存中
                WITH (max_dispatch_latency=1seconds)
    
                -- 启动监控事件
                ALTER EVENT SESSION event_error_monitor on server state = START
            END
        ELSE
            -- 存储过程已经存在就把数据插入表中
            BEGIN
                -- 将内存中已经收集到的错误信息转存到临时表中(方便处理)
                SELECT
                    DATEADD(hh,
                            DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),
                            n.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp],
                    n.value('(event/action[@name="database_name"]/value)[1]', 'nvarchar(128)') AS [database_name],
                    n.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS [sql_text],
                    n.value('(event/data[@name="message"]/value)[1]', 'nvarchar(max)') AS [message],
                    n.value('(event/action[@name="username"]/value)[1]', 'nvarchar(max)') AS [username],
                    n.value('(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(max)') AS [client_hostname],
                    n.value('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(max)') AS [client_app_name],
                    n.value('(event/action[@name="tsql_stack"]/value/frames/frame/@handle)[1]', 'varchar(max)') AS [tsql_stack],
                    n.value('(event/action[@name="tsql_stack"]/value/frames/frame/@offsetStart)[1]', 'int') AS [statement_start_offset],
                    n.value('(event/action[@name="tsql_stack"]/value/frames/frame/@offsetEnd)[1]', 'int') AS [statement_end_offset]
                into #error_monitor -- 临时表
                FROM
                (    SELECT td.query('.') as n
                    FROM
                    (
                        SELECT CAST(target_data AS XML) as target_data
                        FROM sys.dm_xe_sessions AS s
                        JOIN sys.dm_xe_session_targets AS t
                            ON t.event_session_address = s.address
                        WHERE s.name = 'event_error_monitor'
                        --AND t.target_name = 'ring_buffer'
                    ) AS sub
                    CROSS APPLY target_data.nodes('RingBufferTarget/event') AS q(td)
                ) as TAB
    
                -- 把数据存储到自己新建的表中(有SQL语句的直接插入到表中)
                INSERT INTO log_error_message(start_time,database_name,message,sql_text,alltext,username,client_hostname,client_app_name)
                SELECT TIMESTAMP,database_name,[message],sql_text,'',username,client_hostname,client_app_name
                FROM #error_monitor as a
                WHERE a.sql_text != '' --AND client_app_name !='Microsoft SQL Server Management Studio - 查询'
                AND a.MESSAGE NOT LIKE '找不到会话句柄%' AND a.MESSAGE NOT LIKE '%SqlQueryNotification%' --排除server broker
                AND a.MESSAGE NOT LIKE '远程服务已删除%'
    
                -- 插入应用执行信息(没有SQL的语句通过句柄查询下SQL)
                INSERT INTO log_error_message(start_time,database_name,message,sql_text,alltext,username,client_hostname,client_app_name)
                SELECT TIMESTAMP,database_name,[message],
                SUBSTRING(qt.text,a.statement_start_offset/2+1,
                            (case when a.statement_end_offset = -1
                            then DATALENGTH(qt.text)
                            else a.statement_end_offset end -a.statement_start_offset)/2 + 1) sql_text,qt.text alltext,
                username,client_hostname,client_app_name
                FROM #error_monitor as a
                CROSS APPLY sys.dm_exec_sql_text(CONVERT(VARBINARY(max),a.tsql_stack,1)) qt -- 通过句柄查询具体的SQL语句
                WHERE a.sql_text IS NULL AND tsql_stack != '' --AND client_app_name = '.Net SqlClient Data Provider'
    
                DROP TABLE #error_monitor -- 删除临时表
    
                --重启清空
                ALTER EVENT SESSION event_error_monitor ON SERVER STATE = STOP
                ALTER EVENT SESSION event_error_monitor on server state = START
            END
    
        -- 美化版预警邮箱
        DECLARE @body_html VARCHAR(max)
        set @body_html = '<table style="width:100%" cellspacing="0"><tr><td colspan="6" align="center" style="font-weight:bold;color:red">数据库错误监控</td></tr>'
        set @body_html = @body_html + '<tr style="text-align: left;"><th>运行时间</th><th>数据库</th><th>发生错误的SQL语句</th><th>消息</th><th>用户名</th><th>应用</th><th>应用程序名</th></tr>'
        -- 格式处理(没内容就空格填充)
        select @body_html = @body_html + '<tr><td>'
            + case (isnull(start_time, '')) when '' then '&nbsp;' else convert(varchar(20), start_time, 120) end + '</td><td>'
            + case (isnull(database_name, '')) when '' then '&nbsp;' else database_name end + '</td><td>'
            + case (isnull(sql_text, '')) when '' then '&nbsp;' else sql_text end + '</td><td>'
            + case (isnull(message, '')) when '' then '&nbsp;' else message end + '</td><td>'
            + case (isnull(username, '')) when '' then '&nbsp;' else username end + '</td><td>'
            + case (isnull(client_hostname, '')) when '' then '&nbsp;' else client_hostname end + '</td><td>'
            + case (isnull(client_app_name, '')) when '' then '&nbsp;' else client_app_name end + '</td></tr>'
        from (
                 select start_time, database_name,sql_text, message, username, client_hostname, client_app_name
                 from [dbo].[log_error_message]
                 where start_time >= dateadd(hh,-2,getdate()) -- 当前时间 - 定时任务的时间间隔(2h)
                   and client_app_name != 'Microsoft SQL Server Management Studio - 查询' -- and client_hostname in('')
             ) as temp_message
        set @body_html= @body_html+'</table>'
    
        -- 发送警告邮件
        exec msdb.dbo.sp_send_dbmail
        @profile_name = 'SQLServer_DotNetCrazy',         --配置名称
        @recipients = '[email protected]',                  --收件邮箱
        @body_format = 'HTML',                           --内容格式
        @subject = '数据库监控通知',                       --文章标题
        @body = @body_html --邮件内容
    go
    

    下节预估:定时任务、完整版监控

    PS:估计先得更八字的文章(拖太久)然后更完SQLServer更MySQL,等MySQL监控更完会说下备份与恢复,接着我们开架构篇(MyCat系列先不讲放在Redis和爬虫系列的后面)

    晚点在下面补上

    posted @ 2019-07-10 21:07 鲲逸鹏 阅读(...) 评论(...) 编辑 收藏
    广东十一选5一定牛
  • “两学一做”在山西——黄河新闻网 2019-07-13
  • 内地生报读香港高校本科人数持续下跌 2019-07-13
  • 【学习时刻】人民大学王义桅:金砖合作的“自信”与“自觉” 2019-07-12
  • 女子请“私家侦探”被骗3万 警方循线捣毁诈骗团伙 2019-07-11
  • 【学习时刻】北交大马院院长韩振峰:高校思想政治工作必须牢牢把握三大根本问题 2019-07-11
  • 全国“非遗”保护工作先进名单公布 2019-07-01
  • 紫光阁中共中央国家机关工作委员会 2019-06-25
  • 杭州控烟令修改草案拟允许室内设吸烟区,控烟专家:跌破眼镜 2019-06-25
  • 挪用近30万报纸征订款赌博 河南一报社聘用制干部获刑 2019-06-23
  • 2016年,有1145家上市公司大小非减持了3600亿元,还有210名上市公司高管减持了1400亿元。IPO已成了造就成千上万个十亿、百亿富豪的捷径, 2019-06-21
  • 专家“把脉”中国电影市场:提升品质方能逆袭 2019-06-21
  • “善款资助副局长儿子留学”真相须尽快落地 2019-06-19
  • 21岁女护士失联2天后确认遇害 嫌疑人为其前男友 2019-06-19
  • 中国地质公园名录旅行地中国国家地理网 2019-06-13
  • 玄关运用有四大原则 用的好才能财旺挡煞聚财 ——凤凰网房产 2019-06-10
  • 重庆时时彩辅助软件 广西淘宝快3开奖 广东快乐十分任二追号 安徽快3开奖结果今天62 彩票软件开发教学课程 159游戏娱乐平台下载 六合彩直播 北京十一选五前三直走势 007皇家赌场高清 18选7要多少钱 香港2019码报资料大全 沈阳1464万大奖弃奖 ag真人是骗局吗 北京赛车h 幸运农场龙虎怎么分别