mysql sqlserver 性能优化_SQLServer性能优化之---数据库级日记监控

4.6.6.SQLServer监控

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

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

应用：一般就是设置个定时任务，把耗时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 ='xxx@163.com', -- 发件人邮件地址

@display_name ='SQLServer2014_192.168.36.250', -- 发件人姓名

@MAILSERVER_NAME = 'smtp.163.com', -- 邮件服务器地址

@PORT =25, -- 邮件服务器端口

@USERNAME = 'xxx@163.com', -- 用户名

@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 = 'xxx@qq.com', --收件邮箱

@body_format = 'HTML', --内容格式

@subject = '文章标题', --文章标题

@body = '邮件内容

This is Test

...' --邮件内容

效果：

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.新建一个会话向导(熟悉后可以直接新建会话)

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

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

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

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

6.核心代码如下

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

8.SQLServer提供了查看方式

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

2.SQL的方式

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

下面就来个制定化监控：

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

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

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

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

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

4.发送监控提醒的邮件

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

6.来个测试

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

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 = '

数据库错误监控

set @body_html = @body_html + '

运行时间数据库发生错误的SQL语句消息用户名应用应用程序名'

-- 格式处理(没内容就空格填充)

select @body_html = @body_html + '

'

+ case (isnull(start_time, '')) when '' then ' ' else convert(varchar(20), start_time, 120) end + '

'

+ case (isnull(database_name, '')) when '' then ' ' else database_name end + '

'

+ case (isnull(sql_text, '')) when '' then ' ' else sql_text end + '

'

+ case (isnull(message, '')) when '' then ' ' else message end + '

'

+ case (isnull(username, '')) when '' then ' ' else username end + '

'

+ case (isnull(client_hostname, '')) when '' then ' ' else client_hostname end + '

'

+ case (isnull(client_app_name, '')) when '' then ' ' else client_app_name end + '

'

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+'

'

-- 发送警告邮件

exec msdb.dbo.sp_send_dbmail

@profile_name = 'SQLServer_DotNetCrazy', --配置名称

@recipients = 'xxxxx@qq.com', --收件邮箱

@body_format = 'HTML', --内容格式

@subject = '数据库监控通知', --文章标题

@body = @body_html --邮件内容

go

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

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

晚点在下面补上

原文出处：https://www.cnblogs.com/dotnetcrazy/p/11166516.html