Enhancing Your Troubleshooting Toolset with PowerShell - SQL Server 2012 Internals and Troubleshooting - page 395

Get-WmiObject -Class Win32_Service -Property Name,State,StartMode -Filter
"StartMode='Auto' AND State='Stopped'" '
| Select-Object -Property Name,StartMode,State
Finding out which processes are active is useful during troubleshooting. Often, a process has run
away with CPU or memory and is therefore putting pressure on the system, causing other vital
services to be starved of resources. Once again PowerShell jumps to the rescue with Get-Process ,
which returns process information. A busy system will have many processes running, so I like to run
two queries — the i rst i nds the top 10 processes by CPU utilization, and the second i nds the top
10 processes by memory utilization. The following example queries the top 10 processes by
CPU utilization and displays the results from my SQL2012 Virtual Machine:
PS C:\> get-process | Sort-Object CPU -Descending | Select-Object -First 10
Handles NPM(K) PM(K) WS(K) VM(M) CPU(s) Id ProcessName
------- ------ ----- ----- ----- ------ -- -----------
388 50 41052 56776 222 48.50 3660 iexplore
783 53 41408 58104 287 28.92 2452 explorer
889 78 101688 144016 548 27.94 2208 Ssms
914 47 196188 216300 639 27.03 2252 powershell
57 7 1352 5072 70 12.00 3580 notepad
637 55 58648 73928 621 7.56 3028 SQLPS
564 43 16048 29524 177 5.97 3396 iexplore
39 6 2028 5036 57 2.97 3008 conhost
223 17 7816 17984 111 1.02 3968 notepad
79 9 1392 4480 71 0.19 2632 VBoxTray
This isn't a busy system, so the usual suspects such as Internet Explorer (iexplore) and Windows
Explorer (explorer) are high in the list. The following example shows a similar query, this time
querying by memory utilization:
PS C:\> get-process | Sort-Object WS -Descending | Select-Object -First 10
Handles NPM(K) PM(K) WS(K) VM(M) CPU(s) Id ProcessName
------- ------ ----- ----- ----- ------ -- -----------
639 88 444404 308232 -502 1268 sqlservr
816 47 200100 220736 640 28.13 2252 powershell
818 77 99328 143800 547 28.22 2208 Ssms
618 52 101812 74352 1906 1452 ReportingServicesService
637 55 58648 73928 621 7.56 3028 SQLPS
784 54 41748 58472 288 29.61 2452 explorer
388 50 40948 56748 221 48.52 3660 iexplore
1690 54 75844 54556 674 1312 msmdsrv
1061 52 28388 35400 432 848 svchost
311 26 31896 33084 534 1336 SMSvcHost
In this case, the processes consuming the largest working set of memory are different from the
processes consuming the most CPU. Finding processes that are using excessive CPU or memory
gives you a scope for further investigation.