9

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

7

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
9
  • thanks for this... But the main problem I am having is adding additional filters to the counters e.g. If I run the following: $listOfMetrices=(Get-Counter -ListSet "*Databases" -ComputerName $Hostname | where {$_.paths -like "*\transactions/sec"} ) $listOfMetrices | get-counter It shows me all counters under database category...but what I really want to see is transactions/sec for each database. Please help
    – Manjot
    Aug 31 '11 at 1:01
  • adjusted answer.
    – user507
    Aug 31 '11 at 15:44
  • Thanks Shawn, I tried using the code you updated. but, it is not able to find any counters now. (Get-Counter -ListSet "*Databases").Counter | Where {$_ -like "*\Transactions/sec"} doesn't find any counter. thanks for your time
    – Manjot
    Aug 31 '11 at 23:31
  • really? What version of SQL Server are you working with? I only tried this on a Window Server 2008 R2, SQL Server 2008 R2.
    – user507
    Sep 1 '11 at 3:43
  • 1
    I also just tried on my Window Server 2003, SQL 2005 and it returns a single counter of: \SQLServer:Databases(*)\Transactions/sec
    – user507
    Sep 1 '11 at 3:44
0

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 :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.