What’s Taking Up My SQL CPU

I found an article on how to identify the thread(s) that are taking up SQL memory but wanted a single script to do it all.  Below is a powershell script I put together to give me the information on what query the thread is running. Credit goes to  at http://www.mssqltips.com/sqlservertip/2454/how-to-find-out-how-much-cpu-a-sql-server-process-is-really-using/ for the actual SQL queries I used.

Of course a disclaimer statement… Test in your lab and use at your own risk.

The first 2 variables are straight forward.  The CpuThreshold pulls back on threads over that threshold. If anyone comes up with improvements, I’d love to have a copy.

$Server=”SERVERNAME”
$SqlInstance=’SQLSERVER\INSTANCE’
$CpuThreshold=”0″ #CPU usage of thread must be above this value

$Threads=get-wmiobject -ComputerName $server -class Win32_PerfFormattedData_PerfProc_Thread  -filter “name like ‘sql%’ and percentprocessortime>$CpuThreshold”

if ($Threads) {
$SqlConn = new-object system.data.sqlclient.sqlconnection
$SqlConn2= new-object system.data.sqlclient.sqlconnection
$SQLConn.ConnectionString=”Server=$SqlInstance;database=Master;trusted_connection=true;”
$SQLConn2.ConnectionString=”Server=$SqlInstance;database=Master;trusted_connection=true;”
$SQLConn.open()
$SQLConn2.open()
foreach ($Thread in $Threads) `
{$ThreadID=$Thread.IDThread
$SqlQuery=”select top 1 spid from sysprocesses where kpid=$ThreadID”
$SQLCmd=new-object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText=$SqlQuery
$SqlCmd.Connection=$SqlConn
$SqlCmd.CommandTimeout=600

[System.Data.SqlClient.SqlDataReader]$SqlRs = $SqlCmd.ExecuteReader()

while ($SqlRs.Read())
{
$Spid =$SqlRs.item(“spid”)
write-host “The Spid for Thread $ThreadID is $Spid. (cpu=$($Thread.PercentProcessorTime))”
$SqlCmd2=new-object System.Data.SqlClient.SqlCommand
$SqlQuery=”DBCC inputbuffer($Spid)”
$SqlCmd2.commandtext=$SqlQuery
$SqlCmd2.connection=$SqlConn2
$SqlRs2=$SqlCmd2.ExecuteReader()
if ($SqlRs2.Read())
{ $EventInfo =$SqlRs2.item(“EventInfo”)
Write-host “–>$EventInfo”
}
else
{write-host “No Event Info returned”
}
write-host “—————————————————”
write-host “Query for all threads for SPID: SELECT spid, kpid, status, cpu, memusage, open_tran, dbid FROM sysprocesses WHERE spid=$Spid”
write-host “=============================”
} #End While

$SqlRs.Close() | Out-Null
} # End For

$SqlConn.Close() | out-null
$SqlConn2.close() | Out-Null

}
else
{ write-host “No Threads found to process” }

write-host “—- Done ——”

 

One thought on “What’s Taking Up My SQL CPU

  1. Kumar

    Hi,

    I have tried your script but didn’t workout and I am very new to powershell.  Can you provide the details how it could be work.  My environment details as follows.

    Sqlserver 2014(Default Instace)

    Windows server 2012

     

    Regards

    Narendra.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.