基线介绍
基线为历史数据统计而成的数据,具有参考价值,并利用基线数据与当前值的对比,通过一定的报警机制,形成实时监控架构。SQL Server计数器采用同比和环比两种方式。
同比:可以计算未来一周的基线数据,取近八周同一天同一时刻的均值。
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 ) 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
109 IF @@TRANCOUNT >0
110 BEGIN
111 Rollback;
112 END;
113 THROW
114 END CATCH
115
116 FETCH NEXT FROM CBaseCur INTO @MachineName,@LastOneMinute,@RunCBaseEnd
117 END
118 CLOSE CBaseCur
119 DEALLOCATE CBaseCur
spb_CBaseCounterData
同比可以消除由于业务变动带来的波动,具有报警对比价值。下图红线同比数据,表示历史上该时段存在异常升高。
环比:可以计算未来一天的基线数据,取近两周每天同一时刻的均值。
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 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
107 exec (@SqlCmd)
108
109 --0点数据处理
110 IF CONVERT(VARCHAR(24),@RunRBaseStart,120)=CONVERT(VARCHAR(24),@RunRBaseStart,23)+' 00:00:00'
111 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 =1
116 WHILE(@CYLE<14)
117 BEGIN
118 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 END
122 SET @CYLE = @CYLE + 1
123 END
124 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 END
126 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 END
132 SET @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
145 exec (@SqlCmd)
146 END
147
148 END TRY
149 BEGIN CATCH
150 SET @ERRORMSG=@ERRORMSG+ERROR_MESSAGE()
151 IF @@TRANCOUNT >0
152 BEGIN
153 ROLLBACK;
154 END
155 END CATCH
156
157 FETCH NEXT FROM RBaseCur INTO @MachineName,@LastOneMinute,@LastRBaseEnd
158 END
159 CLOSE RBaseCur
160 DEALLOCATE RBaseCur
161
162
163 IF @ERRORMSG<>''
164 BEGIN
165 ;THROW 50000,@ERRORMSG,1
166 END
spb_RBaseCounterData
环比数据可以说明数据近期走势。具有优化对比价值。从上同比图可以看出117-27-139-238服务器cpu在历史上存在异常升高,下图红线环比比数据,却与蓝线同步,说明此异常在近期已经正常。