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
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
Now lets get the top 5 high process :
We can do it continuosly , but we cannot use the –continuous parameter, we need to just do it in a while:
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)
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 :
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 :
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) :
Function Get-ProcessName :
Function Get-RegexProcessName :
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 .
That is it guys