http://www.sqlservercentral.com/blogs/dave_ballantynes_blog/2009/11/30/my-first-useful-powershell-script-_1320_-capturing-performance-counters/

Printed 2014/07/29 05:27PM

My first useful powershell script – capturing performance counters

By Dave Ballantyne, 2009/11/30

After playing around with powershell a bit, I managed to do something quite useful.  There are a multitude of ways to capture performance counters but I think this will have the advantage of being able to be fired by SqlAgent (or another timer process) every X many seconds.  All you now need to do is process the data within Excel (or power pivot as shown by David Castro  here ).  For every server in servers.txt it will collect the counters in counters.txt

$Servers = get-content c:\servers.txt
$CounterList = Get-Content c:\counters.txt


$sw = new-object system.IO.StreamWriter("c:\perf.res",1)
$Counters = $CounterList | Get-Counter -computer $Servers
foreach($counter in $counters){
$counter.ToString()
foreach($sampleset in $counter.CounterSamples){
$sw.writeline($sampleset.Timestamp.ToString()+','+$sampleset.Path + ',' +$sampleset.CookedValue )
}
}

$sw.close()

 

Example Counters.Txt

\Memory\Available MBytes
\Paging File(_total)\% Usage
\PhysicalDisk(_total)\% Disk Time
\PhysicalDisk(_total)\Avg. Disk Bytes/Read
\PhysicalDisk(_total)\Avg. Disk Bytes/Write
\PhysicalDisk(_total)\Disk Reads/Sec
\PhysicalDisk(_total)\Disk Writes/Sec
\SqlServer:Buffer Manager\Buffer cache hit ratio
\SqlServer:Buffer Manager\Page life expectancy
\SqlServer:General Statistics\User Connections
\SqlServer:Memory Manager\Memory Grants Pending
\System\Processor Queue Length

Example Servers.Txt

Server1
Server2

Enjoy


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.