首页 > 代码库 > SQL Server性能计数器收集汇总方案(Reporting Service)

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 NULL100 BEGIN101 BEGIN TRY102     --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=@MachineName109 110         UPDATE  [dbo].[CounterDetails_Dts]111         SET LastFifteenSec=@FifteenSec,[LastRecordIndex]=@MaxRecordIndex112         WHERE MachineName=@MachineName113         --COMMIT 114 END TRY115 BEGIN CATCH116     IF @@TRANCOUNT>0117        COMMIT;118     --THROW119 END CATCH120 121 --更新122 /*123 UPDATE  [dbo].[CounterDetails_Dts]124 SET LastFifteenSec=@FifteenSec,[LastRecordIndex]=@MaxRecordIndex125 WHERE [CounterID]=@CounterID */126 127 --删除30分钟内的数据128 129 130 END131 132 DELETE A133 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 @MachineName138 END139 CLOSE CUR_COUNTERDATA140 DEALLOCATE CUR_COUNTERDATA141 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.CounterID102             WHERE B.MachineName=@MachineName AND A.CounterDateTime <= @HalfHour And A.CounterDateTime > @OldHalfHour103             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 TRY106         BEGIN CATCH107             IF @@ROWCOUNT>0108               COMMIT109         END CATCH110 111         BEGIN TRY112             --一小时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.CounterID117             WHERE B.MachineName=@MachineName AND A.CounterDateTime <= @OneHour And A.CounterDateTime > @OldOneHour118             GROUP BY A.CounterID,CONVERT(VARCHAR(14),A.CounterDateTime,120)119 120         END TRY121         BEGIN CATCH122             IF @@ROWCOUNT>0123               COMMIT124         END CATCH            125                 126         BEGIN TRY127             --六小时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.CounterID132             WHERE B.MachineName=@MachineName AND A.CounterDateTime <= @SixHour And A.CounterDateTime > @OldSixHour133             GROUP BY A.CounterID,Dateadd(HH,Datepart(HH,A.CounterDateTime) / 6 * 6 -Datepart(hh,A.CounterDateTime),A.CounterDateTime)134         END TRY135         BEGIN CATCH136             IF @@ROWCOUNT>0137               COMMIT138         END CATCH    139     FETCH NEXT FROM CUR_CounterDataDts INTO @MachineName140     END141 CLOSE CUR_CounterDataDts142 DEALLOCATE CUR_CounterDataDts
spb_Perf_CounterDataDts

4、 计算历史数据为基线数据,与当前数据对比。SQL Server基线算法(同比和环比)

image

二、SQL Server汇总统计架构

image

如上图所示,计数器架构分别存储三个数据库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天。

SQL Server性能计数器收集汇总方案(Reporting Service)