首页 > 代码库 > SQL Server基线算法(同比和环比)

SQL Server基线算法(同比和环比)

基线介绍

基线为历史数据统计而成的数据,具有参考价值,并利用基线数据与当前值的对比,通过一定的报警机制,形成实时监控架构。SQL Server计数器采用同比和环比两种方式。

clip_image001

同比:可以计算未来一周的基线数据,取近八周同一天同一时刻的均值。

  1 /****************************** 功能描述:<同比基线算法>  2 *   创建者:<HuangCH〉  3 *   创建日期:<2014-11-05>  4 *   备注说明:<每天执行一次>  5 ##########  6 Change Log  7 ##########  8 Date                 Changer             Description  9 -------------------------------------------------- 10 <2014-11-05>        <HuangCH>            <新建> 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))<CreateTime 35  36 DECLARE CBaseCur CURSOR FOR 37 --计数器新建超过一周; 38 --只能计算当天0点之前的数据:CONVERT(DATETIME,CONVERT(VARCHAR(16),LastOneMinute,23)) 39 --计算当前的时间的前天之前 40 SELECT DISTINCT MachineName,DATEADD(D,-1,LastOneMinute) LastOneMinute,CONVERT(DATETIME,CONVERT(VARCHAR(16),LastCBaseEnd,120))  LastCBaseEnd 41 FROM SQLPerfDataStat.dbo.CounterDetails_Dts WITH(NOLOCK) 42 WHERE  DATEDIFF(MI,CONVERT(DATETIME,CONVERT(VARCHAR(16),LastCBaseEnd,120)),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 53   SET  @RunCBaseEnd=CONVERT(VARCHAR(26),DATEADD(WEEK,1,@LastCBaseEnd),23) 54   SET  @RunCBaseEtart=@LastCBaseEnd 55 END 56 ELSE  57 BEGIN 58   SET  @RunCBaseEnd=@LastOneMinute 59   SET  @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,-7,@RunCBaseEnd)) 79 OR  80 (CounterDateTime>=Dateadd(week,-6,@RunCBaseEtart)  81 AND CounterDateTime<Dateadd(week,-6,@RunCBaseEnd)) 82 OR  83 (CounterDateTime>=Dateadd(week,-5,@RunCBaseEtart)  84 AND CounterDateTime<Dateadd(week,-5,@RunCBaseEnd)) 85 OR          86 (CounterDateTime>=Dateadd(week,-4,@RunCBaseEtart)  87 AND CounterDateTime<Dateadd(week,-4,@RunCBaseEnd)) 88 OR  89 (CounterDateTime>=Dateadd(week,-3,@RunCBaseEtart) --取近四周 90 AND CounterDateTime<Dateadd(week,-3,@RunCBaseEnd)) 91 OR  92 (CounterDateTime>=Dateadd(week,-2,@RunCBaseEtart)  93 AND CounterDateTime<Dateadd(week,-2,@RunCBaseEnd)) 94 OR  95 (CounterDateTime>=Dateadd(week,-1,@RunCBaseEtart)  96 AND CounterDateTime<Dateadd(week,-1,@RunCBaseEnd))         97 ) AND  EXISTS (SELECT TOP 1 1 FROM CounterDetails_Collect WITH(NOLOCK) WHERE CounterID = a.CounterID AND MachineName=@MachineName) 98 UNION ALL 99 SELECT CounterID,CounterDateTime,CounterValue 100 FROM dbo.CounterData_OneMinute a (NOLOCK)101 WHERE (CounterDateTime>=@RunCBaseEtart 102         AND CounterDateTime<@RunCBaseEnd) 103 AND  EXISTS (SELECT TOP 1 1 FROM CounterDetails_Collect WITH(NOLOCK) WHERE CounterID = a.CounterID AND MachineName=@MachineName)104 ) AA105 GROUP BY CounterID,DATEADD(MI,-DATEDIFF(MI,CounterDateTime,Dateadd(week,1,@RunCBaseEnd))%(7*24*60),Dateadd(week,1,@RunCBaseEnd))106 107 END TRY108 BEGIN CATCH109   IF @@TRANCOUNT >0110   BEGIN111     Rollback;112   END;113   THROW 114 END CATCH115 116 FETCH NEXT FROM CBaseCur INTO @MachineName,@LastOneMinute,@RunCBaseEnd117 END118 CLOSE CBaseCur119 DEALLOCATE  CBaseCur
spb_CBaseCounterData

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

clip_image003

环比:可以计算未来一天的基线数据,取近两周每天同一时刻的均值。

  1 /****************************** 功能描述:<环比基线算法>  2 *   创建者:<HuangCH〉  3 *   创建日期:<2014-11-05>  4 *   备注说明:<每小时执行一次>  5 ##########  6 Change Log  7 ##########  8 Date                 Changer             Description  9 -------------------------------------------------- 10 <2014-11-05>        <HuangCH>            <新建> 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 @SqlCmd   VARCHAR(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)<CreateTime 42  43 --取最后计算小于当前统计时间的数据 44 DECLARE RBaseCur CURSOR FOR 45 SELECT MachineName,LastOneMinute,CONVERT(DATETIME,CONVERT(VARCHAR(16),LastRBaseEnd,120))  LastRBaseEnd 46 FROM SQLPerfDataStat.dbo.CounterDetails_Dts WITH(NOLOCK) 47 WHERE  DATEDIFF(MI,LastRBaseEnd,LastOneMinute)>1  AND 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 58   SET  @RunRBaseEnd=CONVERT(VARCHAR(26),DATEADD(D,1,@LastRBaseEnd),23) 59   SET  @RunRBaseStart=@LastRBaseEnd 60 END 61 ELSE  62 BEGIN--在同一天 63   SET  @RunRBaseEnd=@LastOneMinute 64   SET  @RunRBaseStart=@LastRBaseEnd 65 END 66  67 IF DATEPART(WEEKDAY,@RunRBaseStart) IN(2,3,4,5,6)--非周末 68 BEGIN   69   SET @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)+‘‘‘) 70   SET @CYLE =1 71   WHILE(@CYLE<14) 72     BEGIN 73       IF DATEPART(WEEKDAY,DATEADD(D,-@CYLE,@RunRBaseStart)) IN(2,3,4,5,6) 74       BEGIN  75         SET @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)+‘‘‘)         76       END 77       SET @CYLE = @CYLE + 1 78     END 79   SET @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  83   SET @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)+‘‘‘) 85   SET @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 --插入数据 94   SET @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 all101     +CHAR(10)+SELECT CounterID,CounterDateTime,CounterValue102     +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)+)AA106     +CHAR(10)+ GROUP BY CounterID,+@GROUPBY107   exec (@SqlCmd)108 109   --0点数据处理110   IF CONVERT(VARCHAR(24),@RunRBaseStart,120)=CONVERT(VARCHAR(24),@RunRBaseStart,23)+ 00:00:00111   BEGIN         112     IF DATEPART(WEEKDAY,@RunRBaseStart) IN(2,3,4,5,6)--非周末113     BEGIN  114       SET @WHERE=WHERE ((CounterDateTime=‘‘‘+CONVERT(VARCHAR(24),@RunRBaseStart,120)+‘‘‘)115       SET @CYLE =1116       WHILE(@CYLE<14)117         BEGIN118           IF DATEPART(WEEKDAY,DATEADD(D,-@CYLE,@RunRBaseStart)) IN(2,3,4,5,6)119           BEGIN 120             SET @WHERE=@WHERE+CHAR(10)+OR (CounterDateTime=‘‘‘+CONVERT(VARCHAR(24),DATEADD(D,-@CYLE,@RunRBaseStart),120)+‘‘‘)        121           END122           SET @CYLE = @CYLE + 1123         END124       SET @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+‘‘‘)125     END126     ELSE--周末数据,取近两周周末数据库127     BEGIN 128       SET @WHERE=WHERE ((CounterDateTime=‘‘‘+CONVERT(VARCHAR(24),@RunRBaseStart,120)+‘‘‘)129                   +CHAR(10)+OR (CounterDateTime=‘‘‘+CONVERT(VARCHAR(24),DATEADD(WEEK,-1,@RunRBaseStart),120)+‘‘‘)130       SET @GROUPBY=DATEADD(MI,-DATEDIFF(MI,CounterDateTime,‘‘‘+CONVERT(VARCHAR(24),@RunRBaseEnd,120)+‘‘‘)%(24*60),‘‘‘+CONVERT(VARCHAR(24),DATEADD(D,-1,DATEADD(WEEK,1,@RunRBaseEnd)),120)+‘‘‘)131     END132     SET @SqlCmd=INSERT INTO dbo.RBaseCounterData_OneMinute(CounterID, CounterDateTime, CounterValue,MaxCounterValue)133     +CHAR(10)+SELECT CounterID,+@GROUPBY+,AVG(CounterValue),MAX(CounterValue) FROM134     +CHAR(10)+(SELECT CounterID,CounterDateTime,CounterValue135     +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 all139     +CHAR(10)+SELECT CounterID,CounterDateTime,CounterValue140     +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,+@GROUPBY145     exec (@SqlCmd)146   END147 148 END TRY149 BEGIN CATCH150   SET @ERRORMSG=@ERRORMSG+ERROR_MESSAGE()151   IF @@TRANCOUNT >0152   BEGIN153     ROLLBACK;154   END 155 END CATCH156 157 FETCH NEXT FROM RBaseCur INTO @MachineName,@LastOneMinute,@LastRBaseEnd158 END159 CLOSE RBaseCur160 DEALLOCATE  RBaseCur161 162 163 IF @ERRORMSG<>‘‘164 BEGIN165 ;THROW 50000,@ERRORMSG,1166 END
spb_RBaseCounterData

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

clip_image005

SQL Server基线算法(同比和环比)