SQL Server自动化运维系列：监控性能指标脚本

需求描述

一般在生产环境中，有时候需要自动的检测指标值状态，如果发生异常，需要提前预警的，比如发邮件告知，本篇就介绍如果通过Power shell实现状态值监控。

监控值范围

根据经验，作为DBA一般需要监控如下系统能行指标。

cpu：                    \Processor(_Total)\% Processor Time                  \Processor(_Total)\% Privileged Time                    \SQLServer:SQL Statistics\Batch Requests/sec                  \SQLServer:SQL Statistics\SQL Compilations/sec                  \SQLServer:SQL Statistics\SQL Re-Compilations/sec                  \System\Processor Queue Length                  \System\Context Switches/sec            Memory:                    \Memory\Available Bytes                  \Memory\Pages/sec                  \Memory\Page Faults/sec                  \Memory\Pages Input/sec                  \Memory\Pages Output/sec                  \Process(sqlservr)\Private Bytes                  \SQLServer:Buffer Manager\Buffer cache hit ratio                  \SQLServer:Buffer Manager\Page life expectancy                  \SQLServer:Buffer Manager\Lazy writes/sec                  \SQLServer:Memory Manager\Memory Grants Pending                  \SQLServer:Memory Manager\Target Server Memory (KB)                  \SQLServer:Memory Manager\Total Server Memory (KB)            Disk:                    \PhysicalDisk(_Total)\% Disk Time                  \PhysicalDisk(_Total)\Current Disk Queue Length                  \PhysicalDisk(_Total)\Avg. Disk Queue Length                  \PhysicalDisk(_Total)\Disk Transfers/sec                  \PhysicalDisk(_Total)\Disk Bytes/sec                  \PhysicalDisk(_Total)\Avg. Disk sec/Read                  \PhysicalDisk(_Total)\Avg. Disk sec/Write            SQL Server:                    \SQLServer:Access Methods\FreeSpace Scans/sec                  \SQLServer:Access Methods\Full Scans/sec                  \SQLServer:Access Methods\Table Lock Escalations/sec                  \SQLServer:Access Methods\Worktables Created/sec                  \SQLServer:General Statistics\Processes blocked                  \SQLServer:General Statistics\User Connections                  \SQLServer:Latches\Total Latch Wait Time (ms)                  \SQLServer:Locks(_Total)\Lock Timeouts (timeout &gt; 0)/sec                  \SQLServer:Locks(_Total)\Lock Wait Time (ms)                  \SQLServer:Locks(_Total)\Number of Deadlocks/sec                  \SQLServer:SQL Statistics\Batch Requests/sec                  \SQLServer:SQL Statistics\SQL Re-Compilations/sec

上述指标含义，可以参照我上一篇文章：SQL Server需要监控哪些计数器

监控脚本

$server = "(local)"  $uid = "sa"  $db="master"  $pwd="password"  $mailprfname = "SendEmail"  $recipients = "787449667@qq.com"  $subject = "数据库指标异常了！"  $computernamexml = "f:\computername.xml"  $alter_cpuxml = "f:\alter_cpu.xml"  function GetServerName($xmlpath)  {      $xml = [xml] (Get-Content $xmlpath)      $return = New-Object Collections.Generic.List[string]      for($i = 0;$i -lt $xml.computernames.ChildNodes.Count;$i++)      {          if ( $xml.computernames.ChildNodes.Count -eq 1)          {              $cp = [string]$xml.computernames.computername          }          else          {              $cp = [string]$xml.computernames.computername[$i]          }          $return.Add($cp.Trim())      }      $return  }    function GetAlterCounter($xmlpath)  {      $xml = [xml] (Get-Content $xmlpath)      $return = New-Object Collections.Generic.List[string]      $list = $xml.counters.Counter      $list  }    function CreateAlter($message)  {      $SqlConnection = New-Object System.Data.SqlClient.SqlConnection       $CnnString ="Server = $server; Database = $db;User Id = $uid; Password = $pwd"       $SqlConnection.ConnectionString = $CnnString       $CC = $SqlConnection.CreateCommand();       if (-not ($SqlConnection.State -like "Open")) { $SqlConnection.Open() }         $cc.CommandText=" EXEC msdb..sp_send_dbmail                @profile_name  = '$mailprfname'              ,@recipients = '$recipients'              ,@body = '$message'              ,@subject = '$subject'  "       $cc.ExecuteNonQuery()|out-null       $SqlConnection.Close();  }    $names = GetServerName($computernamexml)  $pfcounters = GetAlterCounter($alter_cpuxml)  foreach($cp in $names)  {      $p = New-Object Collections.Generic.List[string]      $report = ""      foreach ($pfc in $pfcounters)      {          $b = ""          $counter ="\\"+$cp+$pfc.get_InnerText().Trim()          $p.Add($counter)        }      $count = Get-Counter $p      for ($i = 0; $i -lt $count.CounterSamples.Count; $i++)      {          $v = $count.CounterSamples.Get($i).CookedValue          $pfc = $pfcounters[$i]          #$pfc.get_InnerText()          $b = ""          $lg = ""          if($pfc.operator -eq "lt")          {              if ($v -ge [double]$pfc.alter)                  {$b = "alter"                  $lg = "Greater Than"}          }          elseif ($pfc.operator -eq "gt")          {              if( $v -le [double]$pfc.alter)                  {$b = "alter"                  $lg = "Less Than"}          }          if($b -eq "alter")          {              $path = "\\"+$cp+$pfc.get_InnerText()                $item = "{0}:{1};{2} Threshold:{3}" -f $path,$v.ToString(),$lg,$pfc.alter.Trim()              $report += $item + "`n"          }        }      if($report -ne "")      {          #生产警告 参数 计数器，阀值，当前值          CreateAlter $report      }  }

其中涉及到2个配置文件：computernamexml,alter_cpuxml分别如下：

<computernames>          <computername>                  wuxuelei-pc          </computername>  </computernames>

<Counters>          <Counter alter = "10" operator = "gt" >\Processor(_Total)\% Processor Time</Counter>          <Counter alter = "10" operator = "gt" >\Processor(_Total)\% Privileged Time</Counter>          <Counter alter = "10" operator = "gt" >\SQLServer:SQL Statistics\Batch Requests/sec</Counter>          <Counter alter = "10" operator = "gt" >\SQLServer:SQL Statistics\SQL Compilations/sec</Counter>          <Counter alter = "10" operator = "gt" >\SQLServer:SQL Statistics\SQL Re-Compilations/sec</Counter>          <Counter alter = "10" operator=  "lt" >\System\Processor Queue Length</Counter>          <Counter alter = "10" operator=  "lt" >\System\Context Switches/sec</Counter>  </Counters>

其中 alter 就是阀值，如第一条，如果阀值 > 性能计数器值，就会发出警告。

其实这种自定义配置的方式，实现了灵活多变的自动化监控标准：

1、比如可以检测磁盘空间大小

2、检测运行峰值状态

3、定时的根据历史运行值，更改生产系统中的阀值大小，也就是所谓的运行基线