基线介绍
基线为历史数据统计而成的数据，具有参考价值，并利用基线数据与当前值的对比，通过一定的报警机制，形成实时监控架构。SQL Server计数器采用同比和环比两种方式。
同比：可以计算未来一周的基线数据，取近八周同一天同一时刻的均值。
/****************************** 功能描述：<同比基线算法>
* 创建者：<HuangCH〉
* 创建日期：<2014-11-05>
* 备注说明：<每天执行一次>
##########
Change Log
##########
Date Changer Description
--------------------------------------------------
<2014-11-05> <HuangCH> <新建>
说明：
1、同比算法取近八周数据可以统计一周后的数据
2、考虑当天前一周0点之前的数据已经归档SQLPerfDataStat_Histroy.dbo.CounterData_OneMinute_Bak，因此要计算前一天之前的数据。 2、计数器新建要超过一周才能开始计算 3、开始时间当天0点与结束时间不能超过一周--CONVERT(VARCHAR(26),DATEADD(WEEK,1,@LastCBaseEnd),23)<=@LastOneMinute --------------------------------------------------
***************************/
ALTER PROC [dbo].[spb_CBaseCounterData]
AS
SET NOCOUNT ON
DECLARE @LastOneMinute DATETIME
DECLARE @LastCBaseEnd DATETIME --计算一周之后的数据
DECLARE @RunCBaseEnd DATETIME
DECLARE @RunCBaseEtart DATETIME
DECLARE @CounterID INT
DECLARE @MachineName VARCHAR(128) --若时间未超过一周，则取当前时间
UPDATE SQLPerfDataStat.dbo.CounterDetails_Dts
SET LastCBaseEnd=DATEADD(D,-1,LastOneMinute)
WHERE DATEADD(WEEK,-1,DATEADD(D,-1,LastOneMinute))<CreateTime DECLARE CBaseCur CURSOR FOR
--计数器新建超过一周；
--只能计算当天0点之前的数据：CONVERT(DATETIME,CONVERT(VARCHAR(16),LastOneMinute,23))
--计算当前的时间的前天之前
SELECT DISTINCT MachineName,DATEADD(D,-1,LastOneMinute) LastOneMinute,CONVERT(DATETIME,CONVERT(VARCHAR(16),LastCBaseEnd,120)) LastCBaseEnd
FROM SQLPerfDataStat.dbo.CounterDetails_Dts WITH(NOLOCK)
WHERE DATEDIFF(MI,CONVERT(DATETIME,CONVERT(VARCHAR(16),LastCBaseEnd,120)),DATEADD(D,-1,LastOneMinute))>=1 and DATEADD(WEEK,-1,DATEADD(D,-1,LastOneMinute))>=CreateTime OPEN CBaseCur FETCH NEXT FROM CBaseCur INTO @MachineName,@LastOneMinute,@LastCBaseEnd WHILE @@FETCH_STATUS=0
BEGIN IF CONVERT(VARCHAR(26),DATEADD(WEEK,1,@LastCBaseEnd),23)<=@LastOneMinute--超过一周未收集则时间为上次收集时间推迟一周
BEGIN
SET @RunCBaseEnd=CONVERT(VARCHAR(26),DATEADD(WEEK,1,@LastCBaseEnd),23)
SET @RunCBaseEtart=@LastCBaseEnd
END
ELSE
BEGIN
SET @RunCBaseEnd=@LastOneMinute
SET @RunCBaseEtart=@LastCBaseEnd
END UPDATE SQLPerfDataStat.dbo.CounterDetails_Dts
SET LastCBaseEnd=@RunCBaseEnd
WHERE MachineName=@MachineName BEGIN TRY INSERT INTO dbo.CBaseCounterData_OneMinute(CounterID, CounterDateTime, CounterValue,MaxCounterValue)
SELECT CounterID
,DATEADD(MI,-DATEDIFF(MI,CounterDateTime,Dateadd(week,1,@RunCBaseEnd))%(7*24*60),Dateadd(week,1,@RunCBaseEnd)) CounterDateTime
,AVG(CounterValue) CounterValue,MAX(CounterValue) MaxCounterValue
FROM (
SELECT CounterID,CounterDateTime,CounterValue
FROM SQLPerfDataStat_Histroy.dbo.CounterData_OneMinute_Bak a (NOLOCK)
WHERE
(
(CounterDateTime>=Dateadd(week,-7,@RunCBaseEtart)
AND CounterDateTime<Dateadd(week,-7,@RunCBaseEnd))
OR
(CounterDateTime>=Dateadd(week,-6,@RunCBaseEtart)
AND CounterDateTime<Dateadd(week,-6,@RunCBaseEnd))
OR
(CounterDateTime>=Dateadd(week,-5,@RunCBaseEtart)
AND CounterDateTime<Dateadd(week,-5,@RunCBaseEnd))
OR
(CounterDateTime>=Dateadd(week,-4,@RunCBaseEtart)
AND CounterDateTime<Dateadd(week,-4,@RunCBaseEnd))
OR
(CounterDateTime>=Dateadd(week,-3,@RunCBaseEtart) --取近四周
AND CounterDateTime<Dateadd(week,-3,@RunCBaseEnd))
OR
(CounterDateTime>=Dateadd(week,-2,@RunCBaseEtart)
AND CounterDateTime<Dateadd(week,-2,@RunCBaseEnd))
OR
(CounterDateTime>=Dateadd(week,-1,@RunCBaseEtart)
AND CounterDateTime<Dateadd(week,-1,@RunCBaseEnd))
) AND EXISTS (SELECT TOP 1 1 FROM CounterDetails_Collect WITH(NOLOCK) WHERE CounterID = a.CounterID AND MachineName=@MachineName)
UNION ALL
SELECT CounterID,CounterDateTime,CounterValue
FROM dbo.CounterData_OneMinute a (NOLOCK)
WHERE (CounterDateTime>=@RunCBaseEtart
AND CounterDateTime<@RunCBaseEnd)
AND EXISTS (SELECT TOP 1 1 FROM CounterDetails_Collect WITH(NOLOCK) WHERE CounterID = a.CounterID AND MachineName=@MachineName)
) AA
GROUP BY CounterID,DATEADD(MI,-DATEDIFF(MI,CounterDateTime,Dateadd(week,1,@RunCBaseEnd))%(7*24*60),Dateadd(week,1,@RunCBaseEnd)) END TRY
BEGIN CATCH
IF @@TRANCOUNT >0
BEGIN
Rollback;
END;
THROW
END CATCH FETCH NEXT FROM CBaseCur INTO @MachineName,@LastOneMinute,@RunCBaseEnd
END
CLOSE CBaseCur
DEALLOCATE CBaseCur
spb_CBaseCounterData
同比可以消除由于业务变动带来的波动，具有报警对比价值。下图红线同比数据，表示历史上该时段存在异常升高。
环比：可以计算未来一天的基线数据，取近两周每天同一时刻的均值。
/****************************** 功能描述：<环比基线算法>
* 创建者：<HuangCH〉
* 创建日期：<2014-11-05>
* 备注说明：<每小时执行一次>
##########
Change Log
##########
Date Changer Description
--------------------------------------------------
<2014-11-05> <HuangCH> <新建>
说明：
1、环比算法可以统计一天后的数据 2、如果开始时间与当前时间对比超过一天，则只取开始时间当天的数据进行计算--CONVERT(VARCHAR(26),DATEADD(D,1,@LastRBaseEnd),23)< = @LastOneMinute 3、如果时间分别为两天的0点，则需要单独处理当天0点的数据--DATEDIFF(MI,@RunRBaseStart,@RunRBaseEnd)=1440 4、环比算法，分离工作日和周末，本别计算，统计两周内的数据--DATEPART(WEEKDAY,@RunRBaseStart) IN(2,3,4,5,6)--非周末 --------------------------------------------------
***************************/
ALTER PROC [dbo].[spb_RBaseCounterData]
AS
SET NOCOUNT ON
SET DATEFIRST 7--由于需要考虑工作日与周末，因此需要确认默认设置是以周日开始
DECLARE @LastOneMinute DATETIME
DECLARE @LastRBaseEnd DATETIME --计算一周之后的数据
DECLARE @RunRBaseEnd DATETIME
DECLARE @RunRBaseStart DATETIME
DECLARE @SqlCmd VARCHAR(MAX)
DECLARE @WHERE VARCHAR(2000)
DECLARE @GROUPBY VARCHAR(MAX)
DECLARE @CYLE INT
DECLARE @MachineName VARCHAR(128)
DECLARE @ERRORMSG VARCHAR(MAX)
SET @ERRORMSG='' --更新未超过一周的数据
UPDATE SQLPerfDataStat.dbo.CounterDetails_Dts
SET LastRBaseEnd=CONVERT(DATETIME,CONVERT(VARCHAR(16),GETDATE(),120))
WHERE DATEADD(WEEK,-1,LastOneMinute)<CreateTime --取最后计算小于当前统计时间的数据
DECLARE RBaseCur CURSOR FOR
SELECT MachineName,LastOneMinute,CONVERT(DATETIME,CONVERT(VARCHAR(16),LastRBaseEnd,120)) LastRBaseEnd
FROM SQLPerfDataStat.dbo.CounterDetails_Dts WITH(NOLOCK)
WHERE DATEDIFF(MI,LastRBaseEnd,LastOneMinute)>1 AND DATEADD(WEEK,-1,LastOneMinute)>=CreateTime--不超过新建时间一周，则不处理 OPEN RBaseCur FETCH NEXT FROM RBaseCur INTO @MachineName,@LastOneMinute,@LastRBaseEnd WHILE @@FETCH_STATUS=0
BEGIN IF CONVERT(VARCHAR(26),DATEADD(D,1,@LastRBaseEnd),23)< = @LastOneMinute--如果第二天0点小于当前时间；即不能跨天处理
BEGIN
SET @RunRBaseEnd=CONVERT(VARCHAR(26),DATEADD(D,1,@LastRBaseEnd),23)
SET @RunRBaseStart=@LastRBaseEnd
END
ELSE
BEGIN--在同一天
SET @RunRBaseEnd=@LastOneMinute
SET @RunRBaseStart=@LastRBaseEnd
END IF DATEPART(WEEKDAY,@RunRBaseStart) IN(2,3,4,5,6)--非周末
BEGIN
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)+''')'
SET @CYLE =1
WHILE(@CYLE<14)
BEGIN
IF DATEPART(WEEKDAY,DATEADD(D,-@CYLE,@RunRBaseStart)) IN(2,3,4,5,6)
BEGIN
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)+''')'
END
SET @CYLE = @CYLE + 1
END
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+''')'--周五要计算周一的数据
END
ELSE--周末数据，取近两周周末数据库
BEGIN
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) +''')'
+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)+''')'
SET @GROUPBY='DATEADD(MI,-DATEDIFF(MI,CounterDateTime,'''+CONVERT(VARCHAR(24),@RunRBaseEnd,120)+''')%(24*60),'''+CONVERT(VARCHAR(24),DATEADD(WEEK,1,@RunRBaseEnd),120)+''')'
END UPDATE SQLPerfDataStat.dbo.CounterDetails_Dts
SET LastRBaseEnd=@RunRBaseEnd
WHERE MachineName=@MachineName BEGIN TRY
--插入数据
SET @SqlCmd='INSERT INTO dbo.RBaseCounterData_OneMinute(CounterID, CounterDateTime, CounterValue,MaxCounterValue)'
+CHAR(10)+'SELECT CounterID,'+@GROUPBY+',AVG(CounterValue),MAX(CounterValue) FROM'
+CHAR(10)+'(SELECT CounterID,CounterDateTime,CounterValue'
+CHAR(10)+'FROM CounterData_OneMinute a (NOLOCK)'
+CHAR(10)+@WHERE+') '
+CHAR(10)+'AND EXISTS (SELECT TOP 1 1 FROM CounterDetails_Collect WITH(NOLOCK) WHERE CounterID = a.CounterID AND MachineName='''+@MachineName+''')'
+CHAR(10)+'Union all'
+CHAR(10)+'SELECT CounterID,CounterDateTime,CounterValue'
+CHAR(10)+'FROM SQLPerfDataStat_Histroy.dbo.CounterData_OneMinute_bak a (NOLOCK)'
+CHAR(10)+@WHERE+') '
+CHAR(10)+'AND EXISTS (SELECT TOP 1 1 FROM CounterDetails_Collect WITH(NOLOCK) WHERE CounterID = a.CounterID AND MachineName='''+@MachineName+''')'
+CHAR(10)+')AA'
+CHAR(10)+ 'GROUP BY CounterID,'+@GROUPBY
exec (@SqlCmd) --0点数据处理
IF CONVERT(VARCHAR(24),@RunRBaseStart,120)=CONVERT(VARCHAR(24),@RunRBaseStart,23)+' 00:00:00'
BEGIN
IF DATEPART(WEEKDAY,@RunRBaseStart) IN(2,3,4,5,6)--非周末
BEGIN
SET @WHERE='WHERE ((CounterDateTime='''+CONVERT(VARCHAR(24),@RunRBaseStart,120)+''')'
SET @CYLE =1
WHILE(@CYLE<14)
BEGIN
IF DATEPART(WEEKDAY,DATEADD(D,-@CYLE,@RunRBaseStart)) IN(2,3,4,5,6)
BEGIN
SET @WHERE=@WHERE+CHAR(10)+'OR (CounterDateTime='''+CONVERT(VARCHAR(24),DATEADD(D,-@CYLE,@RunRBaseStart),120)+''')'
END
SET @CYLE = @CYLE + 1
END
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+''')'
END
ELSE--周末数据，取近两周周末数据库
BEGIN
SET @WHERE='WHERE ((CounterDateTime='''+CONVERT(VARCHAR(24),@RunRBaseStart,120)+''')'
+CHAR(10)+'OR (CounterDateTime='''+CONVERT(VARCHAR(24),DATEADD(WEEK,-1,@RunRBaseStart),120)+''')'
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)+''')'
END
SET @SqlCmd='INSERT INTO dbo.RBaseCounterData_OneMinute(CounterID, CounterDateTime, CounterValue,MaxCounterValue)'
+CHAR(10)+'SELECT CounterID,'+@GROUPBY+',AVG(CounterValue),MAX(CounterValue) FROM'
+CHAR(10)+'(SELECT CounterID,CounterDateTime,CounterValue'
+CHAR(10)+'FROM CounterData_OneMinute a (NOLOCK)'
+CHAR(10)+@WHERE+') '
+CHAR(10)+'AND EXISTS (SELECT TOP 1 1 FROM CounterDetails_Collect WITH(NOLOCK) WHERE CounterID = a.CounterID AND MachineName='''+@MachineName+''')'
+CHAR(10)+'Union all'
+CHAR(10)+'SELECT CounterID,CounterDateTime,CounterValue'
+CHAR(10)+'FROM SQLPerfDataStat_Histroy.dbo.CounterData_OneMinute_bak a (NOLOCK)'
+CHAR(10)+@WHERE+') '
+CHAR(10)+'AND EXISTS (SELECT TOP 1 1 FROM CounterDetails_Collect WITH(NOLOCK) WHERE CounterID = a.CounterID AND MachineName='''+@MachineName+''')'
+CHAR(10)+')AA'
+CHAR(10)+ 'GROUP BY CounterID,'+@GROUPBY
exec (@SqlCmd)
END END TRY
BEGIN CATCH
SET @ERRORMSG=@ERRORMSG+ERROR_MESSAGE()
IF @@TRANCOUNT >0
BEGIN
ROLLBACK;
END
END CATCH FETCH NEXT FROM RBaseCur INTO @MachineName,@LastOneMinute,@LastRBaseEnd
END
CLOSE RBaseCur
DEALLOCATE RBaseCur IF @ERRORMSG<>''
BEGIN
;THROW 50000,@ERRORMSG,1
END
spb_RBaseCounterData
环比数据可以说明数据近期走势。具有优化对比价值。从上同比图可以看出117-27-139-238服务器cpu在历史上存在异常升高，下图红线环比比数据，却与蓝线同步，说明此异常在近期已经正常。