SQL Server性能计数器部署（批量）
一、计数器部署项目介绍
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
步骤二、生成部署脚本（从步骤一结果拷贝脚本到指定服务器执行。）
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
步骤三、部署性能计数器（按步骤二结果的步骤执行。执行首次部署的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
可能需要执行五分钟。后面几分钟收集即可认为是正常。
