Reading SQL Performance Counters using PowerShell script

Question

I'm trying create a PS script to read some performance counter about my SQL for my Level 1 partners but I'm having a problem with the instance name.

I'm using this code:

$computer = "\\\" + $ENV:Computername
$instance = $computer + '\MSSQL$SG25DB'
write-Host $instance
Write-Output -InputObject "Use of Memory Buffer. The expected value must be greater than 80."
Get-Counter "$instance:Buffer Manager\Buffer cache hit ratio"

and the output is:

\\\\[hostname]\\MSSQL$SG25DB <--- Here appears the proper value :)
Use of Memory Buffer. The expected value must be greater than 80.
Get-Counter : The specified counter path could not be interpreted. At
line:5 char:12
+ Get-Counter <<<< "$instance:Buffer Manager\Buffer cache hit ratio"
+ CategoryInfo : InvalidResult: (:) [Get-Counter], Exception
+ FullyQualifiedErrorId : CounterApiError,Microsoft.PowerShell.Commands.GetCounterCommand

I have verified with Write-Host that the value of variable $instance is properly constructed but when I apply it to the Get-Counter command its fails. Do you have any idea? Could be related with the special character $? How can I make run it properly?

• Kin Shah: Thomas has answered your question. This will help you out as well .. sqlblog.org/2011/05/03/…
• Thomas Stringer: @Kin The problem the OP is having isn't with the dollar sign interpretation in the instance name. The OP correctly uses the literal string annotations with the single quotes. That propagates over to the variable's reference in Get-Counter. Two separate problems as far as I can see.
• Simkin: Hi Thomas, I have tested your suggestion. As you say with write-Host "$($instance):Buffer Manager\Buffer cache hit ratio" the value shown is the proper, but when I use this chain (Get-Counter "$($instance):Buffer Manager\Buffer cache hit ratio") the same problem appears: Get-Counter : The specified object was not found on the computer. At line:6 char:12 + Get-Counter <<<< "$($instance):Buffer Manager\Buffer cache hit ratio" + CategoryInfo : InvalidResult: (:) [Get-Counter], Exception + FullyQualifiedErrorId : CounterApiError,Microsoft.PowerShell.Commands.GetCounterCommand

Answer 1

Try this:

Get-Counter "$($instance):Buffer Manager\Buffer cache hit ratio"

What happens is that your $instance variable isn't isolated, so therefore you need to explicitly state what the variable is and where it ends. You can do that with the above notation (dollar sign and parenthesis around the variable name).

Take a look at the different outputs of the following:

Write-Host "$instance:Buffer Manager\Buffer cache hit ratio"

The output is:

 Manager\Buffer cache hit ratio

Now with this correct notation:

Write-Host "$($instance):Buffer Manager\Buffer cache hit ratio"

The output is this:

\\MyMachineName\MSSQL$InstanceName:Buffer Manager\Buffer cache hit ratio

A common way to troubleshoot these sorts of problems is to use the above methodology, by seeing exactly what you're passing into other cmdlets/functions/etc.

Answer 2

I suggest you the script you can find on this page.

Set-ExecutionPolicy -Scope Process -ExecutionPolicy Unrestricted -Force
$ErrorActionPreference = "Stop"
$VerbosePreference = "Continue"
cls
Write-Output "Collecting counters..."
Write-Output "Press Ctrl+C to exit."
$counters = @("\Processor(_Total)\% Processor Time", 
"\LogicalDisk(_Total)\Disk Reads/sec", 
"\LogicalDisk(_Total)\Disk Writes/sec", 
"\SQLServer:Databases(_Total)\Log Bytes Flushed/sec") 
Get-Counter -Counter $counters -SampleInterval 1 -MaxSamples 3600 | 
 Export-Counter -FileFormat csv -Path "C:\sql-perfmon-log.csv" -Force