Find using PowerShell, who use the CPU on Microsoft SQL Server

In solving problems with a high load on the server CPU Microsoft SQL Server saw the article "How to find out how much CPU a SQL Server process is really using" but when a large number of processors is not possible to identify problem processes. So the idea came from a script to automate powershell. With this script you can quickly identify problematic processes on the database Microsoft SQL Server:

Download script cpu_spid

$ServerInstance = "YourSQLServer\SQLServerInstanceName"
$CpuThreshold = "90"
$Database = "master"
$ConnectionTimeout = 30
$QueryTimeout = 120
$sql_spid = $null
$QueryBase = "SELECT top 1 spid FROM sysprocesses WHERE kpid="

$proc_time_counters = (get-counter -listset thread).PathsWithInstances | where { ($_ -like "*sqlservr*") -and ($_ -like "*Processor Time") }
$proc_time_values = (get-counter -counter $proc_time_counters).countersamples | sort CookedValue
foreach ($proc_time_value in $proc_time_values)
{
    if ($proc_time_value.CookedValue -gt $CpuThreshold)
    {
        $thread_id_counter = "\" + $proc_time_value.path.split("\")[3] + "\" + "ID Thread"
        $thread_id_value = (get-counter $thread_id_counter).readings.split(":")[1]
        write-host "ID Processor Counter:" $proc_time_value.path
        write-host "Processor Time, %:" $proc_time_value.CookedValue
        write-host "ID Thread Counter:" $thread_id_counter
        write-host "Thread:" $thread_id_value.trim()
        $Query = $QueryBase + $thread_id_value.trim()
        $conn = new-object System.Data.SqlClient.SQLConnection
        $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance, $Database, $ConnectionTimeout
        $conn.ConnectionString = $ConnectionString
        $conn.Open()
        $cmd = new-object system.Data.SqlClient.SqlCommand($Query, $conn)
        $cmd.CommandTimeout = $QueryTimeout
        $ds = New-Object system.Data.DataSet
        $da = New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
        [void]$da.fill($ds)
        $conn.Close()
        $sql_spid = ($ds.Tables[0] | select -first 1).spid
        if ($sql_spid)
        {
            write-host "SQL spid:" $sql_spid
            $conn.ConnectionString = $ConnectionString
            $conn.Open()
            $Query2 = "sp_who2 " + $sql_spid
            $cmd2 = new-object system.Data.SqlClient.SqlCommand($Query2, $conn)
            $cmd2.CommandTimeout = $QueryTimeout
            $ds2 = New-Object system.Data.DataSet
            $da2 = New-Object system.Data.SqlClient.SqlDataAdapter($cmd2)
            [void]$da2.fill($ds2)
            $conn.Close()
            $conn.ConnectionString = $ConnectionString
            $conn.Open()
            $Query3 = "DBCC inputbuffer($sql_spid)"
            $cmd3 = new-object system.Data.SqlClient.SqlCommand($Query3, $conn)
            $cmd3.CommandTimeout = $QueryTimeout
            $ds3 = New-Object system.Data.DataSet
            $da3 = New-Object system.Data.SqlClient.SqlDataAdapter($cmd3)
            [void]$da3.fill($ds3)
            $conn.Close()
            write-host "@@@@@@ SQL session begin @@@@@"
            $ds2.Tables[0]
            $ds3.Tables[0]
            write-host "@@@@@@ SQL session end @@@@@"
        }
    else { write-host "SQL spid: no found" }
    write-host "--------------------------"
    }
}