SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

My first useful powershell script – capturing performance counters

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){
foreach($sampleset in $counter.CounterSamples){
$sw.writeline($sampleset.Timestamp.ToString()+','+$sampleset.Path + ',' +$sampleset.CookedValue )



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




No comments.

Leave a Comment

Please register or log in to leave a comment.