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.

$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
foreach ($Thread in $Threads) `
$SqlQuery=”select top 1 spid from sysprocesses where kpid=$ThreadID”
$SQLCmd=new-object System.Data.SqlClient.SqlCommand

[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)”
if ($SqlRs2.Read())
{ $EventInfo =$SqlRs2.item(“EventInfo”)
Write-host “–>$EventInfo”
{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

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

write-host “—- Done ——”


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

  1. Kumar


    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




Leave a Reply

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