监控-CPU使用率
原始脚本来自TG，自己对部分脚本做了调整，分享出来仅供参考，请勿整篇Copy！
使用以下语句获取【CPU使用率】
USE [DBA_Monitor] GO /****** 对象: StoredProcedure [dbo].[DBA_Pro_Get_CpuUseInfo] 脚本日期: 02/07/2017 16:26:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /****************************************************************** 【概要说明】 <作 者> DBA <创建时间> 2015/01/24 10:27 <中文名称> 获取SQLServer进程CPU使用率 <功能说明> 采集当前SQL实例下面的SQLServer进程CPU使用率 <调用方式> 作业方式定时调用为主；错误处理后，维护人员手工调用为辅 <执行说明> 先决条件：正确的传递实例编号参数@InstanceID 注意事项：暂无 <调用示例> exec DBA_Pro_Get_ErrorLog 100233,'UestMail@163.com' 【修订记录】 ------------------------------------------------------------------- <2015/01/24 10:27> <DBA>: 创建 ******************************************************************/ ALTER proc [dbo].[DBA_Pro_Get_CpuUseInfo] @InstanceID int, @Recipients VARCHAR(200)=null as BEGIN SET NOCOUNT ON IF (@InstanceID<100000 or @InstanceID>999999) begin print '数据库实例编号参数错误！' return end BEGIN TRY --抓取SQL进程内使用CPU的情况 DECLARE @ts_now bigint SELECT @ts_now=cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info --获取最近10分钟的CPU使用率信息 SELECT TOP(10) SQLProcessUtilization AS [SQLServerProcessCPUUtilization], SystemIdle AS [SystemIdleProcess], (100 - SystemIdle - SQLProcessUtilization) AS [OtherProcessCPUUtilization], DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [EventTime] into #CPU_Temp FROM ( SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle], record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization], [timestamp] FROM ( SELECT [timestamp], CONVERT(xml, record) AS [record] FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' AND record LIKE N'%<SystemHealth>%' ) AS x ) AS y ORDER BY record_id DESC --获取sqlserver进程CPU使用率大于40%的总次数和实际最大值 declare @CpuTooHighNums int --总次数 declare @CpuTooHighValue int --最大值 select @CpuTooHighNums=IsNull(count(1),0),@CpuTooHighValue=IsNull(max(SQLServerProcessCPUUtilization),0) from #CPU_Temp where SQLServerProcessCPUUtilization>40 insert into Info_CpuUseLog (InstanceID,SystemIdleProcess,SQLServerProcessCPUUtilization,OtherProcessCPUUtilization,EventTime) select @InstanceID,SystemIdleProcess,SQLServerProcessCPUUtilization,OtherProcessCPUUtilization,EventTime from #CPU_Temp WHERE SQLServerProcessCPUUtilization>0 order by EventTime /*--如果最近10分钟内sqlserver进程CPU使用率超过40%的次数>=5，发邮件预警 IF (@CpuTooHighNums>=5) begin declare @bodyTemp01 varchar(1000) declare @subject varchar(64) declare @ip varchar(15) declare @HostName varchar(64) select @bodyTemp01= '最近10分钟，有'+Cast(@CpuTooHighNums as varchar(10))+'次SQLServer进程CPU使用率高于：40%；最高为：'+Cast(@CpuTooHighValue as varchar(10))+'%' select @IP=Reverse(left(Reverse(IntranetIP),charindex('.',Reverse(IntranetIP))-1)) FROM Info_Hardware where ServerID=left(@InstanceID,4) select @HostName=HostName From dbo.Info_Instance where InstanceID=@InstanceID IF @ip IS NOT NULL set @subject='['+@ip+'][重要] CPU使用率预警' ELSE set @subject='[重要] CPU使用率预警' DECLARE @tableHTML NVARCHAR(MAX); SET @tableHTML = N'<style>td{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}th{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}</style> <H2>'+@HostName+'数据库CPU使用率预警</H2>'+@bodyTemp01+'<br /> 报表时间：'+Convert(varchar(19),getdate(),121)+ N'<table width = "100%" table border="0" cellspacing="0" cellpadding="0" style="BORDER-LEFT: black 1px solid;BORDER-TOP: black 1px solid"> <tr><th>序号</th><th>SQLServer进程CPU使用率%</th><th>其他进程CPU使用率%</th><th>系统空闲百分比%</th><th>取样时间</th></tr>' + CAST ( ( SELECT td = ROW_NUMBER() OVER (ORDER BY EventTime desc),'', td = SQLServerProcessCPUUtilization,'', td = OtherProcessCPUUtilization,'', td = SystemIdleProcess,'', td = convert(varchar(19),EventTime,121),'' FROM #CPU_Temp order by EventTime desc FOR XML PATH('tr') ) AS NVARCHAR(MAX) ) + N'</table>' +'<br />邮件发送条件： <br />1、最近10分钟SQLServer进程CPU使用率超过40%的次数>=5'; --select @tableHTML; EXEC msdb.dbo.sp_send_dbmail @profile_name = 'LKProfile', @recipients = @Recipients, @subject = @subject, @body = @tableHTML, @body_format = 'HTML'; end*/ if object_id('tempdb..#cpu_temp')is not null drop table #CPU_Temp END TRY BEGIN CATCH insert into ExecErrorLog(InstanceID,Error_Procname,Error_Numbers,Error_Messages,Error_Serverity,Error_States,Error_Lines) SELECT @InstanceID,ERROR_PROCEDURE(),ERROR_NUMBER(),ERROR_MESSAGE(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_LINE() END CATCH END
如果一个服务器上有多个实例，不知道得出来的数据是服务器整体的，还是单个实例的数据。
数据库服务器收集本地数据，然后使用SSIS汇集监控数据，目标服务器使用以下语句发送【数据库CPU使用率预警】邮件
USE [DBA_Monitor] GO /****** 对象: StoredProcedure [dbo].[RPT_CpuUseLog] 脚本日期: 02/07/2017 16:29:42 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /*----------------------------------------------- 功 能：邮件数据库CPU使用率 最新修改：2016/11/22 调用示例: exec RPT_CpuUseLog 'UestMail@163.com' -----------------------------------------------*/ ALTER Proc [dbo].[RPT_CpuUseLog] @Recipients VARCHAR(200) AS BEGIN SET NOCOUNT ON declare @StartTime datetime declare @Subject varchar(64) --数据获取后，只需取ETLTime最近几分钟的数据，对应的是自上次后的所有数据 set @StartTime=dateadd(mi,-10,getdate()) set @Subject='[ALL][重要] 数据库CPU使用率预警' select InstanceID,count(*) HighNums,max(SQLServerProcessCPUUtilization) HighValue ,min(EventTime) MinTime,max(EventTime) MaxTime into #MailLog from Info_CpuUseLog where ETLTime>=@StartTime and SQLServerProcessCPUUtilization>40 group by InstanceID having count(*)>=5 select InstanceID,min(EventTime) MinTime,max(EventTime) MaxTime into #RangTime from Info_CpuUseLog where ETLTime>=@StartTime group by InstanceID if exists(select top 1 1 FROM #MailLog) begin DECLARE @tableHTML NVARCHAR(MAX); SET @tableHTML = N'<style>td{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}th{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}</style> <H2>数据库CPU使用率预警</H2> 报表时间：'+Convert(varchar(19),getdate(),121)+ N'<table width = "100%" table border="0" cellspacing="0" cellpadding="0" style="BORDER-LEFT: black 1px solid;BORDER-TOP: black 1px solid"> <tr><th>序号</th><th>主机名(IP)</th><th>取样区间</th><th>累计超过次数</th><th>最高CPU使用率%</th><th>首次超过时间</th><th>末次超过时间</th></tr>' + CAST ( ( SELECT td = ROW_NUMBER() OVER (ORDER BY a.InstanceID),'', td = b.HostName+'('+b.ShortIP+')','', td = convert(varchar(19),c.MinTime,121)+'~'+convert(varchar(19),c.MaxTime,121),'', td = a.HighNums,'', td = a.HighValue,'', td = convert(varchar(19),a.MinTime,121),'', td = convert(varchar(19),a.MaxTime,121),'' FROM #MailLog a inner join Info_Server b on left(a.InstanceID,4)=b.ServerID inner join #RangTime c on a.InstanceID=c.InstanceID ORDER BY a.InstanceID FOR XML PATH('tr') ) AS NVARCHAR(MAX) ) + N'</table>说明：最近60分钟SQLServer进程CPU使用率超过40%的汇总数据<br /><br />'; --print @tableHTML; end if exists(select top 1 1 FROM #MailLog) begin DECLARE @tableHTML1 NVARCHAR(MAX); SET @tableHTML1 = N'<style>td{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}th{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}</style> 报表时间：'+Convert(varchar(19),getdate(),121)+ N'<table width = "100%" table border="0" cellspacing="0" cellpadding="0" style="BORDER-LEFT: black 1px solid;BORDER-TOP: black 1px solid"> <tr><th>序号</th><th>主机名(IP)</th><th>SQLServer进程CPU使用率%</th><th>其他进程CPU使用率%</th><th>系统空闲百分比%</th><th>取样时间</th></tr>' + CAST ( ( SELECT td = ROW_NUMBER() OVER (ORDER BY c.InstanceID,c.EventTime desc),'', td = b.HostName+'('+b.ShortIP+')','', td = SQLServerProcessCPUUtilization,'', td = OtherProcessCPUUtilization,'', td = SystemIdleProcess,'', td = convert(varchar(19),EventTime,121),'' FROM #MailLog a inner join Info_Server b on left(a.InstanceID,4)=b.ServerID inner join Info_CpuUseLog c on a.InstanceID=c.InstanceID where c.EventTime>=dateadd(mi,-5,a.MinTime) and c.EventTime<=dateadd(mi,5,a.MaxTime) order by c.InstanceID,c.EventTime desc FOR XML PATH('tr') ) AS NVARCHAR(MAX) ) + N'</table>' +'<br />邮件发送条件： <br />1、最近60分钟SQLServer进程CPU使用率超过40%的次数>=5 <br />2、明细数据返回首、末次超过时间±5分钟范围内的CPU使用率'; --print @tableHTML1; end IF (@tableHTML is null and @tableHTML1 is null) return SET @tableHTML=ISNULL(@tableHTML,'')+ISNULL(@tableHTML1,'') EXEC msdb.dbo.sp_send_dbmail @profile_name= 'LKProfile', @recipients= @Recipients, @subject = @Subject, @body = @tableHTML, @body_format = 'HTML'; if object_id('tempdb..#MailLog')is not null drop table #MailLog if object_id('tempdb..#RangTime')is not null drop table #RangTime END
单独的邮件扩展了原始收集CPU信息中的邮件部分。首先是汇总数据，显示取样区间、超过阈值的次数、最高CPU使用率、首末次超过时间。然后取首末次超过时间±5分钟范围内的CPU使用率明细数据
每10分钟获取最近10分钟的CPU使用率信息，仅保存sqlserver进程CPU使用率>0的记录。邮件每1小时从CPU使用率信息表筛选记录，邮件中的取样区间，由于sqlserver进程CPU使用率=0，显示时间区间可能不是完整的一小时。
|
|
【作者】： 醒嘞 |
| 【出处】： http://www.cnblogs.com/Uest/ | |
| 【声明】： 本文内容仅代表个人观点。如需转载请保留此段声明，且在文章页面明显位置给出原文链接！ |
