首页 > 代码库 > 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 --监控
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
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天。
SQL Server性能计数器收集汇总方案（Reporting Service）
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。