powershell to get sql server memory counters and show value

Question

I am writing a powershell script to capture the following SQL server counters:

SQL Server: Memory Manager: Total Server Memory (KB)

SQL Server: Memory Manager: Target Server Memory (KB)

My machine has 3 instances of SQL servers, so I want this script to capture all counters dynamically and report the value for 1 sample only. i tried writing the following:

Get-counter -List *SQL*Memory* | Select paths, counter | format-list # doesn't display full list

Get-counter -List *SQL*Memory* | Select paths, counter | where {_.counter -like "*server memory*"} |format-list # displays nothing

eventually I want to run this across multiple servers with -computername parameter and hence I want it to capture dynamically.

Can anyone please help me in finding what is missing? Following is the exact script that I am running:

Function checkTransactionsPerSecond([string] $Hostname )
{ 
    (Get-Counter -ListSet "*Databases").Counter | Where {$_ -like "*\Transactions/sec"} #this returns nothing
#   $listofmetrics = (Get-Counter -ListSet "*Databases").Counter | Where {$_ -like "*\Transactions/sec"}
#   $listofmetrics | Get-Counter
}

clear

    
foreach ($Hostname in Get-Content "D:\TEMP\machines.txt")
{
    Write-Host $Hostname
    checkTransactionsPerSecond($Hostname) 
}

thanks in advance

Answer 1

Aaron Bertrand wrote a good post on it that is pretty detailed...How I use PowerShell to collect Performance Counter data.

Then Laerte Junior has an excelent walk through on how he finds the counters he wants in a Simple-Talk article: Gathering Perfmon Data with Powershell. This might be where you want to start. It has some cmdlets that he uses to capture the counters for a particular instance I believe.

See if this is what you need:

$listofmetrics = (Get-Counter -ListSet "*Databases" -ComputerName $hostname).Counter | Where {$_ -like "*\Transactions/sec"}
$listofmetrics | Get-Counter

Answer 2

Check this out:

$listofmetrics = Get-Counter -ListSet "*Databases" | Get-Counter -MaxSamples 1 | Select -ExpandProperty CounterSamples | Where {$_.path -like "\Transactions/sec"} | Select Path, CookedValue

This will output a list of databases and their associated \Transactions/sec. I'm getting errors on my side at the beginning of the search but I assume it's a permissions issue. Otherwise it works like a beauty. You could use Regex to clean it up if you like :)