SQL Server性能计数器收集汇总方案（Reporting Service）

通过收集计数器信息，并将计数器信息汇总为不同粒度存储，以Reporting Service报表服务器显示。以下是计数器收集汇总的基本架构。

笔者需要收集的SQL Server计数器包括：SQL Server计数器属性详解

一、SQL Server计数器基本架构图

1、生产服务器通过ODBC开发数据库互联ODBC配置访问监控监控服务器的SQLPerfData，将数据写入该数据库。详情请见计数器部署 SQL Server性能计数器部署（批量）

2、通过作业调用存储过程spb_Perf_CounterDataCollect将数据从SQLPerfData写入SQLPerfDataStat数据库。

  1 /****************************** 功能描述：<性能计数器15秒统计>
  2 *   创建者：<HuangCH〉
  3 *   创建日期：<2014-09-22>
  4 *   备注说明：<根据具体业务而定，每分钟一次>
  5 ##########
  6 Change Log
  7 ##########
  8 Date                 Changer             Description
  9 --------------------------------------------------
 10 <2014-09-22>        <HuangCH>            <新建>
 11 --------------------------------------------------
 12 ***************************/
 13 ALTER PROC [dbo].[spb_Perf_CounterDataCollect]
 14 as
 15 --定义当前时间
 16 SET NOCOUNT ON
 17 DECLARE @FifteenSec DATETIME
 18         ,@OneMinute DATETIME
 19         ,@FiveMinute DATETIME
 20         ,@HalfHour DATETIME
 21         ,@OneHour DATETIME
 22         ,@SixHour DATETIME
 23         ,@OldFifteenSec DATETIME
 24 
 25 DECLARE @MaxRecordIndex INT
 26 DECLARE @MinRecordIndex INT
 27 
 28 SET @FifteenSec=CONVERT(VARCHAR(23),GETDATE(),120)
 29 SET @OneMinute=CONVERT(VARCHAR(16),@FifteenSec,120)
 30 SET @FiveMinute=Dateadd(mi,Datepart(mi,@OneMinute) / 5 * 5 - Datepart(mi,@OneMinute),@OneMinute)
 31 SET @HalfHour=Dateadd(mi,Datepart(mi,@OneMinute) / 30 * 30 - Datepart(mi,@OneMinute),@OneMinute)
 32 SET @OneHour=CONVERT(VARCHAR(14),@FifteenSec,120)+'00:00'
 33 SET @SixHour=Dateadd(HH,Datepart(mi,@OneHour) / 30 * 30 - Datepart(mi,@OneHour),@OneHour)
 34 
 35 ------进入循环收集处理-----------------------------------------------
 36 
 37 DECLARE @MachineName SYSNAME
 38 DECLARE CUR_COUNTERDATA CURSOR FOR
 39 SELECT DISTINCT  MachineName
 40 FROM [dbo].[CounterDetails_Collect] WITH(NOLOCK)
 41 OPEN CUR_COUNTERDATA
 42 FETCH NEXT FROM CUR_COUNTERDATA INTO @MachineName
 43 
 44 WHILE @@FETCH_STATUS=0
 45 BEGIN
 46 
 47     IF NOT EXISTS (SELECT TOP 1 1 FROM [dbo].[CounterDetails_Dts] WHERE [MachineName]=@MachineName)
 48     BEGIN
 49         --添加未插入的数据
 50     
 51         INSERT INTO [dbo].[CounterDetails_Dts]
 52         SELECT @MachineName,0,@FifteenSec,@OneMinute,@FiveMinute,@HalfHour,@OneHour,@SixHour,@OneMinute,@OneMinute,@FifteenSec 
 53 
 54         FETCH NEXT FROM CUR_COUNTERDATA INTO @MachineName
 55         CONTINUE
 56     END 
 57 
 58 SELECT @MinRecordIndex=[LastRecordIndex],@OldFifteenSec=LastFifteenSec  FROM [dbo].[CounterDetails_Dts] WITH(NOLOCK)
 59 WHERE [MachineName]=@MachineName
 60 
 61 
 62 IF DATEDIFF(MI,@OldFifteenSec,@FifteenSec)>25
 63 BEGIN
 64   SET  @MinRecordIndex=0
 65 END
 66 
 67 SELECT @MaxRecordIndex=MAX([RecordIndex]),@FifteenSec=MAX(CONVERT(DATETIME,LEFT(A.CounterDateTime,23))) 
 68 FROM [SQLPerfData].[dbo].[CounterData]  A WITH(NOLOCK)
 69 WHERE CounterID=(SELECT TOP 1 CounterID FROM [dbo].[CounterDetails_Collect] B WITH(NOLOCK) WHERE B.MachineName=@MachineName) 
 70 AND [RecordIndex]>@MinRecordIndex
 71 
 72 --若由于重启导致index恢复，则要重新计算
 73 IF @MaxRecordIndex IS NULL
 74 BEGIN
 75 
 76 SET @MaxRecordIndex=@MinRecordIndex--上次最大值
 77 SELECT @MinRecordIndex=NumberOfRecords FROM --当前最大值
 78 (
 79 SELECT ROW_NUMBER() OVER(ORDER BY LogStartTime DESC) ROW_ID,NumberOfRecords 
 80 FROM [SQLPerfData].[dbo].[DisplayToID]
 81 WHERE GUID IN(
 82 SELECT DISTINCT A.GUID FROM [SQLPerfData].[dbo].[CounterData]  A WITH(NOLOCK)
 83 JOIN [dbo].[CounterDetails_Collect] B WITH(NOLOCK)  ON A.CounterID=B.CounterID 
 84 WHERE   B.MachineName=@MachineName)
 85 ) AA
 86 WHERE ROW_ID=1
 87 
 88 --删除已经收集的
 89 DELETE A FROM [SQLPerfData].[dbo].[CounterData]  A WITH(NOLOCK)
 90 JOIN [dbo].[CounterDetails_Collect] B WITH(NOLOCK)  ON A.CounterID=B.CounterID 
 91 WHERE A.[RecordIndex]>@MinRecordIndex AND A.[RecordIndex]<=@MaxRecordIndex AND  B.MachineName=@MachineName
 92 
 93 --恢复当前有效值
 94 SET @MaxRecordIndex=@MinRecordIndex
 95 SET @MinRecordIndex=0
 96 END
 97 
 98 
 99 IF @MaxRecordIndex IS NOT NULL
100 BEGIN
101 BEGIN TRY
102     --BEGIN TRAN 
103         ----收集
104         INSERT INTO [dbo].[CounterData_FifteenSeconds](CounterID,CounterDateTime,CounterValue)
105         SELECT A.CounterID,CONVERT(DATETIME,LEFT(A.CounterDateTime,23)),A.CounterValue 
106         FROM [SQLPerfData].[dbo].[CounterData]  A WITH(NOLOCK)
107         JOIN [dbo].[CounterDetails_Collect] B WITH(NOLOCK)  ON A.CounterID=B.CounterID 
108         WHERE A.[RecordIndex]>@MinRecordIndex AND A.[RecordIndex]<=@MaxRecordIndex AND  B.MachineName=@MachineName
109 
110         UPDATE  [dbo].[CounterDetails_Dts]
111         SET LastFifteenSec=@FifteenSec,[LastRecordIndex]=@MaxRecordIndex
112         WHERE MachineName=@MachineName
113         --COMMIT 
114 END TRY
115 BEGIN CATCH
116     IF @@TRANCOUNT>0
117        COMMIT;
118     --THROW
119 END CATCH
120 
121 --更新
122 /*
123 UPDATE  [dbo].[CounterDetails_Dts]
124 SET LastFifteenSec=@FifteenSec,[LastRecordIndex]=@MaxRecordIndex
125 WHERE [CounterID]=@CounterID */
126 
127 --删除30分钟内的数据
128 
129 
130 END
131 
132 DELETE A
133 FROM [CounterData_FifteenSeconds]   A 
134 JOIN [dbo].[CounterDetails_Collect] B WITH(NOLOCK)  ON A.CounterID=B.CounterID 
135 WHERE B.MachineName=@MachineName AND CounterDateTime<DATEADD(WEEK,-1,@FifteenSec)
136 
137 FETCH NEXT FROM CUR_COUNTERDATA INTO @MachineName
138 END
139 CLOSE CUR_COUNTERDATA
140 DEALLOCATE CUR_COUNTERDATA
141 
142 --监控

spb_Perf_CounterDataCollect

3、汇总取均值，将15秒钟数据汇总成1分钟，5分钟，半小时，一小时，六小时数据。各个粒度的数据用于不同时段的报表显示。例如1分钟数据汇总可用于显示一天内的报表，一个月的报表则需要一小时的数据才能正常显示。通过作业调用spb_Perf_CounterDataDts汇总。

  1 /****************************** 功能描述：<性能计数器15秒统计>
  2 *   创建者：<HuangCH〉
  3 *   创建日期：<2014-09-22>
  4 *   备注说明：<根据具体业务而定，每分钟一次>
  5 ##########
  6 Change Log
  7 ##########
  8 Date                 Changer             Description
  9 --------------------------------------------------
 10 <2014-09-22>        <HuangCH>            <新建>
 11 --------------------------------------------------
 12 ***************************/
 13 ALTER PROC [dbo].[spb_Perf_CounterDataDts]
 14 as
 15 --定义当前时间
 16 SET NOCOUNT ON
 17 DECLARE @OneMinute DATETIME
 18         ,@FiveMinute DATETIME
 19         ,@HalfHour DATETIME
 20         ,@OneHour DATETIME
 21         ,@SixHour DATETIME
 22 DECLARE @OldOneMinute  datetime
 23         ,@OldFiveMinute datetime
 24         ,@OldHalfHour datetime
 25         ,@OldOneHour datetime
 26         ,@OldSixHour datetime
 27         ,@OldFifteenSec DATETIME
 28 
 29 --MachineName
 30 DECLARE @MachineName SYSNAME
 31 
 32 DECLARE CUR_CounterDataDts CURSOR FOR
 33 
 34 SELECT DISTINCT  MachineName
 35 FROM [dbo].[CounterDetails_Dts] WITH(NOLOCK)
 36 OPEN CUR_CounterDataDts
 37 FETCH NEXT FROM CUR_CounterDataDts INTO @MachineName
 38 
 39 WHILE @@FETCH_STATUS=0
 40     BEGIN      
 41         SELECT 
 42         @OldFifteenSec= LastFifteenSec,
 43         @OldOneMinute= LastOneMinute,
 44         @OldFiveMinute= LastFiveMinute,
 45         @OldHalfHour=LastHalfHour,
 46         @OldOneHour=LastOneHour,
 47         @OldSixHour=LastSixHour
 48         FROM [dbo].[CounterDetails_Dts] WITH(NOLOCK)
 49         WHERE MachineName=@MachineName
 50     
 51         SET @OneMinute=CONVERT(VARCHAR(16),@OldFifteenSec,120)
 52         SET @FiveMinute=Dateadd(mi,Datepart(mi,@OneMinute) / 5 * 5 - Datepart(mi,@OneMinute),@OneMinute)
 53         SET @HalfHour=Dateadd(mi,Datepart(mi,@OneMinute) / 30 * 30 - Datepart(mi,@OneMinute),@OneMinute)
 54         SET @OneHour=CONVERT(VARCHAR(14),@OneMinute,120)+'00:00'
 55         SET @SixHour=Dateadd(HH,Datepart(HH,@OneHour) / 6 * 6 -Datepart(hh,@OneHour),@OneHour)
 56 
 57         --超过60分钟未收集，则只收集60分钟以内的数据 
 58         IF DATEDIFF(MI,@OldOneMinute,@OneMinute)>60
 59         BEGIN
 60           SET @OldOneMinute=DATEADD(MI,-60,@OneMinute)
 61         END
 62 
 63         UPDATE [dbo].[CounterDetails_Dts]
 64         SET LastOneMinute=@OneMinute,LastFiveMinute=@FiveMinute,LastHalfHour=@HalfHour,LastOneHour=@OneHour,LastSixHour=@SixHour
 65         WHERE MachineName=@MachineName
 66 
 67         BEGIN TRY    
 68             --一分钟        
 69             INSERT INTO [dbo].[CounterData_OneMinute]
 70             SELECT A.CounterID,CONVERT(VARCHAR(16),A.CounterDateTime,120),AVG(A.CounterValue)
 71             FROM [dbo].[CounterData_FifteenSeconds]  A WITH(NOLOCK)
 72             JOIN [dbo].[CounterDetails_Collect] B WITH(NOLOCK) ON A.CounterID=B.CounterID
 73             WHERE B.MachineName=@MachineName AND A.CounterDateTime <= @OneMinute And A.CounterDateTime > @OldOneMinute
 74             GROUP BY A.CounterID,CONVERT(VARCHAR(16),A.CounterDateTime,120)
 75 
 76         END TRY
 77         BEGIN CATCH
 78             IF @@ROWCOUNT>0
 79               COMMIT
 80         END CATCH
 81         BEGIN TRY
 82             --五分钟
 83             INSERT INTO [dbo].[CounterData_FiveMinute]
 84             SELECT A.CounterID,Dateadd(mi,Datepart(mi,A.CounterDateTime) / 5 * 5 - Datepart(mi,A.CounterDateTime),CONVERT(DATETIME,CONVERT(CHAR(16),A.CounterDateTime,120))),AVG(A.CounterValue)
 85             FROM [dbo].[CounterData_OneMinute]  A WITH(NOLOCK) 
 86             JOIN [dbo].[CounterDetails_Collect] B WITH(NOLOCK) ON A.CounterID=B.CounterID
 87             WHERE B.MachineName=@MachineName AND A.CounterDateTime <= @FiveMinute And A.CounterDateTime > @OldFiveMinute
 88             GROUP BY A.CounterID,Dateadd(mi,Datepart(mi,A.CounterDateTime) / 5 * 5 - Datepart(mi,A.CounterDateTime),CONVERT(DATETIME,CONVERT(CHAR(16),A.CounterDateTime,120)))
 89 
 90         END TRY
 91         BEGIN CATCH
 92             IF @@ROWCOUNT>0
 93               COMMIT
 94 
 95         END CATCH
 96         BEGIN TRY
 97             --半个小时
 98             INSERT INTO [dbo].[CounterData_HalfHour]
 99             SELECT A.CounterID,Dateadd(mi,Datepart(mi,A.CounterDateTime) / 30 * 30 - Datepart(mi,A.CounterDateTime),CONVERT(DATETIME,CONVERT(CHAR(16),A.CounterDateTime,120))),AVG(A.CounterValue)
100             FROM [dbo].[CounterData_OneMinute]  A WITH(NOLOCK) 
101             JOIN [dbo].[CounterDetails_Collect] B WITH(NOLOCK) ON A.CounterID=B.CounterID
102             WHERE B.MachineName=@MachineName AND A.CounterDateTime <= @HalfHour And A.CounterDateTime > @OldHalfHour
103             GROUP BY A.CounterID,Dateadd(mi,Datepart(mi,A.CounterDateTime) / 30 * 30 - Datepart(mi,A.CounterDateTime),CONVERT(DATETIME,CONVERT(CHAR(16),A.CounterDateTime,120)))
104 
105         END TRY
106         BEGIN CATCH
107             IF @@ROWCOUNT>0
108               COMMIT
109         END CATCH
110 
111         BEGIN TRY
112             --一小时
113             INSERT INTO [dbo].[CounterData_OneHour]
114             SELECT A.CounterID,CONVERT(VARCHAR(14),A.CounterDateTime,120)+'00:00',AVG(A.CounterValue)
115             FROM [dbo].[CounterData_OneMinute]  A WITH(NOLOCK) 
116             JOIN [dbo].[CounterDetails_Collect] B WITH(NOLOCK) ON A.CounterID=B.CounterID
117             WHERE B.MachineName=@MachineName AND A.CounterDateTime <= @OneHour And A.CounterDateTime > @OldOneHour
118             GROUP BY A.CounterID,CONVERT(VARCHAR(14),A.CounterDateTime,120)
119 
120         END TRY
121         BEGIN CATCH
122             IF @@ROWCOUNT>0
123               COMMIT
124         END CATCH            
125                 
126         BEGIN TRY
127             --六小时
128             INSERT INTO [dbo].[CounterData_SixHour]
129             SELECT A.CounterID,Dateadd(HH,Datepart(HH,A.CounterDateTime) / 6 * 6 -Datepart(hh,A.CounterDateTime),A.CounterDateTime),AVG(A.CounterValue)
130             FROM [dbo].[CounterData_OneMinute]  A WITH(NOLOCK) 
131             JOIN [dbo].[CounterDetails_Collect] B WITH(NOLOCK) ON A.CounterID=B.CounterID
132             WHERE B.MachineName=@MachineName AND A.CounterDateTime <= @SixHour And A.CounterDateTime > @OldSixHour
133             GROUP BY A.CounterID,Dateadd(HH,Datepart(HH,A.CounterDateTime) / 6 * 6 -Datepart(hh,A.CounterDateTime),A.CounterDateTime)
134         END TRY
135         BEGIN CATCH
136             IF @@ROWCOUNT>0
137               COMMIT
138         END CATCH    
139     FETCH NEXT FROM CUR_CounterDataDts INTO @MachineName
140     END
141 CLOSE CUR_CounterDataDts
142 DEALLOCATE CUR_CounterDataDts

spb_Perf_CounterDataDts

4、计算历史数据为基线数据，与当前数据对比。SQL Server基线算法（同比和环比）

二、SQL Server汇总统计架构

如上图所示，计数器架构分别存储三个数据库SQLPerfData、SQLPerfDataStat、SQLPerfDataStat_Histroy；这三个计数器的功能具体如下：

1、 SQLPerfData接收计数器信息

DisplayToID：计数器自动生成，记录计数器启动信息

CounterDetails：计数器自动生成，记录计数器的基本信息

CounterData：记录计数器传入的数据

2、 SQLPerfDataStat统计计数器信息

基本信息模块：

CounterDetails_Collect：用于同步 CounterDetails表数据，用于轮训统计

CounterDetails_Collect_bak：无用计数器备份

CounterDetails_Dts：统计传输控制表，以服务器为单位，记录服务器统计传输时间。

CounterDetails_Report：同步CounterDetails_Collect表信息，通过优化，用于报表显示作用。

数据模块：

CounterData_FifteenSeconds：15秒数据收集，用于同步CounterData表数据。

CounterData_OneMinute：一分钟数据收集，15秒数据均值计算而成；适合查看当天数据。保留7天数据。

CounterData_FiveMinute：五分钟数据收集，一分钟数据均值计算而成；适合查看4天内数据。保留7天数据。

CounterData_HalfHour：30分钟数据收集，一分钟数据均值计算而成；适合查看一周内数据。保留7天数据。

CounterData_OneHour：一小时数据收集，一分钟数据均值计算而成；适合查看一个月内数据。保留永久

CounterData_SixHour：六小时数据收集，一分钟数据均值计算而成；适合查看一年内数据。保留永久

基线模块：

CBaseCounterData_OneMinute：同比基线，可以计算未来一周的数据走势。保留7天数据。

RBaseCounterData_OneMinute：环比基线，可以计算未来一天的数据走势。保留7天数据。

手动维护模块：

CounterTypeDetails：计数器类型和描述信息记录表。

MonitorContorl：手动维护，计数器报警监控控制表。

3、SQLPerfDataStat_Histroy归档计数器信息

CounterData_OneMinute_bak：一分钟数据备份，保留永久。

CounterData_FiveMinute_bak：五分钟数据备份，保留永久。

CounterData_HalfHour_bak：30分钟数据备份，保留永久。

基线模块：

CBaseCounterData_OneMinute_bak：同比基线备份，保留7天。

RBaseCounterData_OneMinute_bak：环比基线备份，保留7天。

posted @ 2014-12-17 21:39 阿传说阅读(604) 评论(0) 编辑收藏举报

刷新页面返回顶部

阿传说

SQL Server MySQL Hadoop Storm 数据库知识分享现就职地：福州 QQ：694787330 原创作品，转载请注明出处！

SQL Server性能计数器收集汇总方案（Reporting Service）

通过收集计数器信息，并将计数器信息汇总为不同粒度存储，以Reporting Service报表服务器显示。以下是计数器收集汇总的基本架构。

一、SQL Server计数器基本架构图

二、SQL Server汇总统计架构

1、 SQLPerfData接收计数器信息

2、 SQLPerfDataStat统计计数器信息

3、SQLPerfDataStat_Histroy归档计数器信息

公告

阿传说

SQL Server MySQL Hadoop Storm 数据库知识分享 现就职地：福州 QQ：694787330 原创作品，转载请注明出处！

SQL Server性能计数器收集汇总方案（Reporting Service）

通过收集计数器信息，并将计数器信息汇总为不同粒度存储，以Reporting Service报表服务器显示。以下是计数器收集汇总的基本架构。

一、SQL Server计数器基本架构图

二、SQL Server汇总统计架构

1、 SQLPerfData接收计数器信息

2、 SQLPerfDataStat统计计数器信息

3、SQLPerfDataStat_Histroy归档计数器信息

公告

SQL Server MySQL Hadoop Storm 数据库知识分享现就职地：福州 QQ：694787330 原创作品，转载请注明出处！