functions/Get-DbaCpuUsage.ps1

function Get-DbaCpuUsage {
<#
    .SYNOPSIS
        Provides detailed CPU usage information about a SQL Server's process
 
    .DESCRIPTION
        "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.
 
        References: https://www.mssqltips.com/sqlservertip/2454/how-to-find-out-how-much-cpu-a-sql-server-process-is-really-using/
 
        Note: This command returns results from all SQL instances on the destionation server but the process
        column is specific to -SqlInstance passed.
 
    .PARAMETER SqlInstance
        Allows you to specify a comma separated list of servers to query.
 
    .PARAMETER SqlCredential
        Allows you to login to the SQL instance using alternative credentials.
 
    .PARAMETER Credential
        Allows you to login to the Windows Server using alternative credentials.
 
    .PARAMETER Threshold
        CPU threshold.
 
    .PARAMETER EnableException
        By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
        This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
        Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.
 
    .NOTES
        Tags: CPU
        dbatools PowerShell module (https://dbatools.io, clemaire@gmail.com)
        Copyright (C) 2016 Chrissy LeMaire
        License: MIT https://opensource.org/licenses/MIT
 
    .LINK
        https://dbatools.io/Get-DbaCpuUsage
 
    .EXAMPLE
        Get-DbaCpuUsage -SqlInstance sql2017
 
        Logs into the SQL Server instance "sql2017" and also the Computer itself (via WMI) to gather information
 
    .EXAMPLE
        $usage = Get-DbaCpuUsage -SqlInstance sql2017
        $usage.Process
 
        Explores the processes (from Get-DbaProcess) associated with the usage results
 
    .EXAMPLE
        Get-DbaCpuUsage -SqlInstance sql2017 -SqlCredential (Get-Credential sqladmin) -Credential (Get-Credential ad\sqldba)
 
        Logs into the SQL instance using the SQL Login 'sqladmin' and then Windows instance as 'ad\sqldba'
#>

    [CmdletBinding()]
    Param (
        [parameter(Mandatory, ValueFromPipeline)]
        [Alias("ServerInstance", "SqlServer", "SqlServers")]
        [DbaInstanceParameter[]]$SqlInstance,
        [PSCredential]$SqlCredential,
        [PSCredential]$Credential,
        [int]$Threshold = 0,
        [switch]$EnableException
    )
    begin {
        # This can likely be enumerated but I don't know hows
        $threadstates = [pscustomobject]@{
            0 = 'Initialized. It is recognized by the microkernel.'
            1 = 'Ready. It is prepared to run on the next available processor.'
            2 = 'Running. It is executing.'
            3 = 'Standby. It is about to run. Only one thread may be in this state at a time.'
            4 = 'Terminated. It is finished executing.'
            5 = 'Waiting. It is not ready for the processor. When ready, it will be rescheduled.'
            6 = 'Transition. The thread is waiting for resources other than the processor.'
            7 = 'Unknown. The thread state is unknown.'
        }

        $threadwaitreasons = [pscustomobject]@{
            0 = 'Executive'
            1 = 'FreePage'
            2 = 'PageIn'
            3 = 'PoolAllocation'
            4 = 'ExecutionDelay'
            5 = 'FreePage'
            6 = 'PageIn'
            7 = 'Executive'
            8 = 'FreePage'
            9 = 'PageIn'
            10 = 'PoolAllocation'
            11 = 'ExecutionDelay'
            12 = 'FreePage'
            13 = 'PageIn'
            14 = 'EventPairHigh'
            15 = 'EventPairLow'
            16 = 'LPCReceive'
            17 = 'LPCReply'
            18 = 'VirtualMemory'
            19 = 'PageOut'
            20 = 'Unknown'
        }
    }
    process {
        foreach ($instance in $SqlInstance) {
            try {
                Write-Message -Level Verbose -Message "Connecting to $instance"
                $server = Connect-SqlInstance -SqlInstance $instance -SqlCredential $sqlcredential
            }
            catch {
                Stop-Function -Message "Failure" -Category ConnectionError -ErrorRecord $_ -Target $instance -Continue
            }

            $processes = Get-DbaProcess -SqlInstance $server
            $threads = Get-DbaCmObject -ComputerName $instance.ComputerName -ClassName Win32_PerfFormattedData_PerfProc_Thread -Credential $Credential | Where-Object { $_.Name -like 'sql*' -and $_.PercentProcessorTime -ge $Threshold }

            if ($server.VersionMajor -eq 8) {
                $spidcollection = $server.Query("select spid, kpid from sysprocesses")
            }
            else {
                $spidcollection = $server.Query("select t.os_thread_id as kpid, s.session_id as spid
            from sys.dm_exec_sessions s
            join sys.dm_exec_requests er on s.session_id = er.session_id
            join sys.dm_os_workers w on er.task_address = w.task_address
            join sys.dm_os_threads t on w.thread_address = t.thread_address"
)
            }

            foreach ($thread in $threads) {
                $spid = ($spidcollection | Where-Object kpid -eq $thread.IDThread).spid
                $process = $processes | Where-Object spid -eq $spid
                $threadwaitreason = $thread.ThreadWaitReason
                $threadstate = $thread.ThreadState
                $ThreadStateValue = $threadstates.$threadstate
                $ThreadWaitReasonValue = $threadwaitreasons.$threadwaitreason

                Add-Member -Force -InputObject $thread -MemberType NoteProperty -Name ComputerName -value $server.NetName
                Add-Member -Force -InputObject $thread -MemberType NoteProperty -Name InstanceName -value $server.ServiceName
                Add-Member -Force -InputObject $thread -MemberType NoteProperty -Name SqlInstance -value $server.DomainInstanceName
                Add-Member -Force -InputObject $thread -MemberType NoteProperty -Name Processes -Value ($processes | Where-Object HostProcessID -eq $thread.IDProcess)
                Add-Member -Force -InputObject $thread -MemberType NoteProperty -Name ThreadStateValue -Value $ThreadStateValue
                Add-Member -Force -InputObject $thread -MemberType NoteProperty -Name ThreadWaitReasonValue -Value $ThreadWaitReasonValue
                Add-Member -Force -InputObject $thread -MemberType NoteProperty -Name Process -Value $process
                Add-Member -Force -InputObject $thread -MemberType NoteProperty -Name Query -Value $process.LastQuery
                Add-Member -Force -InputObject $thread -MemberType NoteProperty -Name Spid -Value $spid

                Select-DefaultView -InputObject $thread -Property ComputerName, InstanceName, SqlInstance, Name, ContextSwitchesPersec, ElapsedTime, IDProcess, Spid, PercentPrivilegedTime, PercentProcessorTime, PercentUserTime, PriorityBase, PriorityCurrent, StartAddress, ThreadStateValue, ThreadWaitReasonValue, Process, Query
            }
        }
    }
}