“If there are a lot of processes running on your instance and the CPU is very high, then it’s hard to find the exact process eating up your CPU using just the SQL Server tools. One way to correlate the data between what is running within SQL Server and at the Windows level is to use SPID and KPID values to get the exact process.”
This command automates that process.
Note: This command returns results from all SQL instances on the destination server but the process column is specific to -SqlInstance passed.
Logs into the SQL Server instance “sql2017” and also the Computer itself (via WMI) to gather information.
Get-DbaCpuUsage -SqlInstance sql2017
Explores the processes (from Get-DbaProcess) associated with the usage results.
$usage = Get-DbaCpuUsage -SqlInstance sql2017
Logs into the SQL instance using the SQL Login ‘sqladmin’ and then Windows instance as ‘ad\sqldba’.
Get-DbaCpuUsage -SqlInstance sql2017 -SqlCredential (Get-Credential sqladmin) -Credential (Get-Credential ad\sqldba)
This command was created by Chrissy LeMaire. You can find Chrissy on Twitter.