Identifying high CPU SQL Server threads

Some great tips are available to those of us who subscribe to the SQLSkills Insiders email list (http://www.sqlskills.com/JoinCommunity.asp). The 29th April 2012 email featured a video by Jonathan Kehayias demonstrating how to use Process Explorer to identify high CPU SQL Server threads. Jonathan showed that once you’ve identified the thread IDs you can use the following SQL to obtain the query and plan details:

-- Source: SQLSkills Insiders email 29/04/2012
-- Use the system thread id to find the executing session information
SELECT r.session_id, st.text, qp.query_plan
FROM sys.dm_os_threads AS ot
JOIN sys.dm_os_tasks AS t
ON t.worker_address = ot.worker_address
JOIN sys.dm_exec_requests AS r
ON t.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE os_thread_id = <YourThreadID>

Today’s blog post presents a Powershell script that can be used in multi-instance environments (eg clusters) to identify high CPU SQL Server threads. The script uses the performance monitor counters for threads.

The long standing issue for multiple instances is that performance monitor differentiates each running sqlservr.exe process using a hash followed by a number. Trying to relate these hashes to the actual SQL Server instances can be difficult. For example, if you have three instances of SQL Server the instance names for the threads performance counters will be as follows:

For the first SQL Server instance: \\computername\thread(sqlservr/0)\<countername> to \\computername\thread(sqlservr/xxx)\<countername> where xxx is the number of threads you have for this instance

For the second SQL Server instance: \\computername\thread(sqlservr/0#1)\<countername> to \\computername\thread(sqlservr/xxx#1)\<countername>

For the third SQL Server instance: \\computername\thread(sqlservr/0#2)\<countername> to \\computername\thread(sqlservr/xxx#2)\<countername>

Using the registry change listed in the following Microsoft support document

http://support.microsoft.com/kb/281884

it is possible to replace the hashes with process IDs. We then have meaningful names that we can relate back to our SQL Server instances. Along the same lines as ProcessNameFormat, another document

http://msdn.microsoft.com/en-us/library/windows/desktop/aa372175%28v=vs.85%29.aspx

mentions ThreadNameFormat. Using both these format keys we are able replace the meaningless hash symbol and /0, /1, /2 with meaningful names. Our full counter names will now appear as follows:

\\computername\thread(sqlservr_3012/4896)\<countername>

With these registry changes in place the Powershell script simply fetches five samplings of the thread % processor time. For each distinct process ID it groups on processID/threadID (eg 3012/4896 for the example above) and calculates the average, minimum, maximum and count for the five samplings. It finally sorts by the average descending and selects the first five rows.


$ComputerName = "YOURCOMPUTERNAME"

$PerfCounter = "Thread(sqlservr*)\% Processor Time"

$MaxSamples = 5

$ThreadData = Get-Counter -ComputerName $ComputerName -Counter $PerfCounter -MaxSamples $MaxSamples -ErrorAction:SilentlyContinue |
ForEach-Object {$_.CounterSamples | Select-Object Timestamp,CookedValue,Path}

# Distinct process IDs
$PerfCounterPrefix = $ThreadData | Select-Object -Unique @{Name="PerfCounterPrefix";Expression={ $_.Path.SubString(0, $_.Path.IndexOf("/"))}}

# For each of the distinct process IDs select the five threads with the highest average % processor time
ForEach ($Prefix in $PerfCounterPrefix) {
$ThreadData |
Where-Object {$_.Path -match (Select-Object -ExpandProperty $Prefix)} |
# This groups by ProcessID/ThreadID
Group-Object -Property { $_.Path.SubString($_.Path.IndexOf("_") + 1, $_.Path.IndexOf(")") - $_.Path.IndexOf("_") - 1) } |
Select-Object `
@{Name="ProcessID";Expression={($_.Name -split "/")[0]}}, # Left side of / is ProcessID
@{Name="ThreadID";Expression={($_.Name -split "/")[1]}},  # Right side of / is ThreadID
@{Name="PercentProcessorAvg";Expression={[Math]::Round(($_.Group | Measure-Object -Property CookedValue -Average).Average,2)}},
@{Name="PercentProcessorMin";Expression={[Math]::Round(($_.Group | Measure-Object -Property CookedValue -Minimum).Minimum,2)}},
@{Name="PercentProcessorMax";Expression={[Math]::Round(($_.Group | Measure-Object -Property CookedValue -Maximum).Maximum,2)}},
@{Name="NumberOfSamples";Expression={[Math]::Round(($_.Group | Measure-Object).Count,2)}} |
Sort-Object -Property PercentProcessorAvg -Descending |
Select-Object -First 5 |
Format-Table -Auto
}

Please take note of the line continuation back ticks.

Example output:

For a multi-instance server you’ll have five rows for each process ID. You can identify the named instance by plugging the process ID into this (replace xxxx):

(Get-WmiObject Win32_Service -ComputerName $ComputerName -Filter “ProcessId=xxxx”).Name

You can use this same approach to identify other performance metrics at the thread or process level.

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: