Using Process Explorer to resolve SQL Server CPU Spikes

As a DBA, one usually comes across a situation when the CPU usage on a server spikes because of SQL Server. The knee jerk reaction would be to check sysprocesses or sys.dm_exec_requests to check which process is consuming more CPU time. The value of CPU column in sysprocesses is cumulative in nature, hence may not give the right picture everytime.

The Process Explorer utility from Microsoft also comes in handy in such situations. Here is how it can be done.

Open Process Explorer and navigate to sqlservr.exe

Right click on sqlservr.exe and select Properties. Click on Threads tab and sort by CPU.

Make a note of the TID which is consuming more CPU. In this case it is 11744. Open SSMS and get the SPID. The query can be something like this.

select SPID from sysprocesses where kpid=11744

The details of the SPID can be obtained by issuing

DBCC INPUTBUFFER(SPID)

Based on the output of the Inputbuffer, a call to KILL the SPID can be taken.