UPDATE February 13, 2012: As of this update, this post is about four years old. And a lot of things have changed since then. For one, the video is no longer available (as many have discovered). But if you’re trying to identify a query or queries that are consuming the most CPU, I strongly recommend using the sp_whoisactive stored procedure. It’s the method I use now rather than using process explorer What follows is the original 2008 post:
Recently when trying to identify sql processes consuming the most CPU, I came across this most excellent paper written by Jeff Stevenson. It’s called Identifying High CPU SQL Processes.
It describes how to identify which sql processes that are consuming the most cpu cycles. The good part is that he describes how to do it using only tools that are available via SQL Server or the Operating System (i.e. Profiler, Performance Monitor, Management Studio).
It was very useful but the most tedious part of the process was finding the SQL Server process id. The process required a scavenger hunt where it was necessary to find the instance, then thread instance id, then kernel process id, then SQL process id. This required two performance monitor sessions with very awkward settings.
I believe I have an improvement that allows us to skip a number of steps. It requires the use of a free Microsoft tool, but the drawback is that the tool is not available either with the operating system or SQL Server. It’s a free download and the installation is easy, but if, as a dba, you get a call to troubleshoot a problem on a remote server, and the remote server doesn’t have this tool, then you won’t be able to use this tool.
The tool I’m talking about is Process Explorer (made by the guys once known as SysInternals). It’s a free download here. I highly recommend it. At least install it on your development box.
I’ve attached a video (no audio) that gives you an idea of what’s involved when identifying the kpid:
The video shows how to use Process Explorer to find the SQL instance and the thread of the offending sql process. In this particular case, I sort the processes by cpu and I see the largest CPU consumer is sqlservr.exe (If it’s not then this post isn’t for you). I select the SQL Server process and click on the properties icon to launch the properties window. On the image tab I learn that the database instance that is consuming cpu is called “SQL2005”. On the threads tab I learn that the thread id is 3960. So 3960 is what SQL Server refers to as the kernel process id (kpid).
You now have the SQL Server instance and the kpid of the process. After that, it’s time to find out what this process is doing. You can do this by following Jeff Stevenson’s method that I linked to at the top of this post. It’s a matter of using a set of sql queries similar to the following:
select * from master..sysprocesses where kpid = 3960
-- turns out to have spid = 55
dbcc inputbuffer(55)
-- shows what 55 is doing:
-- and if absolutely necessary:
kill 55 -- be careful with this :-)
There’s one last thing I want to mention. You may find that all the connections are taken. It’s an very likely scenario if SQL Server is misbehaving. In this case you can log in using the Dedicated Administrator’s Connection. Definitely practice this before any crisis so that you don’t have to look up any syntax at the time. Essentially, you want to log onto ADMIN:SERVER\INSTANCE instead of SERVER\INSTANCE.
Update !!!
In an extremely timely post, John Paul Cook describes how to get around the drawback of not having sysinternals installed on the remote machine. If the machine can access the internet, then you can simply run the following:
\\live.sysinternals.com\tools\procexp.exe
Hi Michael,
Using Process Explorer is a great idea! This is a super useful tip. Wading through the Performance Monitor counters is a huge PITA.
Comment by Lukas Rathswohl (SQL Server Blog) — December 15, 2008 @ 12:03 pm
You’re right. Especially when time is of the essence (as is the case when dealing with a crisis)
Comment by Michael J. Swart — December 16, 2008 @ 9:20 am
The friggin video wont play it says not available what good is it?
Comment by joseph ferrante — May 24, 2011 @ 4:45 pm
Sorry about that Joseph, Luckily my blog has a guarantee. 100% satisfaction or double your money back. You can expect that in your mailbox shortly.
Comment by Michael J. Swart — May 24, 2011 @ 5:08 pm
I need the video as it won’t play. Is that possible
Comment by Andrew — August 31, 2011 @ 12:40 pm
The video was hosted at my old blog and I guess it didn’t survive the move to my new one. I’ll create a new video and link it to this post sometime this week.
Comment by Michael J. Swart — September 1, 2011 @ 10:28 am
Hi, nice article – thanks, but the video working would also be nice :).
Comment by max — January 16, 2012 @ 3:29 am