Collecting Current Performance Monitor Data on Remote Servers with PowerShell

As an admin of systems, people will come to me on a regular basis an ask me “so and so server is slow, what’s wrong with it??”, as if expecting me to have that information right in front of me. If you are lucky, you may have monitoring software that records your historical data for you, and you can log in, and take a look. If, however, you don’t, or the server in question isn’t deemed important enough to warrant that costly third party license fee, you may need to collect some data right away, to see if thing are looking iffy.

This script will create a function that will allow you to log in to you PowerShell IDE of choice, and pull back some current perfmon data in a couple of minutes (depending on how much you want to collect), and then provide you with an aggregated output of the data points from your server.


function Get-PerformanceCounters
param([String]$Instance, [Int]$samples, [Int]$Interval )

#our output array for the raw data
$outputArr = @()

#set the required format for counter data SQL metrics
if ($Instance.Contains("\"))
$srvr = $Instance.Split("\")
$server = $srvr[0]
$instanceName = $srvr[1]
$server = $Instance
$sqlinstance = "SQLServer"

#build an array of the required counters
$counterCollection = @(
"\Physicaldisk(*)\Avg. Disk Queue Length",
"\Physicaldisk(*)\Avg. Disk sec/Read",
"\Physicaldisk(*)\Avg. Disk sec/Write",
"\Physicaldisk(*)\Avg. Disk Sec/Transfer",
"\Physicaldisk(*)\Disk Reads/sec",
"\Physicaldisk(*)\Disk Writes/sec",
"\Physicaldisk(*)\Disk Bytes/sec",
"\Memory\Page Reads/sec",
"\Memory\Available MBytes",
"\Paging File(_Total)\% Usage",
"\Processor(_Total)\% Processor Time",
"\System\Processor Queue Length",
"\$($sqlinstance):General Statistics\User Connections",
"\$($sqlinstance):Access Methods\Page Splits/sec",
"\$($sqlinstance):Access Methods\Full Scans/sec",
"\$($sqlinstance):Buffer Manager\Page life expectancy",
"\$($sqlinstance):Buffer Manager\Buffer cache hit ratio",
"\$($sqlinstance):Memory Manager\Memory Grants Pending",
"\$($sqlinstance):Memory Manager\Target Server Memory (KB)",
"\$($sqlinstance):Memory Manager\Total Server Memory (KB)",
"\$($sqlinstance):SQL Statistics\Batch Requests/sec",
"\$($sqlinstance):SQL Statistics\SQL Compilations/sec",
"\$($sqlinstance):SQL Statistics\SQL Re-Compilations/sec",
"\$($sqlinstance):Locks(_Total)\Lock Requests/sec",
"\$($sqlinstance):Locks(_Total)\Number of Deadlocks/sec",
"\$($sqlinstance):Wait Statistics(Waits in progress)\Page IO latch waits",
"\$($sqlinstance):Wait Statistics(Average wait time (ms))\Page IO latch waits",
"\$($sqlinstance):Wait Statistics(Waits in progress)\Page latch waits",
"\$($sqlinstance):Wait Statistics(Average wait time (ms))\Page latch waits",
"\$($sqlinstance):Wait Statistics(Waits in progress)\Network IO waits",
"\$($sqlinstance):Wait Statistics(Average wait time (ms))\Network IO waits"

#pull the raw data using default PS function and assign to an object
$counters = Get-Counter -ComputerName $server -Counter $counterCollection -SampleInterval $Interval -MaxSamples $samples
foreach($counter in $Counters.CounterSamples)
#bind the counter details to a variable
$counterPath = $counter.Path;
$counterInstance = $counter.InstanceName;
$counterValue = "{0:N4}" -f ($counter.CookedValue);

#create an object for all values
$properties = @{ `
'counterPath'=$counterPath; `
'counterInstance'=$counterInstance; `

$readingObject = New-Object -TypeName PSObject -Prop $properties

#add the current counter to our main data set
$outputArr += $readingObject

#aggregate and output our values.
$outputArr | Group-Object CounterPath | %{
New-Object psobject -Property @{
CounterPath = $_.Name
Sum = "{0:N2}" -f(($_.Group | Measure-Object CounterValue -Sum).Sum)
Min = "{0:N2}" -f(($_.Group | Measure-Object CounterValue -Min).Minimum)
Max = "{0:N2}" -f(($_.Group | Measure-Object CounterValue -Maximum).Maximum)
Average = "{0:N2}" -f(($_.Group | Measure-Object CounterValue -Average).Average)
} | select CounterPath, Min, Max, Average | ft -AutoSize

Thanks for reading.

This entry was posted in Powershell, SQL Server and tagged , , . Bookmark the permalink.

One Response to Collecting Current Performance Monitor Data on Remote Servers with PowerShell

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s