SQL Server Automation Operations series-Monitoring Performance metrics script (Power Shell)

Requirements Description

Generally in the production environment, sometimes need to automatically detect the status of the indicator value, if there is an exception, need early warning, such as e-mail notification, this article is introduced if the power shell to achieve status value monitoring

Range of monitoring values

Based on experience, as a DBA it is generally necessary to monitor the following system performance indicators.

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 Manag Er\page life expectancy \sqlserver:buffer Manager\lazy writes/sec \sqlserver:memory manager\memory Grants Pending \sqlserver:memory manager\target Server Memory (KB) \sqls Erver:memory manager\total Server Memory (KB) Disk: \physicaldisk (_total) \%Disk Time \physicaldisk (_total) \current Disk Queue Length \physicaldisk (_total) \avg. Disk Queue Length \phys Icaldisk (_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>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

The above indicator meaning, you can refer to my previous article:SQL Server needs to monitor which counters

Monitoring scripts

$server ="(local)"$uid="SA"$db="Master"$pwd="Password"$mailprfname="SendEmail"$recipients="[email protected]"$subject="database metrics are out of the ordinary! "$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-eq1) {$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($CPinch$names) {$p= New-object collections.generic.list[string] $report=""    foreach($PFCinch$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""{#生产警告 parameter counter, threshold, current value Createalter $report}}

There are 2 configuration files involved: Computernamexml,alter_cpuxml:

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

<Counters> <counter alter ="Ten" operator="GT">\processor (_total) \% Processor time</counter> <counter alter ="Ten" operator="GT">\processor (_total) \% privileged time</counter> <counter alter ="Ten" operator="GT">\sqlserver:sql statistics\batch requests/sec</counter> <counter alter ="Ten" operator="GT">\sqlserver:sql statistics\sql compilations/sec</counter> <counter alter ="Ten" operator="GT">\sqlserver:sql statistics\sql re-compilations/sec</counter> <counter alter ="Ten" operator="LT">\system\processor Queue length</counter> <counter alter ="Ten" operator="LT">\system\context switches/sec</counter></counters>

Where alter is the threshold, such as the first one, a warning is issued if the threshold value > Performance counter value.

In fact, this kind of custom configuration, realize the flexible and changeable automatic monitoring standard:

1, such as can detect the size of disk space

2. Peak status of detection operation

3, timing according to historical operating values, change the valve size in the production system, that is, the so-called operating baseline

Warning Implementation method

1, SQL Agent configuration job method implementation

2. Scheduled Tasks

The above two configurations, can be flexibly mastered, the operation is quite simple, if not, can self Google. Of course, if you do not want to intervene in the normal production system, you can add a server dedicated to automating operational inspection to enable remote monitoring.

Subsequent articles will analyze remote calls to the power shell and be able to implement the current state of the incident, automating .... Auto Send Email ... For DBA onsite Forensics first-hand ... Easy to diagnose problems ...

As follows

The above only provides implementation methods, such as the need for content updates, their own flexible update.

Script Http://files.cnblogs.com/zhijianliutang/DBALter.zip

SQL Server Automation Operations series-Monitoring Performance metrics script (Power Shell)