SQL Server : Get CPU consuming query

On certain occasions you will have to troubleshoot high CPU consumption on a server. A rogue query would start chewing up CPU resource and you will see that sqlserver.exe will be using up all the CPU. 

Before running a profiler trace to see which queries are consuming more CPU resources than others, run the following TSQL to capture the SPID that is consuming most of the CPU. In the script I have given an interval of 5 secs and if you want you can use an interval on 10 or 15 secs too.

create table #cpu2 (spid int, dbid int, cpu bigint)

create table #cpu (spid int, dbid int, cpu bigint)

insert into #cpu

select spid, dbid, cpu from sysprocesses order by cpu desc

WAITFOR DELAY '00:00:05'

insert into #cpu2

select spid, dbid, cpu from sysprocesses order by cpu desc

select * from sysprocesses where spid in (select t.spid from #cpu2 t, #cpu o 

where t.cpu> o.cpu and t.spid=o.spid) order by cpu desc

drop table #cpu2

drop table #cpu

Run the DBCC inputbuffer with the SPID at the top of the results and you will find the query that is chewing up all the CPUs.

If you want to select the top 10 queries that are currently consuming more CPU than others then use the top 10 SPIDs to find the queries.

This is mostly useful if you have one or two rogue queries. If you have a set of queries that you doubt are using up more resources then run the profiler trace to capture them in real time.