 |
|
How to find out how much CPU a SQL Server process is really using
|
By: Manvendra Singh | Read Comments (21) | Print Manvendra has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc. Related Tips: More |
|
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.
Last Update: 8/9/2011
| Share: |  |
Share | Tweet |
|
 |
 |
Connect with MSSQLTips.com |
Comments and Feedback:
| Tuesday, August 09, 2011 - 3:26:32 AM - Mohammed Moinudheen | Read The Tip |
|
Good one. Thanks. |
|
| Tuesday, August 09, 2011 - 1:39:22 PM - Satinder Thakur | Read The Tip |
|
Goood Job! Fantastic ...!!! |
|
| Tuesday, August 09, 2011 - 4:15:36 PM - Excellent article. Thank you so much. | Read The Tip |
| Tuesday, August 09, 2011 - 7:07:53 PM - Manish | Read The Tip |
|
Thanks for sharing Knowledge. |
|
| Wednesday, August 10, 2011 - 2:15:57 AM - Ajay Gupta | Read The Tip |
|
Thanks a lot |
|
| Wednesday, August 10, 2011 - 11:07:59 AM - Sasi | Read The Tip |
|
Yep...that helps us a lot |
|
| Thursday, August 11, 2011 - 5:48:56 AM - Hans | Read The Tip |
|
Once in a while, a diamond article pops-up; this is one of those articles! Thank you for the excellent publication. |
|
| Thursday, August 11, 2011 - 7:30:45 AM - Jey Nav | Read The Tip |
|
Really Good one.
Thanks, |
|
| Sunday, August 14, 2011 - 8:38:30 PM - Kyle | Read The Tip |
|
This is amazing. |
|
| Tuesday, August 16, 2011 - 9:08:00 AM - rajendra | Read The Tip |
|
Nice one!!!!!!!!! |
|
| Saturday, August 20, 2011 - 9:45:09 AM - SurendraP | Read The Tip |
|
It is really good post and it helped me a lot while trobleshooting the same problem. Thanks a lot !!!! :) |
|
| Saturday, August 20, 2011 - 1:28:48 PM - Bunty | Read The Tip |
|
Very well explained and this article is very useful for core DBA's. Great job !!!!! =========================================== Better try and fail, instead of not trying at all...
|
|
| Saturday, August 20, 2011 - 5:05:56 PM - bool | Read The Tip |
|
Very well explained one... I was searching for this for very long |
|
| Friday, August 26, 2011 - 8:35:49 AM - Nitin Kumar | Read The Tip |
|
Excellent writeup in step-by-step manner. Really good one. |
|
| Tuesday, September 20, 2011 - 5:42:59 AM - M Azim | Read The Tip |
|
Excellent and precisely explained |
|
| Thursday, November 17, 2011 - 9:57:45 AM - Idris Chambas | Read The Tip |
|
Superb. A brilliant way of finding the smoking gun. Many Thanks. |
|
| Tuesday, December 13, 2011 - 4:08:53 AM - Manvendra | Read The Tip |
|
Thank you guys for such a great feedbacks. |
|
| Thursday, February 16, 2012 - 9:20:28 AM - Christoph | Read The Tip |
| Hi On our Win2008R2-Server, the View of "Add Counter" doesn't display the instances, only "All" can selected. When I select all, then the differenz Counters of all instances is insertet. What the reason, that I cant's select my SQL-Server-Instance? best regards Christoph | |
| Thursday, February 16, 2012 - 10:18:32 AM - Manvendra | Read The Tip |
| Yes its lil different in Win2k8R2. There is a dropdown along with a search button.Just select "All Instances" in that drop down and click on search button.it will display all instances of your selected object, now you can choose only sql server processes by pressing cntrl button. | |
| Monday, February 20, 2012 - 4:33:57 PM - Christoph | Read The Tip |
|
Ah, I have to click search-Button to see something. Thanks. |
|
| Wednesday, April 25, 2012 - 1:38:30 PM - bud | Read The Tip |
|
Thanks so much for this great article and the great suggestion. I was wondering if I collected the above listed threads (only for sqlsrvr 2008) for a period for say a day or a couple of days, whether there is some DMV or some internal SQL feature that I can corelate. We are after those sql jobs that continuosly CPU spike. Thanks again. |
|
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |