A little PowerShell tip: Named instances
May 3rd, 20113
A little PowerShell tip: Named instances
May 3rd, 20113

This afternoon I was working on a little PowerShell script to collect some PerfMon counters. I borrowed some code from myself, and had counters represented in strings like the following (this is a greatly simplified version):

Get-Counter -Counter "\SQLServer:Wait Statistics(Average wait time (ms))\Lock waits"

The above code is for a default instance, but in this case I was dealing with a named instance (SQL2008R2). So I simply replaced "SQLServer:" with "MSSQL$SQL2008R2":

 Get-Counter -Counter "\<b>MSSQL$SQL2008R2</b>:Wait Statistics(Average wait time (ms))\Lock waits"

When I ran this code, I received the following error:

Get-Counter : The specified object was not found on the computer. At line:1 char:12 + Get-Counter <<<< -Counter "\MSSQL$SQL2008R2:Wait Statistics(*)\Lock waits"
  + CategoryInfo : InvalidResult: (:) [Get-Counter], Exception
  + FullyQualifiedErrorId : CounterApiError,Microsoft.PowerShell.Commands.GetCounterCommand

The PowerShell gurus will spot the problem instantly, but play along, okay? I verified the format of the object, instance and counter name using Performance Monitor, going so far as validating exact case:

I even copied the properties to Notepad to make sure I wasn't being dumb and transcribing incorrectly:

I quickly searched for examples of folks using PowerShell to pull performance counter data from named instances of SQL Server, but either there aren't many out there, or my Google-fu was weak today.

I am not sure exactly why, but I looked at the Get-Counter line again, and the $ sign jumped out at me. Time is money; not sure if there is a correlation there. Anyway, when I properly escaped the dollar sign with a backward apostrophe (grave accent), it worked just fine, even though it looks funny:

Get-Counter -Counter "\MSSQL`$SQL2008R2:Wait Statistics(Average wait time (ms))\Lock waits"

Of course, looking back, it's blindingly obvious. I just won't get those 15 minutes back, and hope to save someone else the same 15 minutes.

[The error message does not seem to indicate that it tried to substitute the variable with anything, or that the counter wasn't found because the name wasn't complete. So I spent most of my time chasing other avenues. I'm going to leave that rabbit hole for another day, as I spent more time writing this post than solving the original issue.]

By: Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a husband, a father of two, a huge hockey and football fan, and my pronouns are he/him.

3 Responses

  1. Ryan says:

    I encountered the same issues and took almost 1 hour for troubleshooting.
    The problem got fixed through your post.
    Thanks a lot. You do save me lots of time.

  2. Rhys says:

    Using single quotes around your string or here-strings will also work for this.
    i.e. Write-Host 'This will print all $dollar signs';
    $string = @'
    This will print all $dollar signs
    Write-Host $string;

  3. unclebiguns says:

    Interesting.  I'm not a PoSH guy, but I noticed the $ and wondered if that was the problem because I know that it is a variable indicator.