SQL Server 监控

转载 2011年01月19日 21:57:00

http://www.cnblogs.com/bhtfg538/archive/2011/01/19/1939190.html

 

作为DBA,不仅仅是帮助开发人员写优秀的查询脚本、高效的数据存储架构。其实更多时候都是在运维,大部分工作时间都不算饱和的,可是为什么有些DBA如此忙碌、狼狈不抗甚至等到客户或者乙方公司抱怨呢,那很多原因就是对SQL Server 的基础监控架构不熟悉,不够了解,不能自动化安排监控任务。

    获取通知:SQL Server为我们提供的诸如SQL Mail,Database Mail 邮件方式的信息发送方式,因此我们利用邮件来进行最基础的获取通知异常等情况。我在之前的文章提过Databasemail,大家可以做为参考

MSSQL数据库邮件系列一(环境搭建)

MSSQL数据库邮件系列二(SSMS和TSQL)

    我提到了基本的邮件配置,但是也许我们的内网数据库是没有不能访问Internet的,如何发送到我们的监控邮箱呢。有朋友可能尝试会插入一个Mail表,然后在另外一台能否访问Internet的内网机器读取该表来发送邮件,其实我们可以通过简单的SMTP中继来实现,我们知道的SMTP其实是个推协议,一般是发送到提供商的STMP服务器,其实我们也能通过发送到自己的smtp服务器然后转发到指定DNS响应的SMTP服务器。

   我测试是Windows Server 2008的机器,我们来安装SMTP服务器。首先打开

1.服务器管理器 –> 功能 –> 添加功能 –> SMTP 服务器。

2.安装成功以后,在管理工具 –> IIS (6.0) 管理器打开设置。

sqlmonitor_1

3.首先点击 域 –> 新建域 –> sqlmonitor_2(我新建了一个SMTP.QQ.COM的域,所以我要转发邮件到QQ邮箱)。

我们看到QQ提供的SMTP.QQ.COM使用的标准的25端口。所以打开WF.MSC 去添加25端口的出站规则,同时打开入站规则的25(让DB服务器能够中继到该SMTP服务器),不关闭选项卡。

4.在“出站安全”中->选择“基本身份验证”->添加你的邮箱登录帐号 –>(TSL加密根据你SMTP服务器的规则)来设置。

5.选中“SMTP Virtual Server #1” –> 右键属性 –> 切换到 “访问”选项卡 –>在“中继限制”中单击“中继” 添加你对应机器的访问列表。

6.最后一步,把你的DatabaseMail帐号设置成 匿名身份验证,SMTP服务器选择你的内网SMTP服务器地址,端口默认25,其他都不变动。

    如果发送测试邮件通过说明没问题了,搞不定可以搜搜资料吧,或者直接提问。那么我也就解决了第一个问题“我们如何获取通知”,采用最基本的邮件方式。

    详细的监控信息:数据库避免不了一些异常状态,比如错误的脚本导致的异常,空间不够,磁盘挂了,复制失败了等。这里我先提提SQL Server事件。这个意味着SQL Server发生特定错误产生的事件,每个事件都有对应的数据库、严重级别、错误号、错误文本。

打开SSMS->在SQL Server 代理 –>的警告中可以创建警告。一般来说我们关注大于等于13严重级别的事件。为乙方公司提供服务时快速的部署也很重要,因此我整理了一些脚本:

USE [msdb]
GO
 
/****** Object:  Alert [SQL_EVENT_13]    Script Date: 12/08/2010 14:44:53 ******/
EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_13', 
        @message_id=0, 
        @severity=13, 
        @enabled=1, 
        @delay_between_responses=1800, 
        @include_event_description_in=1, 
        @category_name=N'[Uncategorized]', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO
 
USE [msdb]
GO
 
/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/
EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_14', 
        @message_id=0, 
        @severity=14, 
        @enabled=1, 
        @delay_between_responses=1800, 
        @include_event_description_in=1, 
        @category_name=N'[Uncategorized]', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO
 
/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/
EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_15', 
        @message_id=0, 
        @severity=15, 
        @enabled=1, 
        @delay_between_responses=1800, 
        @include_event_description_in=1, 
        @category_name=N'[Uncategorized]', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO
 
/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/
EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_16', 
        @message_id=0, 
        @severity=16, 
        @enabled=1, 
        @delay_between_responses=1800, 
        @include_event_description_in=1, 
        @category_name=N'[Uncategorized]', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO
 
/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/
EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_17', 
        @message_id=0, 
        @severity=17, 
        @enabled=1, 
        @delay_between_responses=1800, 
        @include_event_description_in=1, 
        @category_name=N'[Uncategorized]', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO
 
/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/
EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_18', 
        @message_id=0, 
        @severity=18, 
        @enabled=1, 
        @delay_between_responses=1800, 
        @include_event_description_in=1, 
        @category_name=N'[Uncategorized]', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO
 
/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/
EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_19', 
        @message_id=0, 
        @severity=19, 
        @enabled=1, 
        @delay_between_responses=1800, 
        @include_event_description_in=1, 
        @category_name=N'[Uncategorized]', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO
 
/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/
EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_20', 
        @message_id=0, 
        @severity=20, 
        @enabled=1, 
        @delay_between_responses=1800, 
        @include_event_description_in=1, 
        @category_name=N'[Uncategorized]', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO
 
/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/
EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_21', 
        @message_id=0, 
        @severity=21, 
        @enabled=1, 
        @delay_between_responses=1800, 
        @include_event_description_in=1, 
        @category_name=N'[Uncategorized]', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO
 
/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/
EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_22', 
        @message_id=0, 
        @severity=22, 
        @enabled=1, 
        @delay_between_responses=1800, 
        @include_event_description_in=1, 
        @category_name=N'[Uncategorized]', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO
 
/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/
EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_23', 
        @message_id=0, 
        @severity=23, 
        @enabled=1, 
        @delay_between_responses=1800, 
        @include_event_description_in=1, 
        @category_name=N'[Uncategorized]', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO
 
/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/
EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_24', 
        @message_id=0, 
        @severity=24, 
        @enabled=1, 
        @delay_between_responses=1800, 
        @include_event_description_in=1, 
        @category_name=N'[Uncategorized]', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO
 
/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/
EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_25', 
        @message_id=0, 
        @severity=25, 
        @enabled=1, 
        @delay_between_responses=1800, 
        @include_event_description_in=1, 
        @category_name=N'[Uncategorized]', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO

 

上面提供了 13-25严重级别的事件。有了事件,必然的,需要一个操作员或者说是监控人员来对获取事件来转发邮件,我们可以看到SQL Server 代理中 有 操作员的选项卡,可以创建操作员:

 

USE [msdb]
GO
 
/****** Object:  Operator [Terry Mao]    Script Date: 01/19/2011 11:37:34 ******/
EXEC msdb.dbo.sp_add_operator @name=N'Terry Mao', 
        @enabled=1, 
        @weekday_pager_start_time=90000, 
        @weekday_pager_end_time=180000, 
        @saturday_pager_start_time=90000, 
        @saturday_pager_end_time=180000, 
        @sunday_pager_start_time=90000, 
        @sunday_pager_end_time=180000, 
        @pager_days=0, 
        @email_address=N'10000@qq.com', 
        @category_name=N'[Uncategorized]'
GO

我添加了一个 “Terry Mao” 的操作人员,对应邮件地址为10000的邮件地址,然后我们可能需要为所有的事件添加订阅人员, 也就是Terry Mao。

在视图中,可以查询到所有创建的警报:

SELECT * FROM msdb.dbo.sysalerts;

 

当然不可能一个去添加订阅事件,可以创建类似功能的sp:

CREATE PROC dbo.sp_add_all_notification
    @Operator sysname
AS
BEGIN
DECLARE @Name sysname,@OperatorID int;
SELECT @OperatorID = id FROM msdb.dbo.sysoperators WHERE name = @Operator;
IF @OperatorID IS NULL
    RETURN -1;
    
DECLARE Cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR SELECT [name] FROM 
msdb.dbo.sysalerts alters WHERE NOT EXISTS
(SELECT 1 FROM msdb.dbo.sysnotifications notify 
WHERE notify.operator_id = @OperatorID AND notify.alert_id = alters.id);
OPEN Cur;

FETCH NEXT FROM Cur INTO @Name;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC msdb.dbo.sp_add_notification @alert_name = @Name,
@operator_name = @Operator,
@notification_method = 1;
    FETCH NEXT FROM Cur INTO @Name;
END

CLOSE Cur;
DEALLOCATE Cur;
RETURN 0;
END

 

这样就会把所有的事件全部给监控起来,其实我就解决了过滤的监控列表,但是我们可能会对运行中的SQL Server 比如长时间执行的语句,存储过程进行监控来调优。比如,我们给另外一家公司做DBA顾问,就立马需要获取有问题的查询,这个我会在接下来的文章详细说明。其中包括Profile的监控,SQL Server WMI的监控等。

监控 SQL Server 的运行状况

  • 2012年11月16日 10:35
  • 46KB
  • 下载

SQL Server性能监控

对象 指标名称 指标定义 指标 范围 指标单位 解决方案 SQL Server:Access Methods (SQL Server访问方法) Full Scans/s...

监控SQL Server 2005

  • 2007年08月11日 00:47
  • 189KB
  • 下载

监控 SQL Server 2005 的运行状况

Microsoft SQL Server 2005 提供了一些工具来监控数据库。方法之一是动态管理视图。动态管理视图 (DMV) 和动态管理函数 (DMF) 返回的服务器状态信息可用于监控服务器实例的...
  • gzh0222
  • gzh0222
  • 2011年10月17日 09:48
  • 922

关于SQL Server 监控与优化

最近有个培训,需要讲解SQL Server优化,做些储备。   首先做几个名词的说明: 执行计划(Plan):sql 编译后的代码段,并不是二进制代码,而是针对SQL的执行代码,叫做plan。一...

SQL SERVER 资源性能监控-资源监视器

资源监视器提供一个快速、实时及图形化界面来显示包括CPU、内存、I/O和网络使用情况的信息,可以监视和检查所有当前运行在机器上的进程的这部分信息。并可以通过这个工具来结束你认为对性能有潜在风险的进程。...

[SQL Server]自动监控“发布订阅”的滞后时间(延迟时间)

[SQL Server]自动监控“发布订阅”的滞后时间(延迟时间)

Sql Server 监控 Job 执行情况

由于最近数据库的部分job总是不能十分健康的执行,而监控起来只能登陆db服务器去查看错误日志,维护起来十分麻烦,并且job一般都是比较重要的,比如备份数据库或者清理数据库等等。网站这边,经常由于清理j...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL Server 监控
举报原因:
原因补充:

(最多只允许输入30个字)