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.
Solution
Normally we monitor this type of issue using sp_who2, Activity Monitor or DMVs. If there are a lot of processes running on your instance and the CPU is very high, then it’s hard to find the exact process eating up your CPU using just the SQL Server tools. One way to correlate the data between what is running within SQL Server and at the Windows level is to use SPID and KPID values to get the exact process.
Before discussing how to do this, we should discuss these terms:
- SPID is the SQL Server Process ID number and is assigned by SQL Server to each new connection. It starts with one and is globally unique. SPID 1 through 50 are reserved for system uses and are not used for any user connections.
- KPID is the kernel-process ID. Under SQL Server for Windows this is the thread ID number, also known as “ID Thread,” and is assigned by Windows when the thread is created. The Thread ID number is a system-wide identifier that uniquely identifies the thread. KPID is visible by querying the KPID column of master..sysprocesses. It is only filled in for spid numbers four and higher. You can also get KPID/ID Thread from Windows Perfmon using the “Thread” object and the “ID Thread” counter.
I am sure you are familiar with the SPID value, but have you ever noticed KPID? Probably not unless you queried the sysprocesses view directly. Let’s look at example of how this works.
Example
Step 1
If we look at Task Manager on the server we can see the CPU usage. So the first step is to verify that the SQL Server process (sqlservr), and not some other process, is responsible for the excessive CPU use. Remember, the “sqlservr” process is made up of many different threads, each one generally (but not always) representing a specific user connection. In the screenshot below we see the sqlservr process is taking approximately 70 percent of CPU.
Now that we know SQL Server is the main culprit and responsible for the high CPU, the next step is to find out which SQL Server process is causing the high CPU.
Step 2
Now we need to find out what SQL Server thread is causing the high CPU. Once we have this ID Thread we can correlate that ID thread (KPID) to the SPID in SQL Server.
We will use Performance Monitor to get this info. Type perfmon in a Windows CMD prompt or launch from Control Panel.
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.
Step 3
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.
Step 4
Our last step is to correlate the Thread ID (KPID) identified in the last step to the SPID. To do this, run the following query in Query analyzer:
Step 5
From the query above we can see SPID 71 is causing the issue. To find how many threads and open transactions this is running we can run this query.
Step 6
To get the exact query that is running, we can run DBCC INPUTBUFFER using the SPID. The below output shows us that a backup job is causing our CPU issues on our server.
Next Steps
- Use this procedure to find the main culprit in SQL Server which is eating up your CPU. You can use this correlation in many performance related tasks and can track many Perfmon counters to an individual thread and with that you can optimize your SQL Server.
- If you have a lot of SQL Server processes, you can use the line graph in Perfmon to find the process that is using high CPU and hover over the line in the chart and it will show you the SQL Server thread. Then you can change to the Report view and scroll over to that SQL Server process to see the details for the counters you selected.
