powershell to get sql server memory counters and show value 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 &quot;*server memory*&quot;} |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 &quot;*Databases&quot;).Counter | Where {$_ -like &quot;*\Transactions/sec&quot;} #this returns nothing
#   $listofmetrics = (Get-Counter -ListSet &quot;*Databases&quot;).Counter | Where {$_ -like &quot;*\Transactions/sec&quot;}
#   $listofmetrics | Get-Counter
}

clear

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

thanks in advance
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