一、计数器部署项目介绍
SQL Server每个服务器，日常需要监控的计数器指标高达上百，若一个个手动添加非常麻烦。此项目通过命令行工具针对指定计数器集成部署，提高部署效率。此包括开发数据库互联（ODBC）配置，通过ODBC访问监控服务器的数据库，将计数器信息传送到数据库中。同时还会详细讲解选取了哪些计数器，这些数据库的作用；以及如何利用相关的命令来实现批量部署。
二、计数器部署
计数器部署基本包括以下几个步骤：
- 筛选指定计数器名称输出到指定txt文件中：TypePerf.exe使用命令查找计数器
- 部署ODBC配置：开放数据库互联ODBC配置；通过 ODBC直接访问远程监控服务器数据库SQLPerfData， 由于是远程访问，若服务器宕机，监控服务器故障等等都将导致连接断开；连接断开将导致本地计数器停止。因此需要实时启动计数器，以保证计数器及时收集。本文通过系统任务调用vb脚本启动计数器。停止并删除原有计数器
- 新建最新的计数器（新建过程调用已有的txt文件）：PerfMon.exe通过命令管理计数器；若是域环境新建计数器的时候要执行启动账户为域账户；需要确保当前服务器账户在目标服务器中存在，且密码相同。
- 启动性能计数器
- 创建系统任务（五分钟执行一次，每次调用vb脚本，自动启动计数器）
例：部署服务器名称为117-27-139-236的计数器
步骤一、计数器初始化（若只是添加计数器，则不需要执行此步骤）
EXEC [dbo].[spb_PerfConfigure_New] '117-27-139-236'
得出以下结果：
1 ALTER proc [dbo].[spb_PerfConfigure_New]
2 @machinename sysname
3 as
4 SET NOCOUNT ON
5 DECLARE @MSG VARCHAR(MAX)
6 SET @MSG=''
7 IF NOT EXISTS(SELECT TOP 1 1 FROM SQLPerfData.dbo.CounterDetails WITH(NOLOCK) WHERE MachineName='\\'+@machinename)
8 BEGIN
9 SELECT @MSG=@MSG+'结果01：'+@machinename+'服务器未收集任何计数器'+char(10)
10 GOTO RES
11 END
12
13 BEGIN TRY
14 BEGIN TRAN
15 --删除SQLPerfData.dbo.CounterDetails
16 DELETE FROM SQLPerfData.dbo.CounterDetails
17 WHERE MachineName='\\'+@machinename
18
19 INSERT INTO SQLPerfDataStat.dbo.CounterDetails_Collect_bak
20 SELECT * FROM SQLPerfDataStat.dbo.CounterDetails_Collect
21 WHERE MachineName=@machinename
22
23 DELETE FROM SQLPerfDataStat.dbo.CounterDetails_Collect
24 WHERE MachineName=@machinename
25
26 DELETE FROM SQLPerfDataStat.[dbo].[CounterDetails_Dts]
27 WHERE MachineName=@machinename
28 COMMIT
29 SELECT @MSG=@MSG+'结果01：'+@machinename+'原计数器已经删除完成'+char(10)
30 END TRY
31 BEGIN CATCH
32 SELECT @MSG='数据删除失败'+ERROR_MESSAGE()
33 IF @@TRANCOUNT>0
34 ROLLBACK;
35 THROW 50000,@MSG,1
36 END CATCH
37
38 RES:
39 SELECT @MSG=@MSG+'结果02：'+'在服务器['+@machinename+']上执行以下脚本，后按步骤操作'+CHAR(10)
40 +'USE CONFIGDB
41 GO
42 EXEC [DBO].[spb_PerfConfigure]' ;
43
44 THROW 50000,@MSG,1
spb_PerfConfigure_New
步骤二、生成部署脚本（从步骤一结果拷贝脚本到指定服务器执行。）
USE CONFIGDB
GO
EXEC [DBO].[spb_PerfConfigure]
得出以下结果：
1 /****************************** 功能描述：<性能计数器部署>
2 * 创建者：<HuangCH〉
3 * 创建日期：<2014-09-22>
4 * 备注说明：<手动执行>
5 ##########
6 Change Log
7 ##########
8 Date Changer Description
9 --------------------------------------------------
10 <2014-09-24> <HuangCH> <新建>
11 --------------------------------------------------
12 ***************************/
13 ALTER Proc [dbo].[spb_PerfConfigure]
14 as
15
16 SET NOCOUNT ON
17 DECLARE @CMD VARCHAR(8000)
18 DECLARE @Path VARCHAR(8000)
19 DECLARE @RetCode INT
20 DECLARE @ServerName VARCHAR(128)
21 SELECT @ServerName = CASE
22 WHEN Charindex('\',@@SERVERNAME) > 0 THEN LEFT(@@SERVERNAME,Charindex('\',@@SERVERNAME) - 1)
23 ELSE @@SERVERNAME
24 END
25
26
27 --确认路径是否存在
28 SELECT @Path = 'C:\Perf_'+@ServerName+'_请勿删除'
29 SELECT @CMD='DIR '+@Path
30 EXEC @RetCode=MASTER.DBO.XP_CMDSHELL @CMD
31 IF @RetCode=1
32 BEGIN
33 SELECT @CMD='MD '+@Path
34 EXEC @RetCode=MASTER.DBO.XP_CMDSHELL @CMD
35 END
36
37 IF OBJECT_ID('TEMPDB.DBO.TABLEOUT') IS NOT NULL
38 BEGIN
39 DROP TABLE TEMPDB.DBO.TABLEOUT
40 END
41 CREATE TABLE TEMPDB.DBO.TABLEOUT (vars VARCHAR(max))
42
43 ----删除自动启动bat
44 --SELECT @CMD = 'DEL C:\Perf_'+@ServerName+'_请勿删除\PerfAutoStart.bat'
45 --EXEC @RetCode=MASTER.DBO.XP_CMDSHELL @CMD;
46
47 ----添加自动启动脚本bat
48 --TRUNCATE TABLE TEMPDB.DBO.TABLEOUT
49 --INSERT INTO TEMPDB.DBO.TABLEOUT(VARS)
50 --SELECT '@echo off'
51 --UNION all
52 --SELECT 'Logman Start Perf_'+@ServerName
53
54 --SELECT @Path = 'C:\Perf_'+@ServerName+'_请勿删除\PerfAutoStart.bat'
55 --SELECT @CMD='BCP TEMPDB.DBO.TABLEOUT OUT ' + @Path + ' -T -c -CRAW -S' + @@SERVERNAME
56 --EXEC @RetCode=MASTER.DBO.XP_CMDSHELL @CMD;
57
58
59 --删除自动启动vb
60 SELECT @CMD = 'DEL C:\Perf_'+@ServerName+'_请勿删除\PerfAutoStart.vbs'
61 EXEC @RetCode=MASTER.DBO.XP_CMDSHELL @CMD;
62
63 --添加自动启动脚本vb
64 TRUNCATE TABLE TEMPDB.DBO.TABLEOUT
65 INSERT INTO TEMPDB.DBO.TABLEOUT(VARS)
66 SELECT 'set ws=wscript.createobject("wscript.shell")'
67 --UNION all
68 --SELECT 'ws.run "PerfAutoStart.bat /start",0'
69 UNION all
70 SELECT 'ws.run "Logman Start Perf_'+@ServerName+'",0'
71
72
73 SELECT @Path = 'C:\Perf_'+@ServerName+'_请勿删除\PerfAutoStart.vbs'
74 SELECT @CMD='BCP TEMPDB.DBO.TABLEOUT OUT ' + @Path + ' -T -c -CRAW -S' + @@SERVERNAME
75 EXEC @RetCode=MASTER.DBO.XP_CMDSHELL @CMD;
76
77
78
79 --所有计数器临时存放
80 IF OBJECT_ID('TEMPDB.DBO.TempPerf') IS NOT NULL
81 BEGIN
82 DROP TABLE TEMPDB.DBO.TempPerf
83 END
84 CREATE TABLE TEMPDB.DBO.TempPerf (VarStr VARCHAR(max))
85 --计数器筛选存放
86 IF OBJECT_ID('TEMPDB.DBO.Perf') IS NOT NULL
87 BEGIN
88 DROP TABLE TEMPDB.DBO.Perf
89 END
90 CREATE TABLE TEMPDB.DBO.Perf (VarStr VARCHAR(max))
91
92 IF OBJECT_ID('CONFIGDB.DBO.PerfConfigSetting') IS NOT NULL
93 BEGIN
94 TRUNCATE TABLE CONFIGDB.DBO.PerfConfigSetting
95 END
96 ELSE
97 BEGIN
98 CREATE TABLE CONFIGDB.DBO.PerfConfigSetting (VarStr VARCHAR(max))
99 END
100
101 INSERT INTO TEMPDB.DBO.TempPerf
102 EXEC( 'Master..XP_CMDSHELL ''TypePerf -qx''')
103
104 --Step1:通用性能计数器
105 INSERT INTO TempDB.dbo.Perf(VarStr)
106 SELECT VarStr
107 FROM TempDB.dbo.TempPerf(NOLOCK)
108 WHERE
109 --Cpu
110 (VarStr LIKE '\Processor(_Total)%'
111 AND (VarStr LIKE '% Processor Time'
112 OR VarStr LIKE '% Privileged Time'
113 )
114 AND VarStr LIKE '%(_Total)%'
115 )
116 OR VarStr ='\System\Processor Queue Length'
117 --Memory
118 OR (VarStr LIKE '\Memory%'
119 AND (VarStr LIKE '%Committed Bytes'
120 OR VarStr LIKE '%Commit Limit'
121 OR VarStr LIKE '%Available Mbytes'
122 OR VarStr LIKE '%Cache Bytes'
123 OR VarStr LIKE '%Page Faults/sec'
124 OR VarStr LIKE '%Pages/sec'
125 OR VarStr LIKE '%Free System Page Table Entries'
126 )
127 )
128 --PhysicalDisk
129 OR(VarStr LIKE '\PhysicalDisk%'
130 AND (
131 (
132 VarStr LIKE '%Avg. Disk sec/Read'
133 AND VarStr NOT LIKE '%(_Total)%'
134 )
135 OR (
136 VarStr LIKE '%Avg. Disk sec/Write'
137 AND VarStr NOT LIKE '%(_Total)%'
138 )
139 OR (
140 VarStr LIKE '%Avg. Disk sec/Transfer'
141 AND VarStr NOT LIKE '%(_Total)%'
142 )
143 OR (
144 VarStr LIKE '%Avg. Disk Queue Length'
145 AND VarStr NOT LIKE '%(_Total)%'
146 )
147 )
148 )
149 --LogicDisk
150 OR(VarStr LIKE '\logicalDisk%'
151 AND (VarStr LIKE '%% Free Space'
152 OR VarStr LIKE '%Free Megabytes'
153 OR VarStr LIKE '%Disk Read Bytes/sec'
154 OR VarStr LIKE '%Disk Write Bytes/sec'
155 OR VarStr LIKE '%Disk Transfers/sec'
156 OR VarStr LIKE '%Free Megabytes'
157 )
158 AND VarStr NOT LIKE '%(_Total)%'
159 )
160 --SQLServer
161 OR(VarStr LIKE '%:Buffer Manager%'
162 AND (VarStr LIKE '%\Buffer cache hit ratio'
163 OR VarStr LIKE '%\Page life expectancy'
164 OR VarStr LIKE '%\Checkpoint pages/sec'
165 OR VarStr LIKE '%\Lazy writes/sec'
166 OR VarStr LIKE '%\Free pages'
167 OR VarStr LIKE '%\Database pages'
168 OR VarStr LIKE '%\Page reads/sec'
169 OR VarStr LIKE '%\Page writes/sec'
170 OR VarStr LIKE '%\Stolen pages'
171 )
172 )
173 OR (VarStr LIKE '%:Memory Manager%'
174 AND (
175 VarStr LIKE '%\Total Server Memory (KB)'
176 OR VarStr LIKE '%\Target Server Memory (KB)'
177 OR VarStr LIKE '%\Memory Grants Pending'--指定等待工作空间内存授权的进程总数。
178 --OR VarStr LIKE '%\Optimizer Memory (KB)'--指定服务器正用于查询优化的动态内存总数。
179 --OR VarStr LIKE '%\SQL Cache Memory (KB)'--指定服务器正用于动态 SQL 缓存的动态内存总数。
180 --OR VarStr LIKE '%\Lock Memory (KB)'--指定服务器用于锁的动态内存总量。
181 --OR VarStr LIKE '%\Connection Memory (KB)'--指定服务器正用来维护连接的动态内存的总量。
182 --OR VarStr LIKE '%\Granted Workspace Memory (KB)')--指定当前授予执行哈希、排序、大容量复制和索引创建操作等进程的内存总量。
183 )
184 OR (VarStr LIKE '%:General Statistics%'
185 AND (VarStr LIKE '%\Processes blocked'
186 OR VarStr LIKE '%\User Connections'
187 OR VarStr LIKE '%\Logins/sec'
188 OR VarStr LIKE '%\Logouts/sec'
189 OR VarStr LIKE '%\Temp Tables For Destruction'
190 )
191 )
192
193 OR (VarStr LIKE '%:Wait Statistics%'
194 AND (
195 (
196 VarStr LIKE '%(平均等待时间(ms))\Page IO latch waits'
197 OR VarStr LIKE '%(平均等待时间(ms))\Page latch waits'
198 OR VarStr LIKE '%(平均等待时间(ms))\Lock waits'
199 OR VarStr LIKE '%(平均等待时间(ms))\Log write waits'
200 )
201
202 OR (
203 VarStr LIKE '%(Average wait time (ms))\Page IO latch waits'
204 OR VarStr LIKE '%(Average wait time (ms))\Page latch waits'
205 OR VarStr LIKE '%(Average wait time (ms))\Lock waits'
206 OR VarStr LIKE '%(Average wait time (ms))\Log write waits'
207 )
208 )
209 )
210 OR (VarStr LIKE '%:Access Methods%'
211 AND (VarStr LIKE '%\Page Splits/sec'
212 OR VarStr LIKE '%\Workfiles Created/sec'
213 OR VarStr LIKE '%\Worktables Created/sec'
214 )
215 )
216 OR (VarStr LIKE '%:SQL Statistics%'
217 AND (VarStr LIKE '%\Batch Requests/sec'
218 OR VarStr LIKE '%\SQL Compilations/sec'
219 OR VarStr LIKE '%\SQL Re-Compilations/sec'
220 )
221 )
222 OR (VarStr LIKE '%:Locks%'
223 AND(
224 ( VarStr LIKE '%(_Total)%'
225 AND(
226 VarStr LIKE '%\Lock Timeouts/sec'
227 OR VarStr LIKE '%\Lock Requests/sec'
228 OR VarStr LIKE '%\Lock Wait Time (ms)'
229 OR VarStr LIKE '%\Lock Waits/sec'
230 )
231 )
232 --OR ( (
233 -- VarStr LIKE '%(Key)%'
234 -- OR VarStr LIKE '%(Metadata)%'
235 -- OR VarStr LIKE '%(Object)%'
236 -- OR VarStr LIKE '%(Page)%'
237 -- OR VarStr LIKE '%(RID)%'
238 -- )
239 -- AND(VarStr LIKE '%\Lock Wait Time (ms)'
240 -- OR VarStr LIKE '%\Lock Waits/sec'
241 -- )
242 )
243 )
244 OR (VarStr LIKE '%Number of Deadlocks/sec%' AND VarStr LIKE '%(_Total)%')
245 )
246 or( VarStr LIKE '%:Databases%'
247 And (
248 -- VarStr LIKE '%\Data File(s) Size (KB)'
249 --or VarStr LIKE '%\Log File(s) Size (KB)'
250 VarStr LIKE '%\Transactions/sec'
251 --or VarStr LIKE '%\Bulk Copy Rows/sec'
252 or VarStr LIKE '%\Log Flushes/sec'
253 --or VarStr LIKE '%\Log Flush Wait Time'
254 or VarStr LIKE '%\Log Flush Waits/sec'
255 --or VarStr LIKE '%\Log Flush Write Time (ms)'
256 )
257 And VarStr not like '%:Databases(tempdb)\%'
258 And VarStr not like '%:Databases(model)\%'
259 And VarStr not like '%:Databases(master)\%'
260 And VarStr not like '%:Databases(msdb)\%'
261 And VarStr not like '%:Databases(configdb)\%'
262 And VarStr not like '%:Databases(mssqlsystemresource)\%'
263 AND VarStr not LIKE '%(_Total)%'
264 )
265 or varstr like '%:Transactions\Transactions'
266 ORDER BY VarStr
267 --Step2:网络监控
268 DECLARE @Wmic TABLE(ID INT IDENTITY(1,1),ReMark VARCHAR(MAX))
269 INSERT INTO @Wmic EXEC Master..Xp_cmdshell 'wmic nicconfig get DNSHostName,Description,IPAddress,MACAddress,Index,InterfaceIndex /value'
270 ;WITH Y1 AS(
271 SELECT Id
272 ,REPLACE(LTRIM(RTRIM(LEFT(ReMark,CHARINDEX('"}',ReMark)-1))),'IPAddress={"','') AS IPAddress
273 FROM @Wmic
274 WHERE ReMark LIKE 'IPAddress={"%'
275 )
276 ,Y2 AS(
277 SELECT A.ID
278 ,A.IPAddress
279 ,REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(REPLACE(B.ReMark,'Description=',''))),'/','_'),'#','_'),CHAR(10),''),CHAR(13),'')AS Desc1
280 ,LTRIM(RTRIM(REPLACE(C.ReMark,'MACAddress=','')))AS MACAddress
281 FROM Y1 A
282 CROSS APPLY(SELECT TOP 1 Id,ReMark
283 FROM @Wmic
284 WHERE Id <A.ID
285 AND (ReMark LIKE 'Description=%')
286 ORDER BY Id DESC
287 )B
288 CROSS APPLY(SELECT TOP 1 Id,ReMark
289 FROM @Wmic
290 WHERE Id >A.ID
291 AND (ReMark LIKE 'MACAddress=%')
292 )C
293 )
294 ,Y3 AS(
295 SELECT DISTINCT A.*
296 ,B.ID AS ID_B
297 ,REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(REPLACE(C.ReMark,'Description=',''))),'/','_'),'#','_'),CHAR(10),''),CHAR(13),'')AS Desc2
298 FROM Y2 A
299 INNER JOIN @Wmic B ON B.ReMark LIKE '%'+A.MACAddress AND A.ID<>B.ID
300 CROSS APPLY(SELECT TOP 1 Id,ReMark
301 FROM @Wmic
302 WHERE Id <B.ID
303 AND (ReMark LIKE 'Description=%')
304 ORDER BY Id DESC
305 )C
306 )
307 ,Y4 AS(
308 SELECT A.*
309 ,CAST(REPLACE(REPLACE(LTRIM(RTRIM(REPLACE(B.ReMark,'Index=',''))),CHAR(10),''),CHAR(13),'')AS INT)AS IndexId
310 ,CAST(REPLACE(REPLACE(LTRIM(RTRIM(REPLACE(C.ReMark,'InterfaceIndex=',''))),CHAR(10),''),CHAR(13),'')AS INT)AS InterfaceIndex
311 FROM Y3 A
312 CROSS APPLY(SELECT TOP 1 ReMark
313 FROM @Wmic
314 WHERE Id >A.ID_B
315 AND (ReMark LIKE 'Index=%')
316 )B
317 CROSS APPLY(SELECT TOP 1 ReMark
318 FROM @Wmic
319 WHERE Id >A.ID_B
320 AND (ReMark LIKE 'InterfaceIndex=%')
321 )C
322 WHERE A.Desc1<>A.Desc2
323 )
324 ,Y5 AS(
325 SELECT ROW_NUMBER()OVER(ORDER BY IndexId,InterfaceIndex)AS Rnt,*
326 FROM Y4
327 )
328 ,Y6 AS(
329 SELECT Rnt,IPAddress,Desc1,MACAddress
330 ,CASE WHEN Rnt=1 THEN Desc2 ELSE Desc2+' _'+RTRIM(CAST(Rnt AS CHAR))END AS Desc2
331 FROM Y5
332 UNION
333 SELECT 1,IPAddress,Desc1,MACAddress,Desc2
334 FROM Y3
335 WHERE Desc1=Desc2
336 )
337 ,Y7 AS(
338 SELECT VarStr
339 FROM TempDB.dbo.TempPerf(NOLOCK)
340 WHERE VarStr LIKE '\network interface%'
341 AND (VarStr LIKE '%Current Bandwidth'
342 OR VarStr LIKE '%Bytes Received/sec'
343 OR VarStr LIKE '%Bytes Sent/sec'
344 OR VarStr LIKE '%Bytes Total/sec'
345 OR VarStr LIKE '%Output Queue Length'
346 OR VarStr LIKE '%Packets Outbound Discarded'
347 OR VarStr LIKE '%Packets Outbound Errors'
348 OR VarStr LIKE '%Packets Received Discarded'
349 OR VarStr LIKE '%Packets Received Errors'
350 )
351 )
352 ,Y8 AS(
353 SELECT A.*,B.*
354 FROM Y7 A
355 INNER JOIN Y6 B ON 1=1
356 AND REPLACE(REPLACE(A.VarStr,'[','('),']',')') LIKE '%('+B.Desc2+'%'
357 )
358 INSERT INTO TempDB.dbo.Perf(VarStr)
359 SELECT DISTINCT VarStr
360 FROM Y8
361 --添加
362 --INSERT INTO CONFIGDB.DBO.PerfConfigSetting
363 --SELECT *FROM TempDB.dbo.Perf
364
365 --删除计数器配置文件
366 SELECT @CMD = 'DEL C:\Perf_'+@ServerName+'_请勿删除\首次部署_PerfConfigure_' + @ServerName + '_*.txt'
367 EXEC @RetCode=MASTER.DBO.XP_CMDSHELL @CMD
368 --导出计数器配置文件
369 SELECT @Path = 'C:\Perf_'+@ServerName+'_请勿删除\首次部署_PerfConfigure_' + @ServerName + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19),Getdate(),21),'-',''),':',''),' ','') + '.txt'
370 SELECT @CMD='BCP TEMPDB.DBO.Perf OUT ' + @Path + ' -T -c -CRAW -S' + @@SERVERNAME
371 EXEC @RetCode=MASTER.DBO.XP_CMDSHELL @CMD
372
373 --添加部署脚本
374 TRUNCATE TABLE TEMPDB.DBO.TABLEOUT
375 INSERT INTO TEMPDB.DBO.TABLEOUT(VARS)
376 SELECT '@echo off'
377 UNION all
378 --SELECT 'echo 正在删除同名称Cliconfg...'
379 --UNION all
380 --SELECT 'reg delete "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo" /v SQLPerforMance /f'
381 --UNION all
382 --SELECT 'echo -'
383 --UNION all
384 --SELECT 'echo 正在添加Cliconfg部署...'
385 --UNION all
386 --SELECT 'reg add "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo" /v SQLPerforMance /t REG_SZ /d DBNMPNTW,\\117-27-139-236\PIPE\sql\query'
387 --UNION all
388 --SELECT 'echo -'
389 --UNION all
390 SELECT 'echo 正在部署ODBC...'
391 UNION all
392 SELECT 'odbcconf CONFIGSYSDSN "Sql Server" "DSN=Perf_'+@ServerName+'|SERVER=10.195.0.136,55944|Database=SQLPerfData|Trusted_Connection=yes"'
393 UNION all
394 SELECT 'echo -'
395 UNION all
396 SELECT 'echo 正在停止已经存在的计数器...'
397 UNION all
398 SELECT 'Logman Stop Perf_'+@ServerName
399 UNION all
400 SELECT 'echo -'
401 UNION all
402 SELECT 'echo 正在删除已经存在的计数器...'
403 UNION all
404 SELECT 'Logman Delete Perf_'+@ServerName
405 UNION all
406 SELECT 'echo -'
407 UNION all
408 SELECT 'echo 正在部署新计数器...'
409 UNION all
410 SELECT 'Logman Create counter Perf_'+@ServerName+' -si 00:00:15 -cf "' + @Path + '" -o Perf_'+@ServerName + '!Perf_'+@ServerName + ' -f sql -v mmddhhmm '+CASE WHEN Serverproperty('ISClustered') = 1 or Serverproperty('IsHadrEnabled')=1 THEN '-u fzcyjh\administrator "sql.jdyou.org.Sqldata"' ELSE '' END
411 UNION all
412 SELECT 'echo -'
413 UNION all
414 SELECT 'echo 正在初始化并启动计数器...'
415 UNION all
416 SELECT 'Logman Start Perf_'+@ServerName
417 UNION all
418 SELECT 'echo -'
419 UNION all
420 SELECT 'echo 完成计数器部署'
421 UNION all
422 SELECT 'echo -'
423 UNION all
424 SELECT 'echo 正在删除系统任务计划...'
425 UNION all
426 SELECT 'schtasks /delete /tn "[请勿删除]Perf_'+@ServerName+'_计数器自动启动" /f'
427 UNION all
428 SELECT 'echo -'
429 UNION all
430 SELECT 'echo 正在创建系统任务计划...'
431 UNION all
432 SELECT 'schtasks /create /sc MINUTE /mo 5 /st 00:05:00 /tn "[请勿删除]Perf_'+@ServerName+'_计数器自动启动" /tr C:\Perf_'+@ServerName+'_请勿删除\PerfAutoStart.vbs /ru "System"'
433 --UNION all
434 --SELECT 'echo 添加开机启动...'
435 --UNION all
436 --SELECT 'reg add HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Run /v Perf_'+@ServerName+'_计数器自动启动vbs /d '+@Path
437 UNION all
438 SELECT 'echo -'
439 UNION all
440 SELECT 'echo. & pause'
441
442 --删除部署文件
443 SELECT @CMD = 'DEL C:\Perf_'+@ServerName+'_请勿删除\首次部署_执行脚本_' + @ServerName + '_*.bat'
444 EXEC @RetCode=MASTER.DBO.XP_CMDSHELL @CMD;
445 --添加部署文件
446 SELECT @Path = 'C:\Perf_'+@ServerName+'_请勿删除\首次部署_执行脚本_' + @ServerName + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19),Getdate(),21),'-',''),':',''),' ','') + '.bat'
447 SELECT @CMD='BCP TEMPDB.DBO.TABLEOUT OUT ' + @Path + ' -T -c -CRAW -S' + @@SERVERNAME
448 EXEC @RetCode=MASTER.DBO.XP_CMDSHELL @CMD;
449
450
451 DECLARE @INFO VARCHAR(MAX)
452 --群集环境
453 IF Serverproperty('ISClustered') = 1
454 BEGIN
455 DECLARE @CurNode VARCHAR(128)
456 DECLARE @ALLNode VARCHAR(128)
457 SELECT TOP 1 @CurNode=NodeName FROM sys.dm_os_cluster_nodes WITH(NOLOCK)
458 WHERE is_current_owner=1
459 SET @INFO='恭喜！！！成功在群集主节点服务器['+@CurNode+']生成部署文件。'+CHAR(10)+'请按以下步骤执行部署：'
460 SELECT @INFO=@INFO
461 +CHAR(10)+'部署节点['+NodeName+']:从主节点提取以上生成的部署文件'
462 +CHAR(10)+'步骤一(部署)：在['+NodeName+']节点执行Bat文件 ：'+@path
463
464 +CHAR(10)+'步骤二(检测_5分钟)：在[117-27-139-236]监控服务器上执行 ： EXEC [SQLPerfData].[dbo].[spb_PerfConfigure_Check] '''+NodeName+''''
465 +CHAR(10)
466 FROM sys.dm_os_cluster_nodes WITH(NOLOCK)
467 END
468 ELSE
469 BEGIN
470 SET @INFO='恭喜！！！成功在服务器本地生成部署文件！'+CHAR(10)+'请按以下步骤执行部署：'
471 +CHAR(10)+'步骤一(部署)：在['+@ServerName+']服务器执行Bat文件 ：'+@path
472 +CHAR(10)+'步骤二(检测_5分钟)：在[117-27-139-236]监控服务器上执行 ： EXEC [SQLPerfData].[dbo].[spb_PerfConfigure_Check] '''+@ServerName+''''
473 END;
474 THROW 50000,@INFO,1
spb_PerfConfigure
步骤三、部署性能计数器（按步骤二结果的步骤执行。执行首次部署的Bat文件。）
部署需要在当前节点上执行封装脚本，执行完成后，会输出制定的配置文件，如果是群集环境，则需要分别在每个相关节点执行此配置脚本。
如下结果，部署成功
步骤四、检测部署结果
1 ALTER proc [dbo].[spb_PerfConfigure_Check]
2 @MachineName sysname
3 as
4 --执行数据同步
5 exec [SQLPerfDataStat].dbo.spb_Perf_Sync_Setting
6 --declare @MachineName sysname='117-27-139-236'
7
8 DECLARE @RES_T TABLE(CHECK_CLASS NVARCHAR(100),CHECK_INFO NVARCHAR(MAX),ERRORMSG NVARCHAR(MAX))
9 DECLARE @MSG VARCHAR(MAX)
10 SET @MSG=''
11 --是否存在未添加计数器
12 IF NOT EXISTS (
13 SELECT TOP 1 1 FROM [SQLPerfData].dbo.CounterDetails WITH(NOLOCK)
14 WHERE MachineName='\\'+@MachineName
15 )
16 BEGIN
17 SELECT '启动情况' CHECK_CLASS,'计数器未启动' AS CHECK_INFO
18 END
19 ELSE
20 BEGIN
21 SELECT '启动情况' CHECK_CLASS,'计数器已启动' AS CHECK_INFO
22 END
23
24 --是否存在未添加计数器
25 IF EXISTS(
26 SELECT TOP 1 1
27 FROM [SQLPerfDataStat].dbo.CounterTypeDetails B
28 LEFT JOIN (
29 SELECT DISTINCT ObjectName,CounterName
30 FROM [SQLPerfDataStat].dbo.CounterDetails_Collect
31 WHERE MachineName=@MachineName) AA
32 ON AA.ObjectName=B.ObjectName AND AA.CounterName=B.CounterName
33 WHERE AA.ObjectName IS NULL
34 )
35 BEGIN
36 SELECT @MSG=@MSG +B.ObjectName+'\'+B.CounterName +'; '
37 FROM [SQLPerfDataStat].dbo.CounterTypeDetails B
38 LEFT JOIN (
39 SELECT DISTINCT ObjectName,CounterName
40 FROM [SQLPerfDataStat].dbo.CounterDetails_Collect
41 WHERE MachineName=@MachineName) AA
42 ON AA.ObjectName=B.ObjectName AND AA.CounterName=B.CounterName
43 WHERE AA.ObjectName IS NULL
44
45 SELECT '对象添加情况' CHECK_CLASS,'未完整添加计数器' CHECK_INFO,@MSG as ERRORMSG
46 END
47 ELSE
48 BEGIN
49 SELECT '对象添加情况' CHECK_CLASS,'完整添加计数器' CHECK_INFO
50 END
51
52 --察看近五分钟收集情况
53 DECLARE @NOWDATE DATETIME
54 SET @NOWDATE=CONVERT(VARCHAR(16),GETDATE(),120)--当前时间
55 WAITFOR DELAY '00:05:00'--5分钟之后
56
57 DECLARE @I_COUNT INT
58 DECLARE @C_COUNT INT
59 SELECT @C_COUNT=COUNT(CounterID)
60 FROM [SQLPerfDataStat].dbo.CounterDetails_Collect A
61 WHERE A.MachineName=@MachineName
62 DECLARE @I INT
63 SET @I=0
64 WHILE @I<5
65 BEGIN
66 SELECT @I_COUNT=COUNT(CounterID)
67 FROM [SQLPerfDataStat].[dbo].[CounterData_OneMinute] B
68 WHERE B.CounterDateTime=DATEADD(MI,-@I,@NOWDATE)
69 AND B.CounterID IN(SELECT CounterID FROM [SQLPerfDataStat].dbo.CounterDetails_Collect A WHERE A.MachineName=@MachineName)
70
71 IF @C_COUNT=@I_COUNT
72 BEGIN
73 INSERT INTO @RES_T
74 SELECT '第'+cast(@I+1 as varchar(20))+'分钟计数器收集情况' CHECK_CLASS,'收集正常' CHECK_INFO,'当前要求：'+CONVERT(VARCHAR(28),@C_COUNT)+'收集个数：'+CONVERT(VARCHAR(28),@I_COUNT)
75 END
76 ELSE
77 BEGIN
78 INSERT INTO @RES_T
79 SELECT '第'+cast(@I+1 as varchar(20))+'分钟计数器收集情况' CHECK_CLASS,'收集不正常' CHECK_INFO,'当前要求：'+CONVERT(VARCHAR(28),@C_COUNT)+'收集个数：'+CONVERT(VARCHAR(28),@I_COUNT)
80 END
81
82 SET @I=@I+1
83 END
84 SELECT * FROM @RES_T
spb_PerfConfigure_Check
可能需要执行五分钟。后面几分钟收集即可认为是正常。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
