Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

migrated from stackoverflow.com Sep 1 '11 at 14:07

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 6 down vote accepted

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.

EDIT: See if this is what you need:


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

Edit2 Added -ComputerName

share|improve this answer
    
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. –  Shawn Melton 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. –  Shawn Melton 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 –  Shawn Melton Sep 1 '11 at 3:44
show 4 more comments

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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