Performance benchmarking with Powershell

I’ve been working on a performance benchmarking project recently to gauge the effect of new releases to our systems. Powershell happens to be very useful gathering various system statistics using the Get-Counter cmdlet.

There’s literally a tonne of available counters for everything to disk use, memory usage to sql server specific counters. You can list the ones available on a system with the below command.

Get-Counter -ListSet "*";

To list all SQL Server specific counters…

?View Code POWERSHELL
Get-Counter -ListSet "*sql*";

The amount of data generated quickly becomes unmanageable so it’s best to write the info to a csv file. Then the data can then be imported into a database or excel for further analysis.

I found this to be a little tricky so I’ve included a script here that will produce a suitable file. The script below will run a set of counters, at a specified interval, and produce a csv file for each sample. A few variables need to be modified.

  • $computer – Set this to the computer you wish to gather performance counters for.
  • $loadFlag – This will be appended as an extra column to the csv file, I use this for uniquely tagging each session to compare data from different sessions.
  • $sampleInterval – Number of seconds between each sample in the session.
  • $maxSamples – The number of samples to take in the session.
  • $path – This is where the csv files will be output.

There’s a few sample counters included in the script. Be sure to research the available counters to include the ones appropriate to you.

?View Code POWERSHELL
# CPU0 Metrics
$listOfMetrics += ("\processor(0)\% processor time", "\processor(0)\% idle time");
# CPU1 Metrics
$listOfMetrics += ("\processor(1)\% processor time", "\processor(1)\% idle time");
# Memory Metrics
$listOfMetrics += ("\memory\page faults/sec", "\memory\available mbytes");
# Paging File Metrics (Totals)
$listOfMetrics += ("\paging file(_total)\% usage", "\paging file(_total)\% usage peak");
# process Metrics
$listOfMetrics += ("\process(idle)\% processor time","\process(sqlservr)\% processor time");
 
$computer = "rhys-VAIO";
$loadFlag = 1; # Unique id for each load test session CHANGE THIS!!!!
$sampleInterval = 10; # seconds between each sample
$maxSamples = 10; # Number of samples to take
$path = "C:\Users\Rhys\Desktop\output\output_"; # Output path. Don't put the extension as it's done below.
 
$metrics = Get-Counter -ComputerName $computer -Counter $listOfMetrics -SampleInterval $sampleInterval -MaxSamples $maxSamples;
 
$count = 1; # Count for numbering each individual file
foreach($metric in $metrics)
{
	$obj = $metric.CounterSamples | Select-Object -Property Path, CookedValue, Timestamp;
	$obj | Add-Member -MemberType NoteProperty -Name LoadFlag -Value $loadFlag -Force; # Add a new column to the csv for loadFlag
	$obj | Export-Csv -Path "$path$count.txt" -NoTypeInformation;
	$count += 1; # Increment filename counter
}

After successful execution check your output directory for the files. If all has gone well they should look something like below.

"Path","CookedValue","Timestamp","LoadFlag"
"\\rhys-vaio\processor(0)\% processor time","3.90531358130328","01/12/2010 20:51:02","1"
"\\rhys-vaio\processor(0)\% idle time","96.0946864186967","01/12/2010 20:51:02","1"
"\\rhys-vaio\processor(1)\% processor time","0.629358362484078","01/12/2010 20:51:02","1"
"\\rhys-vaio\processor(1)\% idle time","99.3706416375159","01/12/2010 20:51:02","1"
"\\rhys-vaio\memory\page faults/sec","782.099537446347","01/12/2010 20:51:02","1"
"\\rhys-vaio\memory\available mbytes","961","01/12/2010 20:51:02","1"
"\\rhys-vaio\paging file(_total)\% usage","21.5294152137404","01/12/2010 20:51:02","1"
"\\rhys-vaio\paging file(_total)\% usage peak","21.9042513538076","01/12/2010 20:51:02","1"
"\\rhys-vaio\process(idle)\% processor time","389.370677436797","01/12/2010 20:51:02","1"
"\\rhys-vaio\process(sqlservr)\% processor time","0","01/12/2010 20:51:02","1"

All to be done now is to wrestle with Excel pivot tables to make sense of this data!


6 Comments

  1. Hi Rhys, the script seems to work great if I don’t specify -ComputerName for Get-Counter. As soon as I do (even if it is the name of the local computer) I get:

    Get-Counter : Unable to connect to the specified computer or the computer is offline.
    At line:8 char:23
    + $metrics = Get-Counter <<<< -ComputerName $computer -Counter $listOfMetrics -SampleInterval $sampleInterval -MaxSamples $maxSamples;
    + CategoryInfo : InvalidResult: (:) [Get-Counter], Exception
    + FullyQualifiedErrorId : CounterApiError,Microsoft.PowerShell.Commands.GetCounterCommand

    I know the counters are working because, again, if I leave the -ComputerName parameter out, the script works as advertised. There seems to be something blocking the use of the parameter.

    Did you test this explicitly using $computer = "rhys-vaio" or did you just leave the -ComputerName parameter out when you were testing? (The output would be the same – I'm curious because in the script the -ComputerName parameter has unexpected formatting.) Have you come across this error before?

    Thanks,
    Aaron

  2. Rhys says:

    Hi Aaron,

    I did this at work with the -ComputerName parameter but it craps out on my home laptop. All was fine when executing against remote servers. At work I was running on Win 7 64 Bit Enterprise VM (I think). What OS are you trying on?

    I may be jumping the gun here but I seem to be running into a lot of 32 / 64 bit issues at the moment. Something I’d be keen to nail as I’m due to push this out soon.

    Cheers,

    Rhys

  3. Hi Rhys,

    Fails on Win7 x64 Ultimate (with PowerShell v2.0 installed) but succeeds on 2008 R2 x64 (when used at a v1.0 prompt).

  4. Rhys says:

    Hi Aaron,

    I think this is perhaps a bug with the V2 version of Get-Counter

    All of these work…

    Get-Service –ComputerName “rhys-vaio”;
    Get-WmiObject -ComputerName “rhys-vaio” -Class Win32_LogicalDisk;
    Get-Process -ComputerName “rhys-vaio”;
    Get-Counter -ComputerName “rhys-vaio” -ListSet “*”;

    The following do not work…

    $counter = “\processor(*)\% processor time”;
    $counter | Get-Counter -ComputerName “rhys-vaio”;

    Get-Counter -ComputerName “rhys-vaio” -Counter “\processor(*)\% processor time”;

    Having said that I’m using Windows 7 at work and didn’t that ship with Powershell V2?

    Rhys

  5. […] become better at PowerShell" as one of my goals for this year. I searched around and quickly found this post from Rhys Cambell (blog | twitter). He was collecting a set of counter samples every n seconds and […]

Leave a Reply