基线介绍 基线为历史数据统计而成的数据，具有参考价值，并利用基线数据与当前值的对比，通过一定的报警机制，形成实时监控架构。SQL Server计数器采用同比和环比两种方式。
【SQL|SQL Server基线算法（同比和环比）】
文章图片

同比：可以计算未来一周的基线数据，取近八周同一天同一时刻的均值。
文章图片

文章图片

1 /****************************** 功能描述：<同比基线算法> 2 *创建者： 4 *备注说明：<每天执行一次> 5 ########## 6 Change Log 7 ########## 8 DateChangerDescription 9 -------------------------------------------------- 10 <2014-11-05><新建> 11 说明： 12 1、同比算法取近八周数据可以统计一周后的数据 13 2、考虑当天前一周0点之前的数据已经归档SQLPerfDataStat_Histroy.dbo.CounterData_OneMinute_Bak，因此要计算前一天之前的数据。 14 15 2、计数器新建要超过一周才能开始计算 16 17 3、开始时间当天0点与结束时间不能超过一周--CONVERT(VARCHAR(26),DATEADD(WEEK,1,@LastCBaseEnd),23)<=@LastOneMinute 18 19 -------------------------------------------------- 20 ***************************/ 21 ALTER PROC [dbo].[spb_CBaseCounterData] 22 AS 23 SET NOCOUNT ON 24 DECLARE @LastOneMinute DATETIME 25 DECLARE @LastCBaseEnd DATETIME --计算一周之后的数据 26 DECLARE @RunCBaseEnd DATETIME 27 DECLARE @RunCBaseEtart DATETIME 28 DECLARE @CounterID INT 29 DECLARE @MachineName VARCHAR(128) 30 31 --若时间未超过一周，则取当前时间 32 UPDATE SQLPerfDataStat.dbo.CounterDetails_Dts 33 SET LastCBaseEnd=DATEADD(D,-1,LastOneMinute) 34 WHERE DATEADD(WEEK,-1,DATEADD(D,-1,LastOneMinute))=1 and DATEADD(WEEK,-1,DATEADD(D,-1,LastOneMinute))>=CreateTime 43 44 OPEN CBaseCur 45 46 FETCH NEXT FROM CBaseCur INTO @MachineName,@LastOneMinute,@LastCBaseEnd 47 48 WHILE @@FETCH_STATUS=0 49 BEGIN 50 51 IF CONVERT(VARCHAR(26),DATEADD(WEEK,1,@LastCBaseEnd),23)<=@LastOneMinute--超过一周未收集则时间为上次收集时间推迟一周 52 BEGIN 53SET@RunCBaseEnd=CONVERT(VARCHAR(26),DATEADD(WEEK,1,@LastCBaseEnd),23) 54SET@RunCBaseEtart=@LastCBaseEnd 55 END 56 ELSE 57 BEGIN 58SET@RunCBaseEnd=@LastOneMinute 59SET@RunCBaseEtart=@LastCBaseEnd 60 END 61 62 UPDATE SQLPerfDataStat.dbo.CounterDetails_Dts 63 SET LastCBaseEnd=@RunCBaseEnd 64 WHERE MachineName=@MachineName 65 66 BEGIN TRY 67 68 INSERT INTO dbo.CBaseCounterData_OneMinute(CounterID, CounterDateTime, CounterValue,MaxCounterValue) 69 SELECT CounterID 70,DATEADD(MI,-DATEDIFF(MI,CounterDateTime,Dateadd(week,1,@RunCBaseEnd))%(7*24*60),Dateadd(week,1,@RunCBaseEnd)) CounterDateTime 71,AVG(CounterValue) CounterValue,MAX(CounterValue) MaxCounterValue 72 FROM ( 73 SELECT CounterID,CounterDateTime,CounterValue 74 FROM SQLPerfDataStat_Histroy.dbo.CounterData_OneMinute_Bak a (NOLOCK) 75 WHERE 76 ( 77 (CounterDateTime>=Dateadd(week,-7,@RunCBaseEtart) 78 AND CounterDateTime=Dateadd(week,-6,@RunCBaseEtart) 81 AND CounterDateTime=Dateadd(week,-5,@RunCBaseEtart) 84 AND CounterDateTime=Dateadd(week,-4,@RunCBaseEtart) 87 AND CounterDateTime=Dateadd(week,-3,@RunCBaseEtart) --取近四周 90 AND CounterDateTime=Dateadd(week,-2,@RunCBaseEtart) 93 AND CounterDateTime=Dateadd(week,-1,@RunCBaseEtart) 96 AND CounterDateTime=@RunCBaseEtart 102AND CounterDateTime<@RunCBaseEnd) 103 ANDEXISTS (SELECT TOP 1 1 FROM CounterDetails_Collect WITH(NOLOCK) WHERE CounterID = a.CounterID AND MachineName=@MachineName) 104 ) AA 105 GROUP BY CounterID,DATEADD(MI,-DATEDIFF(MI,CounterDateTime,Dateadd(week,1,@RunCBaseEnd))%(7*24*60),Dateadd(week,1,@RunCBaseEnd)) 106 107 END TRY 108 BEGIN CATCH 109IF @@TRANCOUNT >0 110BEGIN 111Rollback; 112END; 113THROW 114 END CATCH 115 116 FETCH NEXT FROM CBaseCur INTO @MachineName,@LastOneMinute,@RunCBaseEnd 117 END 118 CLOSE CBaseCur 119 DEALLOCATECBaseCur

spb_CBaseCounterData 同比可以消除由于业务变动带来的波动，具有报警对比价值。下图红线同比数据，表示历史上该时段存在异常升高。

文章图片

环比：可以计算未来一天的基线数据，取近两周每天同一时刻的均值。
文章图片

文章图片

1 /****************************** 功能描述：<环比基线算法> 2 *创建者： 4 *备注说明：<每小时执行一次> 5 ########## 6 Change Log 7 ########## 8 DateChangerDescription 9 -------------------------------------------------- 10 <2014-11-05><新建> 11 说明： 12 1、环比算法可以统计一天后的数据 13 14 2、如果开始时间与当前时间对比超过一天，则只取开始时间当天的数据进行计算--CONVERT(VARCHAR(26),DATEADD(D,1,@LastRBaseEnd),23)< = @LastOneMinute 15 16 3、如果时间分别为两天的0点，则需要单独处理当天0点的数据--DATEDIFF(MI,@RunRBaseStart,@RunRBaseEnd)=1440 17 18 4、环比算法，分离工作日和周末，本别计算，统计两周内的数据--DATEPART(WEEKDAY,@RunRBaseStart) IN(2,3,4,5,6)--非周末 19 20 -------------------------------------------------- 21 ***************************/ 22 ALTER PROC [dbo].[spb_RBaseCounterData] 23 AS 24 SET NOCOUNT ON 25 SET DATEFIRST 7--由于需要考虑工作日与周末，因此需要确认默认设置是以周日开始 26 DECLARE @LastOneMinute DATETIME 27 DECLARE @LastRBaseEnd DATETIME --计算一周之后的数据 28 DECLARE @RunRBaseEnd DATETIME 29 DECLARE @RunRBaseStart DATETIME 30 DECLARE @SqlCmdVARCHAR(MAX) 31 DECLARE @WHERE VARCHAR(2000) 32 DECLARE @GROUPBY VARCHAR(MAX) 33 DECLARE @CYLE INT 34 DECLARE @MachineName VARCHAR(128) 35 DECLARE @ERRORMSG VARCHAR(MAX) 36 SET @ERRORMSG='' 37 38 --更新未超过一周的数据 39 UPDATE SQLPerfDataStat.dbo.CounterDetails_Dts 40 SET LastRBaseEnd=CONVERT(DATETIME,CONVERT(VARCHAR(16),GETDATE(),120)) 41 WHERE DATEADD(WEEK,-1,LastOneMinute)1AND DATEADD(WEEK,-1,LastOneMinute)>=CreateTime--不超过新建时间一周，则不处理 48 49 OPEN RBaseCur 50 51 FETCH NEXT FROM RBaseCur INTO @MachineName,@LastOneMinute,@LastRBaseEnd 52 53 WHILE @@FETCH_STATUS=0 54 BEGIN 55 56 IF CONVERT(VARCHAR(26),DATEADD(D,1,@LastRBaseEnd),23)< = @LastOneMinute--如果第二天0点小于当前时间；即不能跨天处理 57 BEGIN 58SET@RunRBaseEnd=CONVERT(VARCHAR(26),DATEADD(D,1,@LastRBaseEnd),23) 59SET@RunRBaseStart=@LastRBaseEnd 60 END 61 ELSE 62 BEGIN--在同一天 63SET@RunRBaseEnd=@LastOneMinute 64SET@RunRBaseStart=@LastRBaseEnd 65 END 66 67 IF DATEPART(WEEKDAY,@RunRBaseStart) IN(2,3,4,5,6)--非周末 68 BEGIN 69SET @WHERE='WHERE ((CounterDateTime>'+CASE WHEN CONVERT(VARCHAR(24),@RunRBaseStart,120)=CONVERT(VARCHAR(24),@RunRBaseStart,23)+' 00:00:00' THEN ''ELSE '=' END +''''+CONVERT(VARCHAR(24),@RunRBaseStart,120)+''' AND CounterDateTime<'''+CONVERT(VARCHAR(24),@RunRBaseEnd,120)+''')' 70SET @CYLE =1 71WHILE(@CYLE<14) 72BEGIN 73IF DATEPART(WEEKDAY,DATEADD(D,-@CYLE,@RunRBaseStart)) IN(2,3,4,5,6) 74BEGIN 75SET @WHERE=@WHERE+CHAR(10)+'OR (CounterDateTime>'++CASE WHEN CONVERT(VARCHAR(24),@RunRBaseStart,120)=CONVERT(VARCHAR(24),@RunRBaseStart,23)+' 00:00:00' THEN ''ELSE '=' END ++''''+CONVERT(VARCHAR(24),DATEADD(D,-@CYLE,@RunRBaseStart),120)+''' AND CounterDateTime<'''+CONVERT(VARCHAR(24),DATEADD(D,-@CYLE,@RunRBaseEnd),120)+''')' 76END 77SET @CYLE = @CYLE + 1 78END 79SET @GROUPBY='DATEADD(MI,-DATEDIFF(MI,CounterDateTime,'''+CONVERT(VARCHAR(24),@RunRBaseEnd,120)+''')%(24*60),'''+CASE WHEN DATEPART(WEEKDAY,@RunRBaseStart) =6 THEN CONVERT(VARCHAR(24),DATEADD(D,3,@RunRBaseEnd),120) ELSE CONVERT(VARCHAR(24),DATEADD(D,1,@RunRBaseEnd),120) END+''')'--周五要计算周一的数据 80 END 81 ELSE--周末数据，取近两周周末数据库 82 BEGIN 83SET @WHERE='WHERE ((CounterDateTime>'+CASE WHEN CONVERT(VARCHAR(24),@RunRBaseStart,120)=CONVERT(VARCHAR(24),@RunRBaseStart,23)+' 00:00:00' THEN ''ELSE '=' END +''''+CONVERT(VARCHAR(24),@RunRBaseStart,120)+''' AND CounterDateTime<'''+CONVERT(VARCHAR(24),@RunRBaseEnd,120) +''')' 84+CHAR(10)+'OR (CounterDateTime>'+CASE WHEN CONVERT(VARCHAR(24),@RunRBaseStart,120)=CONVERT(VARCHAR(24),@RunRBaseStart,23)+' 00:00:00' THEN ''ELSE '=' END +''''+CONVERT(VARCHAR(24),DATEADD(WEEK,-1,@RunRBaseStart),120)+''' AND CounterDateTime<'''+CONVERT(VARCHAR(24),DATEADD(WEEK,-1,@RunRBaseEnd),120)+''')' 85SET @GROUPBY='DATEADD(MI,-DATEDIFF(MI,CounterDateTime,'''+CONVERT(VARCHAR(24),@RunRBaseEnd,120)+''')%(24*60),'''+CONVERT(VARCHAR(24),DATEADD(WEEK,1,@RunRBaseEnd),120)+''')' 86 END 87 88 UPDATE SQLPerfDataStat.dbo.CounterDetails_Dts 89 SET LastRBaseEnd=@RunRBaseEnd 90 WHERE MachineName=@MachineName 91 92 BEGIN TRY 93 --插入数据 94SET @SqlCmd='INSERT INTO dbo.RBaseCounterData_OneMinute(CounterID, CounterDateTime, CounterValue,MaxCounterValue)' 95+CHAR(10)+'SELECT CounterID,'+@GROUPBY+',AVG(CounterValue),MAX(CounterValue) FROM' 96+CHAR(10)+'(SELECT CounterID,CounterDateTime,CounterValue' 97+CHAR(10)+'FROM CounterData_OneMinute a (NOLOCK)' 98+CHAR(10)+@WHERE+') ' 99+CHAR(10)+'AND EXISTS (SELECT TOP 1 1 FROM CounterDetails_Collect WITH(NOLOCK) WHERE CounterID = a.CounterID AND MachineName='''+@MachineName+''')' 100+CHAR(10)+'Union all' 101+CHAR(10)+'SELECT CounterID,CounterDateTime,CounterValue' 102+CHAR(10)+'FROM SQLPerfDataStat_Histroy.dbo.CounterData_OneMinute_bak a (NOLOCK)' 103+CHAR(10)+@WHERE+') ' 104+CHAR(10)+'AND EXISTS (SELECT TOP 1 1 FROM CounterDetails_Collect WITH(NOLOCK) WHERE CounterID = a.CounterID AND MachineName='''+@MachineName+''')' 105+CHAR(10)+')AA' 106+CHAR(10)+ 'GROUP BY CounterID,'+@GROUPBY 107exec (@SqlCmd) 108 109--0点数据处理 110IF CONVERT(VARCHAR(24),@RunRBaseStart,120)=CONVERT(VARCHAR(24),@RunRBaseStart,23)+' 00:00:00' 111BEGIN 112IF DATEPART(WEEKDAY,@RunRBaseStart) IN(2,3,4,5,6)--非周末 113BEGIN 114SET @WHERE='WHERE ((CounterDateTime='''+CONVERT(VARCHAR(24),@RunRBaseStart,120)+''')' 115SET @CYLE =1 116WHILE(@CYLE<14) 117BEGIN 118IF DATEPART(WEEKDAY,DATEADD(D,-@CYLE,@RunRBaseStart)) IN(2,3,4,5,6) 119BEGIN 120SET @WHERE=@WHERE+CHAR(10)+'OR (CounterDateTime='''+CONVERT(VARCHAR(24),DATEADD(D,-@CYLE,@RunRBaseStart),120)+''')' 121END 122SET @CYLE = @CYLE + 1 123END 124SET @GROUPBY='DATEADD(MI,-DATEDIFF(MI,CounterDateTime,'''+CONVERT(VARCHAR(24),@RunRBaseEnd,120)+''')%(24*60),'''+CASE WHEN DATEPART(WEEKDAY,@RunRBaseStart) =6 THEN CONVERT(VARCHAR(24),DATEADD(D,3,@RunRBaseEnd),120) ELSE CONVERT(VARCHAR(24),DATEADD(D,1,@RunRBaseEnd),120) END+''')' 125END 126ELSE--周末数据，取近两周周末数据库 127BEGIN 128SET @WHERE='WHERE ((CounterDateTime='''+CONVERT(VARCHAR(24),@RunRBaseStart,120)+''')' 129+CHAR(10)+'OR (CounterDateTime='''+CONVERT(VARCHAR(24),DATEADD(WEEK,-1,@RunRBaseStart),120)+''')' 130SET @GROUPBY='DATEADD(MI,-DATEDIFF(MI,CounterDateTime,'''+CONVERT(VARCHAR(24),@RunRBaseEnd,120)+''')%(24*60),'''+CONVERT(VARCHAR(24),DATEADD(D,-1,DATEADD(WEEK,1,@RunRBaseEnd)),120)+''')' 131END 132SET @SqlCmd='INSERT INTO dbo.RBaseCounterData_OneMinute(CounterID, CounterDateTime, CounterValue,MaxCounterValue)' 133+CHAR(10)+'SELECT CounterID,'+@GROUPBY+',AVG(CounterValue),MAX(CounterValue) FROM' 134+CHAR(10)+'(SELECT CounterID,CounterDateTime,CounterValue' 135+CHAR(10)+'FROM CounterData_OneMinute a (NOLOCK)' 136+CHAR(10)+@WHERE+') ' 137+CHAR(10)+'AND EXISTS (SELECT TOP 1 1 FROM CounterDetails_Collect WITH(NOLOCK) WHERE CounterID = a.CounterID AND MachineName='''+@MachineName+''')' 138+CHAR(10)+'Union all' 139+CHAR(10)+'SELECT CounterID,CounterDateTime,CounterValue' 140+CHAR(10)+'FROM SQLPerfDataStat_Histroy.dbo.CounterData_OneMinute_bak a (NOLOCK)' 141+CHAR(10)+@WHERE+') ' 142+CHAR(10)+'AND EXISTS (SELECT TOP 1 1 FROM CounterDetails_Collect WITH(NOLOCK) WHERE CounterID = a.CounterID AND MachineName='''+@MachineName+''')' 143+CHAR(10)+')AA' 144+CHAR(10)+ 'GROUP BY CounterID,'+@GROUPBY 145exec (@SqlCmd) 146END 147 148 END TRY 149 BEGIN CATCH 150SET @ERRORMSG=@ERRORMSG+ERROR_MESSAGE() 151IF @@TRANCOUNT >0 152BEGIN 153ROLLBACK; 154END 155 END CATCH 156 157 FETCH NEXT FROM RBaseCur INTO @MachineName,@LastOneMinute,@LastRBaseEnd 158 END 159 CLOSE RBaseCur 160 DEALLOCATERBaseCur 161 162 163 IF @ERRORMSG<>'' 164 BEGIN 165 ; THROW 50000,@ERRORMSG,1 166 END

spb_RBaseCounterData 环比数据可以说明数据近期走势。具有优化对比价值。从上同比图可以看出117-27-139-238服务器cpu在历史上存在异常升高，下图红线环比比数据，却与蓝线同步，说明此异常在近期已经正常。

文章图片

转载于:https://www.cnblogs.com/chhuang/p/4170560.html

推荐阅读

• 

  刷牙是先漱口还是直接刷
• 

  ig蕉太狼是中国人吗 ig队员国籍
• 

  商业模式是什么意思
• 

  西门子e36故障
• 

  ts3300显示p08问题的解决方法
• 

  天堂鸟的叶子边变焦了,要剪掉吗 天堂鸟叶子焦边的原因是什么
• 

  电商思维图是什么意思呀 电商思维图是什么意思，电商思维图是什么意思啊
• 

  小米ai音箱可以改名字吗（小米ai音箱能够自己改名字吗）
• 

  我给女儿当婆婆 请你不要尝试做婆婆的女儿
• 

  phpcmsjquery的简单介绍
• 

  基因肽能调理哪些症状
• 

  高铁一等座的桌子在哪
• 

  竹子可以放阳台养吗 阳台种的竹子有毒吗
• 

  石墨烯一般用在哪里 石墨烯可以用了吗
• 

  一周减肥餐 分享一日三餐营养搭配减肥餐
• 

  月子中心|省省吧，月子中心最大的作用就是炫富
• 

  丽字五行属什么 丽字五行属啥
• 

  冬天空调多少度最热
• 

  如何给六年级孩子制定计划
• 

  IG冠军皮肤可以免费领？LOL策划终于良心发现,如何免费领？

• gitlab|gitlab 通过备份还原 admin/runner 500 Internal Server Error
• py连接mysql
• 2019-01-18Mysql中主机名的问题
• MySql数据库备份与恢复
• mysql|InnoDB数据页结构
• 数据库|SQL行转列方式优化查询性能实践
• mysql中视图事务索引与权限管理
• MYSQL主从同步的实现
• MySQL数据库的基本操作
• javaweb|基于Servlet+jsp+mysql开发javaWeb学生成绩管理系统