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