SQL Server性能计数器部署（批量）

一、计数器部署项目介绍

SQL Server每个服务器，日常需要监控的计数器指标高达上百，若一个个手动添加非常麻烦。此项目通过命令行工具针对指定计数器集成部署，提高部署效率。此包括开发数据库互联（ODBC）配置，通过ODBC访问监控服务器的数据库，将计数器信息传送到数据库中。同时还会详细讲解选取了哪些计数器，这些数据库的作用；以及如何利用相关的命令来实现批量部署。

二、计数器部署

计数器部署基本包括以下几个步骤：

1. 筛选指定计数器名称输出到指定txt文件中：TypePerf.exe使用命令查找计数器
2. 部署ODBC配置：开放数据库互联ODBC配置；通过 ODBC直接访问远程监控服务器数据库SQLPerfData， 由于是远程访问，若服务器宕机，监控服务器故障等等都将导致连接断开；连接断开将导致本地计数器停止。因此需要实时启动计数器，以保证计数器及时收集。本文通过系统任务调用vb脚本启动计数器。停止并删除原有计数器
3. 新建最新的计数器（新建过程调用已有的txt文件）：PerfMon.exe通过命令管理计数器；若是域环境新建计数器的时候要执行启动账户为域账户；需要确保当前服务器账户在目标服务器中存在，且密码相同。
4. 启动性能计数器
5. 创建系统任务（五分钟执行一次，每次调用vb脚本，自动启动计数器）

例：部署服务器名称为117-27-139-236的计数器

步骤一、计数器初始化（若只是添加计数器，则不需要执行此步骤）

EXEC [dbo].[spb_PerfConfigure_New] '117-27-139-236'

得出以下结果：

 ALTER proc [dbo].[spb_PerfConfigure_New]
 @machinename sysname
 as
 SET NOCOUNT ON
 DECLARE @MSG VARCHAR(MAX)
 SET @MSG=''
 IF NOT EXISTS(SELECT TOP 1 1 FROM SQLPerfData.dbo.CounterDetails WITH(NOLOCK) WHERE MachineName='\\'+@machinename)
 BEGIN
   SELECT @MSG=@MSG+'结果01：'+@machinename+'服务器未收集任何计数器'+char(10)
   GOTO RES
 END

 BEGIN TRY
     BEGIN TRAN
     --删除SQLPerfData.dbo.CounterDetails
     DELETE FROM SQLPerfData.dbo.CounterDetails
     WHERE MachineName='\\'+@machinename

     INSERT INTO SQLPerfDataStat.dbo.CounterDetails_Collect_bak
     SELECT * FROM SQLPerfDataStat.dbo.CounterDetails_Collect
     WHERE MachineName=@machinename

     DELETE FROM SQLPerfDataStat.dbo.CounterDetails_Collect
     WHERE MachineName=@machinename

     DELETE FROM SQLPerfDataStat.[dbo].[CounterDetails_Dts]
     WHERE MachineName=@machinename
     COMMIT
 SELECT @MSG=@MSG+'结果01：'+@machinename+'原计数器已经删除完成'+char(10)
 END TRY
 BEGIN CATCH
 SELECT @MSG='数据删除失败'+ERROR_MESSAGE()
 IF @@TRANCOUNT>0
 ROLLBACK;
 THROW 50000,@MSG,1
 END CATCH

 RES:
 SELECT @MSG=@MSG+'结果02：'+'在服务器['+@machinename+']上执行以下脚本，后按步骤操作'+CHAR(10)
 +'USE CONFIGDB
 GO
 EXEC [DBO].[spb_PerfConfigure]' ;

 THROW 50000,@MSG,1

spb_PerfConfigure_New

步骤二、生成部署脚本（从步骤一结果拷贝脚本到指定服务器执行。）

USE CONFIGDB
GO
EXEC [DBO].[spb_PerfConfigure]

得出以下结果：

 /****************************** 功能描述：<性能计数器部署>
 *   创建者：<HuangCH〉
 *   创建日期：<2014-09-22>
 *   备注说明：<手动执行>
 ##########
 Change Log
 ##########
 Date                 Changer             Description
 --------------------------------------------------
 <2014-09-24>        <HuangCH>            <新建>
 --------------------------------------------------
 ***************************/
 ALTER Proc [dbo].[spb_PerfConfigure]
 as

 SET NOCOUNT ON
 DECLARE @CMD VARCHAR(8000)
 DECLARE @Path VARCHAR(8000)
 DECLARE @RetCode INT
 DECLARE @ServerName VARCHAR(128)
 SELECT @ServerName = CASE
                        WHEN Charindex('\',@@SERVERNAME) > 0 THEN LEFT(@@SERVERNAME,Charindex('\',@@SERVERNAME) - 1)
                        ELSE @@SERVERNAME
                      END

 --确认路径是否存在
 SELECT @Path = 'C:\Perf_'+@ServerName+'_请勿删除'
 SELECT @CMD='DIR '+@Path
 EXEC @RetCode=MASTER.DBO.XP_CMDSHELL @CMD
 IF @RetCode=1
 BEGIN
     SELECT @CMD='MD '+@Path
     EXEC @RetCode=MASTER.DBO.XP_CMDSHELL @CMD
 END

 IF OBJECT_ID('TEMPDB.DBO.TABLEOUT') IS NOT NULL
 BEGIN
   DROP TABLE TEMPDB.DBO.TABLEOUT
 END
 CREATE TABLE TEMPDB.DBO.TABLEOUT (vars VARCHAR(max))

 ----删除自动启动bat
 --SELECT @CMD = 'DEL C:\Perf_'+@ServerName+'_请勿删除\PerfAutoStart.bat'
 --EXEC @RetCode=MASTER.DBO.XP_CMDSHELL @CMD;

 ----添加自动启动脚本bat
 --TRUNCATE TABLE TEMPDB.DBO.TABLEOUT
 --INSERT INTO TEMPDB.DBO.TABLEOUT(VARS)
 --SELECT '@echo off'
 --UNION all
 --SELECT 'Logman Start Perf_'+@ServerName

 --SELECT @Path = 'C:\Perf_'+@ServerName+'_请勿删除\PerfAutoStart.bat'
 --SELECT @CMD='BCP TEMPDB.DBO.TABLEOUT OUT ' + @Path + ' -T -c -CRAW -S' + @@SERVERNAME
 --EXEC @RetCode=MASTER.DBO.XP_CMDSHELL @CMD;

 --删除自动启动vb
 SELECT @CMD = 'DEL C:\Perf_'+@ServerName+'_请勿删除\PerfAutoStart.vbs'
 EXEC @RetCode=MASTER.DBO.XP_CMDSHELL @CMD;

 --添加自动启动脚本vb
 TRUNCATE TABLE TEMPDB.DBO.TABLEOUT
 INSERT INTO TEMPDB.DBO.TABLEOUT(VARS)
 SELECT 'set ws=wscript.createobject("wscript.shell")'
 --UNION all
 --SELECT 'ws.run "PerfAutoStart.bat /start",0'
 UNION all
 SELECT 'ws.run "Logman Start Perf_'+@ServerName+'",0'

 SELECT @Path = 'C:\Perf_'+@ServerName+'_请勿删除\PerfAutoStart.vbs'
 SELECT @CMD='BCP TEMPDB.DBO.TABLEOUT OUT ' + @Path + ' -T -c -CRAW -S' + @@SERVERNAME
 EXEC @RetCode=MASTER.DBO.XP_CMDSHELL @CMD;

 --所有计数器临时存放
 IF OBJECT_ID('TEMPDB.DBO.TempPerf') IS NOT NULL
 BEGIN
   DROP TABLE TEMPDB.DBO.TempPerf
 END
 CREATE TABLE TEMPDB.DBO.TempPerf (VarStr VARCHAR(max))
 --计数器筛选存放
 IF OBJECT_ID('TEMPDB.DBO.Perf') IS NOT NULL
 BEGIN
   DROP TABLE TEMPDB.DBO.Perf
 END
 CREATE TABLE TEMPDB.DBO.Perf (VarStr VARCHAR(max))

 IF OBJECT_ID('CONFIGDB.DBO.PerfConfigSetting') IS NOT NULL
 BEGIN
   TRUNCATE TABLE CONFIGDB.DBO.PerfConfigSetting
 END
 ELSE
 BEGIN
   CREATE TABLE CONFIGDB.DBO.PerfConfigSetting (VarStr VARCHAR(max))
 END

 INSERT INTO  TEMPDB.DBO.TempPerf
 EXEC( 'Master..XP_CMDSHELL ''TypePerf -qx''')

 --Step1:通用性能计数器
 INSERT INTO TempDB.dbo.Perf(VarStr)
 SELECT VarStr
 FROM TempDB.dbo.TempPerf(NOLOCK)
 WHERE
      --Cpu
      (VarStr LIKE '\Processor(_Total)%'
               AND (VarStr LIKE '% Processor Time'
                    OR VarStr LIKE '% Privileged Time'
                    )
               AND VarStr LIKE '%(_Total)%'
           )
      OR VarStr ='\System\Processor Queue Length'
      --Memory
      OR (VarStr LIKE '\Memory%'
               AND (VarStr LIKE '%Committed Bytes'
                    OR VarStr LIKE '%Commit Limit'
                    OR VarStr LIKE '%Available Mbytes'
                    OR VarStr LIKE '%Cache Bytes'
                    OR VarStr LIKE '%Page Faults/sec'
                    OR VarStr LIKE '%Pages/sec'
                    OR  VarStr LIKE '%Free System Page Table Entries'
                    )
           )
      --PhysicalDisk
      OR(VarStr LIKE '\PhysicalDisk%'
             AND (
                 (
                     VarStr LIKE '%Avg. Disk sec/Read'
                 AND VarStr NOT LIKE '%(_Total)%'
                 )
             OR (
                     VarStr LIKE '%Avg. Disk sec/Write'
                 AND VarStr NOT LIKE '%(_Total)%'
                 )
             OR (
                     VarStr LIKE '%Avg. Disk sec/Transfer'
                 AND VarStr NOT LIKE '%(_Total)%'
                 )
             OR (
                     VarStr LIKE '%Avg. Disk Queue Length'
                 AND VarStr NOT LIKE '%(_Total)%'
                 )
                 )
           )
      --LogicDisk
      OR(VarStr LIKE '\logicalDisk%'
           AND (VarStr LIKE '%% Free Space'
               OR VarStr LIKE '%Free Megabytes'
               OR VarStr LIKE '%Disk Read Bytes/sec'
               OR VarStr LIKE '%Disk Write Bytes/sec'
               OR VarStr LIKE '%Disk Transfers/sec'
               OR VarStr LIKE '%Free Megabytes'
               )
           AND VarStr NOT LIKE '%(_Total)%'
           )
      --SQLServer
      OR(VarStr LIKE '%:Buffer Manager%'
           AND (VarStr LIKE '%\Buffer cache hit ratio'
               OR VarStr LIKE '%\Page life expectancy'
               OR VarStr LIKE '%\Checkpoint pages/sec'
               OR VarStr LIKE '%\Lazy writes/sec'
               OR VarStr LIKE '%\Free pages'
               OR VarStr LIKE '%\Database pages'
               OR VarStr LIKE '%\Page reads/sec'
               OR VarStr LIKE '%\Page writes/sec'
               OR VarStr LIKE '%\Stolen pages'
               )
           )
      OR (VarStr LIKE '%:Memory Manager%'
            AND (
                      VarStr LIKE '%\Total Server Memory (KB)'
                 OR      VarStr LIKE '%\Target Server Memory (KB)'
                 OR      VarStr LIKE '%\Memory Grants Pending'--指定等待工作空间内存授权的进程总数。
                 --OR      VarStr LIKE '%\Optimizer Memory (KB)'--指定服务器正用于查询优化的动态内存总数。
                 --OR      VarStr LIKE '%\SQL Cache Memory (KB)'--指定服务器正用于动态 SQL 缓存的动态内存总数。
                 --OR      VarStr LIKE '%\Lock Memory (KB)'--指定服务器用于锁的动态内存总量。
                 --OR      VarStr LIKE '%\Connection Memory (KB)'--指定服务器正用来维护连接的动态内存的总量。
                 --OR      VarStr LIKE '%\Granted Workspace Memory (KB)')--指定当前授予执行哈希、排序、大容量复制和索引创建操作等进程的内存总量。
                 )
      OR (VarStr LIKE '%:General Statistics%'
           AND (VarStr LIKE '%\Processes blocked'
               OR VarStr LIKE '%\User Connections'
               OR VarStr LIKE '%\Logins/sec'
               OR VarStr LIKE '%\Logouts/sec'
               OR VarStr LIKE '%\Temp Tables For Destruction'
           )
           )

      OR (VarStr LIKE '%:Wait Statistics%'
      AND (
               (
               VarStr LIKE '%(平均等待时间(ms))\Page IO latch waits'
               OR VarStr LIKE '%(平均等待时间(ms))\Page latch waits'
               OR VarStr LIKE '%(平均等待时间(ms))\Lock waits'
               OR VarStr LIKE '%(平均等待时间(ms))\Log write waits'
               )

           OR (
               VarStr LIKE '%(Average wait time (ms))\Page IO latch waits'
               OR VarStr LIKE '%(Average wait time (ms))\Page latch waits'
               OR VarStr LIKE '%(Average wait time (ms))\Lock waits'
               OR VarStr LIKE '%(Average wait time (ms))\Log write waits'
               )
           )
           )
      OR (VarStr LIKE '%:Access Methods%'
           AND (VarStr LIKE '%\Page Splits/sec'
               OR VarStr LIKE '%\Workfiles Created/sec'
               OR VarStr LIKE '%\Worktables Created/sec'
               )
           )
      OR (VarStr LIKE '%:SQL Statistics%'
           AND (VarStr LIKE '%\Batch Requests/sec'
               OR VarStr LIKE '%\SQL Compilations/sec'
               OR VarStr LIKE '%\SQL Re-Compilations/sec'
               )
           )
      OR (VarStr LIKE '%:Locks%'
             AND(
                 ( VarStr LIKE '%(_Total)%'
                     AND(
                         VarStr LIKE '%\Lock Timeouts/sec'
                         OR VarStr LIKE '%\Lock Requests/sec'
                         OR VarStr LIKE '%\Lock Wait Time (ms)'
                         OR VarStr LIKE '%\Lock Waits/sec'
                         )
                     )
                 --OR (   (
                 --        VarStr LIKE '%(Key)%'
                 --        OR VarStr LIKE '%(Metadata)%'
                 --        OR VarStr LIKE '%(Object)%'
                 --        OR VarStr LIKE '%(Page)%'
                 --        OR VarStr LIKE '%(RID)%'
                 --        )
                 --    AND(VarStr LIKE '%\Lock Wait Time (ms)'
                 --        OR VarStr LIKE '%\Lock Waits/sec'
                 --        )
                     )
                 )
           OR (VarStr LIKE '%Number of Deadlocks/sec%' AND VarStr LIKE '%(_Total)%')
           )
     or( VarStr LIKE '%:Databases%'
           And (
             --   VarStr LIKE '%\Data File(s) Size (KB)'
                --or VarStr LIKE '%\Log File(s) Size (KB)'
                 VarStr LIKE '%\Transactions/sec'
                --or VarStr LIKE '%\Bulk Copy Rows/sec'
                or VarStr LIKE '%\Log Flushes/sec'
                --or VarStr LIKE '%\Log Flush Wait Time'
                or VarStr LIKE '%\Log Flush Waits/sec'
                --or VarStr LIKE '%\Log Flush Write Time (ms)'
                )
           And VarStr not like '%:Databases(tempdb)\%'
           And VarStr not like '%:Databases(model)\%'
           And VarStr not like '%:Databases(master)\%'
           And VarStr not like '%:Databases(msdb)\%'
           And VarStr not like '%:Databases(configdb)\%'
           And VarStr not like '%:Databases(mssqlsystemresource)\%'
           AND VarStr not LIKE '%(_Total)%'
           )
     or varstr like '%:Transactions\Transactions'
 ORDER BY VarStr
 --Step2:网络监控
 DECLARE @Wmic TABLE(ID INT IDENTITY(1,1),ReMark VARCHAR(MAX))
 INSERT INTO @Wmic EXEC Master..Xp_cmdshell 'wmic nicconfig get DNSHostName,Description,IPAddress,MACAddress,Index,InterfaceIndex /value'
 ;WITH Y1 AS(
 SELECT Id
     ,REPLACE(LTRIM(RTRIM(LEFT(ReMark,CHARINDEX('"}',ReMark)-1))),'IPAddress={"','') AS IPAddress
 FROM @Wmic
 WHERE ReMark LIKE 'IPAddress={"%'
 )
 ,Y2 AS(
 SELECT A.ID
     ,A.IPAddress
     ,REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(REPLACE(B.ReMark,'Description=',''))),'/','_'),'#','_'),CHAR(10),''),CHAR(13),'')AS Desc1
     ,LTRIM(RTRIM(REPLACE(C.ReMark,'MACAddress=','')))AS MACAddress
 FROM Y1    A
 CROSS APPLY(SELECT TOP 1 Id,ReMark
             FROM @Wmic
             WHERE Id <A.ID
                 AND (ReMark LIKE 'Description=%')
             ORDER BY Id DESC
             )B
 CROSS APPLY(SELECT TOP 1 Id,ReMark
             FROM @Wmic
             WHERE Id >A.ID
                 AND (ReMark LIKE 'MACAddress=%')
             )C
 )
 ,Y3 AS(
 SELECT DISTINCT A.*
     ,B.ID AS ID_B
     ,REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(REPLACE(C.ReMark,'Description=',''))),'/','_'),'#','_'),CHAR(10),''),CHAR(13),'')AS Desc2
 FROM Y2 A
 INNER JOIN @Wmic B ON B.ReMark LIKE '%'+A.MACAddress AND A.ID<>B.ID
 CROSS APPLY(SELECT TOP 1 Id,ReMark
             FROM @Wmic
             WHERE Id <B.ID
                 AND (ReMark LIKE 'Description=%')
             ORDER BY Id DESC
             )C
 )
 ,Y4 AS(
 SELECT A.*
     ,CAST(REPLACE(REPLACE(LTRIM(RTRIM(REPLACE(B.ReMark,'Index=',''))),CHAR(10),''),CHAR(13),'')AS INT)AS IndexId
     ,CAST(REPLACE(REPLACE(LTRIM(RTRIM(REPLACE(C.ReMark,'InterfaceIndex=',''))),CHAR(10),''),CHAR(13),'')AS INT)AS InterfaceIndex
 FROM Y3 A
 CROSS APPLY(SELECT TOP 1 ReMark
             FROM @Wmic
             WHERE Id >A.ID_B
                 AND (ReMark LIKE 'Index=%')
             )B
 CROSS APPLY(SELECT TOP 1 ReMark
             FROM @Wmic
             WHERE Id >A.ID_B
                 AND (ReMark LIKE 'InterfaceIndex=%')
             )C
 WHERE A.Desc1<>A.Desc2
 )
 ,Y5 AS(
 SELECT ROW_NUMBER()OVER(ORDER BY IndexId,InterfaceIndex)AS Rnt,*
 FROM Y4
 )
 ,Y6 AS(
 SELECT Rnt,IPAddress,Desc1,MACAddress
     ,CASE WHEN Rnt=1 THEN Desc2 ELSE Desc2+' _'+RTRIM(CAST(Rnt AS CHAR))END AS Desc2
 FROM Y5
 UNION
 SELECT 1,IPAddress,Desc1,MACAddress,Desc2
 FROM Y3
 WHERE Desc1=Desc2
 )
 ,Y7 AS(
 SELECT VarStr
 FROM TempDB.dbo.TempPerf(NOLOCK)
 WHERE VarStr LIKE '\network interface%'
     AND (VarStr LIKE '%Current Bandwidth'
         OR VarStr LIKE '%Bytes Received/sec'
         OR VarStr LIKE '%Bytes Sent/sec'
         OR VarStr LIKE '%Bytes Total/sec'
         OR VarStr LIKE '%Output Queue Length'
         OR VarStr LIKE '%Packets Outbound Discarded'
         OR VarStr LIKE '%Packets Outbound Errors'
         OR VarStr LIKE '%Packets Received Discarded'
         OR VarStr LIKE '%Packets Received Errors'
         )
 )
 ,Y8 AS(
 SELECT A.*,B.*
 FROM Y7 A
 INNER JOIN Y6 B ON 1=1
     AND REPLACE(REPLACE(A.VarStr,'[','('),']',')') LIKE '%('+B.Desc2+'%'
 )
 INSERT INTO TempDB.dbo.Perf(VarStr)
 SELECT DISTINCT VarStr
 FROM Y8
 --添加
 --INSERT INTO CONFIGDB.DBO.PerfConfigSetting
 --SELECT *FROM TempDB.dbo.Perf

 --删除计数器配置文件
 SELECT @CMD = 'DEL C:\Perf_'+@ServerName+'_请勿删除\首次部署_PerfConfigure_' + @ServerName + '_*.txt'
 EXEC @RetCode=MASTER.DBO.XP_CMDSHELL @CMD
 --导出计数器配置文件
 SELECT @Path = 'C:\Perf_'+@ServerName+'_请勿删除\首次部署_PerfConfigure_' + @ServerName + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19),Getdate(),21),'-',''),':',''),' ','') + '.txt'
 SELECT @CMD='BCP TEMPDB.DBO.Perf OUT ' + @Path + ' -T -c -CRAW -S' + @@SERVERNAME
 EXEC @RetCode=MASTER.DBO.XP_CMDSHELL @CMD

 --添加部署脚本
 TRUNCATE TABLE TEMPDB.DBO.TABLEOUT
 INSERT INTO TEMPDB.DBO.TABLEOUT(VARS)
 SELECT '@echo off'
 UNION all
 --SELECT 'echo 正在删除同名称Cliconfg...'
 --UNION all
 --SELECT 'reg delete  "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo" /v SQLPerforMance /f'
 --UNION all
 --SELECT 'echo -'
 --UNION all
 --SELECT 'echo 正在添加Cliconfg部署...'
 --UNION all
 --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'
 --UNION all
 --SELECT 'echo -'
 --UNION all
 SELECT 'echo 正在部署ODBC...'
 UNION all
 SELECT 'odbcconf CONFIGSYSDSN "Sql Server" "DSN=Perf_'+@ServerName+'|SERVER=10.195.0.136,55944|Database=SQLPerfData|Trusted_Connection=yes"'
 UNION all
 SELECT 'echo -'
 UNION all
 SELECT 'echo 正在停止已经存在的计数器...'
 UNION all
 SELECT 'Logman Stop Perf_'+@ServerName
 UNION all
 SELECT 'echo -'
 UNION all
 SELECT 'echo 正在删除已经存在的计数器...'
 UNION all
 SELECT 'Logman Delete Perf_'+@ServerName
 UNION all
 SELECT 'echo -'
 UNION all
 SELECT 'echo 正在部署新计数器...'
 UNION all
 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
 UNION all
 SELECT 'echo -'
 UNION all
 SELECT 'echo 正在初始化并启动计数器...'
 UNION all
 SELECT 'Logman Start Perf_'+@ServerName
 UNION all
 SELECT 'echo -'
 UNION all
 SELECT 'echo 完成计数器部署'
 UNION all
 SELECT 'echo -'
 UNION all
 SELECT 'echo 正在删除系统任务计划...'
 UNION all
 SELECT 'schtasks /delete /tn "[请勿删除]Perf_'+@ServerName+'_计数器自动启动" /f'
 UNION all
 SELECT 'echo -'
 UNION all
 SELECT 'echo 正在创建系统任务计划...'
 UNION all
 SELECT 'schtasks /create /sc MINUTE /mo 5 /st 00:05:00 /tn "[请勿删除]Perf_'+@ServerName+'_计数器自动启动" /tr C:\Perf_'+@ServerName+'_请勿删除\PerfAutoStart.vbs /ru "System"'
 --UNION all
 --SELECT 'echo 添加开机启动...'
 --UNION all
 --SELECT 'reg add HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Run /v Perf_'+@ServerName+'_计数器自动启动vbs /d '+@Path
 UNION all
 SELECT 'echo -'
 UNION all
 SELECT 'echo. & pause'

 --删除部署文件
 SELECT @CMD = 'DEL C:\Perf_'+@ServerName+'_请勿删除\首次部署_执行脚本_' + @ServerName + '_*.bat'
 EXEC @RetCode=MASTER.DBO.XP_CMDSHELL @CMD;
 --添加部署文件
 SELECT @Path = 'C:\Perf_'+@ServerName+'_请勿删除\首次部署_执行脚本_' + @ServerName + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19),Getdate(),21),'-',''),':',''),' ','') + '.bat'
 SELECT @CMD='BCP TEMPDB.DBO.TABLEOUT OUT ' + @Path + ' -T -c -CRAW -S' + @@SERVERNAME
 EXEC @RetCode=MASTER.DBO.XP_CMDSHELL @CMD;

 DECLARE @INFO VARCHAR(MAX)
 --群集环境
 IF  Serverproperty('ISClustered') = 1
   BEGIN
     DECLARE @CurNode VARCHAR(128)
     DECLARE @ALLNode VARCHAR(128)
     SELECT TOP 1 @CurNode=NodeName FROM sys.dm_os_cluster_nodes WITH(NOLOCK)
     WHERE is_current_owner=1
     SET @INFO='恭喜！！！成功在群集主节点服务器['+@CurNode+']生成部署文件。'+CHAR(10)+'请按以下步骤执行部署：'
     SELECT @INFO=@INFO
           +CHAR(10)+'部署节点['+NodeName+']:从主节点提取以上生成的部署文件'
           +CHAR(10)+'步骤一(部署)：在['+NodeName+']节点执行Bat文件 ：'+@path

           +CHAR(10)+'步骤二(检测_5分钟)：在[117-27-139-236]监控服务器上执行 ： EXEC [SQLPerfData].[dbo].[spb_PerfConfigure_Check] '''+NodeName+''''
           +CHAR(10)
     FROM sys.dm_os_cluster_nodes WITH(NOLOCK)
   END
 ELSE
 BEGIN
     SET @INFO='恭喜！！！成功在服务器本地生成部署文件！'+CHAR(10)+'请按以下步骤执行部署：'
       +CHAR(10)+'步骤一(部署)：在['+@ServerName+']服务器执行Bat文件 ：'+@path
       +CHAR(10)+'步骤二(检测_5分钟)：在[117-27-139-236]监控服务器上执行 ： EXEC [SQLPerfData].[dbo].[spb_PerfConfigure_Check] '''+@ServerName+''''
 END;
 THROW 50000,@INFO,1

spb_PerfConfigure

步骤三、部署性能计数器（按步骤二结果的步骤执行。执行首次部署的Bat文件。）

部署需要在当前节点上执行封装脚本，执行完成后，会输出制定的配置文件，如果是群集环境，则需要分别在每个相关节点执行此配置脚本。

如下结果，部署成功

步骤四、检测部署结果

 ALTER proc [dbo].[spb_PerfConfigure_Check]
 @MachineName sysname
 as
 --执行数据同步
 exec [SQLPerfDataStat].dbo.spb_Perf_Sync_Setting
 --declare @MachineName sysname='117-27-139-236'

 DECLARE @RES_T TABLE(CHECK_CLASS NVARCHAR(100),CHECK_INFO NVARCHAR(MAX),ERRORMSG NVARCHAR(MAX))
 DECLARE @MSG VARCHAR(MAX)
 SET @MSG=''
 --是否存在未添加计数器
 IF NOT EXISTS (
 SELECT TOP 1 1  FROM [SQLPerfData].dbo.CounterDetails WITH(NOLOCK)
 WHERE MachineName='\\'+@MachineName
 )
 BEGIN
  SELECT '启动情况' CHECK_CLASS,'计数器未启动' AS CHECK_INFO
 END
 ELSE
 BEGIN
  SELECT '启动情况' CHECK_CLASS,'计数器已启动' AS CHECK_INFO
 END

 --是否存在未添加计数器
 IF EXISTS(
 SELECT TOP 1 1
 FROM [SQLPerfDataStat].dbo.CounterTypeDetails  B
 LEFT JOIN (
 SELECT DISTINCT ObjectName,CounterName
 FROM [SQLPerfDataStat].dbo.CounterDetails_Collect
 WHERE MachineName=@MachineName) AA
 ON AA.ObjectName=B.ObjectName AND AA.CounterName=B.CounterName
 WHERE AA.ObjectName IS NULL
 )
 BEGIN
     SELECT @MSG=@MSG +B.ObjectName+'\'+B.CounterName +'; '
     FROM [SQLPerfDataStat].dbo.CounterTypeDetails  B
     LEFT JOIN (
     SELECT DISTINCT ObjectName,CounterName
     FROM [SQLPerfDataStat].dbo.CounterDetails_Collect
     WHERE MachineName=@MachineName) AA
     ON AA.ObjectName=B.ObjectName AND AA.CounterName=B.CounterName
     WHERE AA.ObjectName IS NULL 

     SELECT '对象添加情况' CHECK_CLASS,'未完整添加计数器' CHECK_INFO,@MSG as ERRORMSG
 END
 ELSE
 BEGIN
     SELECT '对象添加情况' CHECK_CLASS,'完整添加计数器' CHECK_INFO
 END

 --察看近五分钟收集情况
 DECLARE @NOWDATE DATETIME
 SET @NOWDATE=CONVERT(VARCHAR(16),GETDATE(),120)--当前时间
 WAITFOR DELAY '00:05:00'--5分钟之后

 DECLARE @I_COUNT INT
 DECLARE @C_COUNT INT
 SELECT @C_COUNT=COUNT(CounterID)
 FROM [SQLPerfDataStat].dbo.CounterDetails_Collect A
 WHERE A.MachineName=@MachineName
 DECLARE @I INT
 SET @I=0
 WHILE @I<5
 BEGIN
   SELECT @I_COUNT=COUNT(CounterID)
   FROM  [SQLPerfDataStat].[dbo].[CounterData_OneMinute] B
   WHERE  B.CounterDateTime=DATEADD(MI,-@I,@NOWDATE)
   AND B.CounterID IN(SELECT CounterID FROM [SQLPerfDataStat].dbo.CounterDetails_Collect A  WHERE A.MachineName=@MachineName)

   IF @C_COUNT=@I_COUNT
   BEGIN
     INSERT INTO @RES_T
     SELECT '第'+cast(@I+1 as varchar(20))+'分钟计数器收集情况' CHECK_CLASS,'收集正常' CHECK_INFO,'当前要求：'+CONVERT(VARCHAR(28),@C_COUNT)+'收集个数：'+CONVERT(VARCHAR(28),@I_COUNT)
   END
   ELSE
   BEGIN
     INSERT INTO @RES_T
     SELECT '第'+cast(@I+1 as varchar(20))+'分钟计数器收集情况' CHECK_CLASS,'收集不正常' CHECK_INFO,'当前要求：'+CONVERT(VARCHAR(28),@C_COUNT)+'收集个数：'+CONVERT(VARCHAR(28),@I_COUNT)
   END

   SET @I=@I+1
 END
 SELECT * FROM @RES_T

spb_PerfConfigure_Check

可能需要执行五分钟。后面几分钟收集即可认为是正常。