Skip to content

Instantly share code, notes, and snippets.

@benf101 benf101/high cpu.md Secret
Last active Jan 17, 2017

Embed
What would you like to do?
Identifying which SPID is causing high CPU usage.

This is a boiled down version of: https://www.mssqltips.com/sqlservertip/2454/how-to-find-out-how-much-cpu-a-sql-server-process-is-really-using/

Problem

Someone has reported a performance issue for your SQL Server application. When you look into the database server you see CPU utilization is very high and the SQL Server process is consuming most of the CPU. You launch SSMS and run sp_who2 and notice that there are a few SPIDs taking a long time to complete and these queries may be causing the high CPU pressure. At the server level you can only see the overall SQL Server process, but within SQL Server you can see each individual query that is running. Is there a way to tell how much CPU each SQL Server process is consuming? In this article I explain how this can be done.

In PERFMON

Click on Add counters and select the "Thread" object in the drop down.

Select these counters at the same time:

  • % Processor Time
  • ID Thread
  • Thread State
  • Thread Wait Reason

In the right pane, you will see multiple instances from multiple applications, which are running on that server. Since we are looking for "sqlservr" select all of the instances that begin with "sqlservr" from the list box as shown below and click Add. You can add other counters as needed and will see below that I added a few additional counters.

Find the guilty SPID

Press (Ctrl+R) or click on the view Report tab to change from graphical to report view as shown below. Here you should be able to find which ID Thread is eating up your CPU resources by using the scroll bar to see all instances.

Below we have identified which thread is causing the problem. Here you can see ID Thread 30 is taking 46.841% of the total CPU. You can also find the ID Thread which is 872. Now that we know the ID Thread, we can run a query in SSMS to find the actual query.

Get the SPID info in TSQL

SELECT spid, kpid, dbid, cpu, memusage FROM sysprocesses WHERE kpid=872

Get the SPID's activity

To get the exact query that is running, we can run DBCC INPUTBUFFER using the SPID.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.