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 
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
6:
7: )
8: Begin {}
9: Process {
10:
11: $ProcessName = $SQLProcess.Substring(($SQLProcess.IndexOf('(')+1),($SQLProcess.IndexOf(')')-1)-($SQLProcess.IndexOf('(')))
12:
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: }
34:
35: Write-Output $output
36: }
37: End{}
38: }
Function Get-RegexProcessName :
1: function Get-RegexProcessName {
2: param ([String] $stringSQL)
3:
4: #Regex - Thanks to Shay Levy and Ted Krueger
5:
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
#PowerShellLifeStyle
Pingback: PowerShell – Diversas « Alex Souza