Displaying SQL Server Instance Name on Get-Process and Get-Counter

A great friend asked to me a simple and effective way to display the top high CPU in his servers. Let´s go :

Get-Process has a bug when using remotely with the –computername parameter , the CPU column does not show. You can  use it with Invoke-Command or scripting by WMI to avoid this issue. (Check out –> Quick Post–Troubleshooting SQL Server with PowerShell–Top 5 CPU Process by Server)

In my day to day I do prefer to use Get-Counter.  Let´s see what we can do : (in my case I am using Obiwan as my remote computer)

The counter to use is \process(<processname>)\% processor time

1 – Get all process

   1: Get-Counter –ComputerName Obiwan'\process(*)\% processor time' 


The return it is not so friendly , for me to see this and nothing is the same stuff



2 – Get all process expanding CounterSamples :

To start to get a better display, lets expand the CounterSamples property

   1: Get-Counter -ComputerName obiwan  '\process(*)\% processor time' | | select -expand Countersamples




Now lets get the top 5 high process :

   1: Get-Counter -ComputerName obiwan  '\process(*)\% processor time' `

   2: | select -expand Countersamples `

   3: | sort cookedvalue -Descending `

   4: | select -First 5




We can do it continuosly , but we cannot use the  –continuous parameter, we need to just do it in a while:

   1: while ($true) {

   2:     Get-Counter -ComputerName obiwan  '\process(*)\% processor time'  `

   3:     | select -expand Countersamples `

   4:     | sort cookedvalue -Descending `

   5:     | select -First 5

   6:     Write-Host "`n"

   7: }




But are you seeing the problem ? I have more than a default Instance in my server. So How Can I know which SQL Server Instance is ?


Then I wrote 2 functions : Get-RegexProcessName and Get-ProcessName.

(MANY THANKS to the  Jedi  Shay Levy (@shaylevy)  and Ted Krueger (@onpnt) with the REGEX Help guys)

Get-ProcessName Flow

The Get-Counter cmdlet, when expading the property CounterSamples, we have the property path. This property show what counter you are using with the instanceName (Counter Instance name, not SQL Server). So If I have 2 SQL Server instances , and run only to SQL Server counters, my output will be :

   1: Get-Counter -ComputerName obiwan  '\process(sqlservr*)\% processor time'`

   2: |select -expand Countersamples `

   3: | select instancename,path


We can see that what differentiates  a instance from the other it is the #(number) . The process does not will be the same. If I have 3 instances (default and 2 named) and I start the second named instance, that will be the sqlservr. Then if I start the first named instance that will be the sqlservr #1 and the deafult will be sqlservr#2.

The order from #(x) is by the order of the process was started. So how Can I know which SQL Server Instance corresponds to the process  ?

I  pass to the Get-ProcessName 3  parameters  :

[string] ComputerName : The name of the computer

[string]  SQLProcess  : is the path property from get-counter

and [switch] text : You an switch the output in a custom object or a string object (I dont like it, but it is to facilitate the display)

As I only have the path information, my next step is to query  Win32_PerfFormattedData_PerfProc_Process and search for the name of the process (sqlservr or sqlservr#1..etc)

The WMI return the IDProcess from this process name and I am sending as parameter to Get-Process and returning the Path from this process. In Get-Process the path is the full path excutable name . Let´s see how it works :

   1: invoke-command     -computername Obiwan `

   2:                 -ScriptBlock { Get-Process "*sql*" | select name, path | fl

   3:                               } 



Now it is just to extract the SQL Server Instance Name from the Path Alegre

and the final result to query all top 5 high process in Obiwan, excluding idle and _total with the SQL Server Instance Name (it if is in the list) :

   1: $ComputerName = "obiwan"

   2: while ($true) {

   3:     Get-Counter -ComputerName $ComputerName  '\process(*)\% processor time' `

   4:     | select  -ExpandProperty countersamples `

   5:     | where {  $_.path -notmatch '^*_total|idle*'}`

   6:     | sort cookedvalue -Descending  `

   7:     | Select     @{Expression= {(Get-Date)};Label = "DateTime"} ,`

   8:                 @{Expression= {$_.cookedvalue };Label = "Value"} ,`

   9:                 @{Expression= {(Get-ProcessName -ComputerName $ComputerName -SQLProcess $_.path -text ) };Label = "Information"} -First 5

  10:     Write-Host "`n"

  11: }



Function Get-ProcessName :

   1: function Get-ProcessName {

   2:         Param (

   3:             [Parameter(position=0)] [string] $ComputerName = $Env:COMPUTERNAME,

   4:             [Parameter(position=1)][String] $SQLProcess,

   5:             [Parameter(position=2)][switch] $Text


   7:         )

   8:     Begin {}

   9:     Process {


  11:         $ProcessName = $SQLProcess.Substring(($SQLProcess.IndexOf('(')+1),($SQLProcess.IndexOf(')')-1)-($SQLProcess.IndexOf('(')))


  13:         $Query = "Select * from Win32_PerfFormattedData_PerfProc_Process where name ='$($ProcessName)'" 

  14:         $IDProcess = (    Get-WmiObject     -Query $Query `

  15:                                         -ComputerName $ComputerName `

  16:                         | select IdProcess) 

  17:         if ($ProcessName -like "*sql*") {

  18:             $InstanceData  = (    invoke-command     -computername $ComputerName `

  19:                                                 -ScriptBlock { param ($IDProcess) Get-Process -Id $IDProcess | select path

  20:                                                              } `

  21:                                                 -ArgumentList $idprocess.idprocess

  22:                             )

  23:             $ProcessName = Get-RegexProcessName $InstanceData.Path 

  24:         }

  25:         if ($text) {

  26:             $output = "Name :$($ProcessName) | ID :$($IdProcess.IdProcess)" 

  27:         } else {

  28:             $Property = @{ 

  29:                                 'IdProcess'=$IdProcess.IdProcess;

  30:                                 'ProcessName'=$ProcessName; 

  31:                         }

  32:             $output = New-Object -Type PSObject -Prop $Property

  33:         }


  35:         Write-Output $output

  36:     }

  37:     End{}

  38: }

Function Get-RegexProcessName :

   1: function Get-RegexProcessName {

   2:     param ([String] $stringSQL)


   4:     #Regex - Thanks to Shay Levy and Ted Krueger


   6:     $output = "Not SQL Server Process"

   7:     if ($stringSQL -like "*sql*") {

   8:         $Output = "$([regex]::match($stringSQL,'\.([^\\]+)').Groups[1].Value)/$($stringSQL.Split('\')[-1])"

   9:     }

  10:     write-output $Output

  11: }

Why do I wrote 2 functions ? Reusable functions. Because I can use the Get-RegexProcessName  directly on the Get-Process cmdlet or inside the Get-ProcessName .

   1: Get-Process *sql* | select  name,`

   2:                             cpu,`

   3:                             @{Expression= {(Get-RegexProcessName $_.path ) };Label = "Information"} 


That is it guys Alegre


About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell, SQL SERVER EM GERAL, Virtual Pass BR. Bookmark the permalink.

1 Response to Displaying SQL Server Instance Name on Get-Process and Get-Counter

  1. Pingback: PowerShell – Diversas « Alex Souza

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s