Here is a script I wrote which uses Terry's technique, and expands on it.  It gets all the sysprocesses into a temp table, then waits for (default) five seconds.  It then checks sysprocesses again and calculates the change in the cpu column.  It returns the results, in order of the cpu usage descending (the process that uses the most clock cycles is at the top of the list) and then, using a cursor it loops through the top few results running dbcc inputbuffer against them and returns the output.

Put this in the master database, and run when required.

Merry Christmas everybody,

Martin

CREATE proc sp_CPUByProcess
@TimePeriod varchar(8) = '00:00:05'
as

set nocount on

--drop table #sysprocessUsage

create table #sysprocessUsage (cpu int, spid smallint, cpuIncrease int)

set nocount on
insert into #sysprocessUsage (cpu, spid)
select 
 cpu,
 spid
from
 master..sysprocesses

waitfor delay @TimePeriod

update
 #sysprocessUsage
set
 cpuIncrease = sp.cpu - spu.cpu
from
 master..sysprocesses sp
 inner join #sysprocessUsage spu
  on sp.spid = spu.spid

select
 spu.cpuIncrease,
 sp.*
from
 master..sysprocesses sp
 inner join #sysprocessUsage spu 
  on sp.spid = spu.spid
where
 spu.cpuIncrease > 0
order by
 sp.cpu - spu.cpu desc
compute
 sum(cpuIncrease)

declare @spid int, @cpuIncrease int
declare @qry nvarchar(50)

declare c cursor for
 select cpuIncrease, spid from #sysprocessUsage where cpuIncrease > 0 and spid <> 0 order by cpuIncrease desc
for read only
open c

fetch next from c into @cpuIncrease, @spid
while @@fetch_status = 0
begin

 set nocount on
 exec ('print ''DBCC INPUTBUFFER FOR SPID ' + @spid + '''')

 select @qry = 'dbcc inputbuffer(' +  rtrim(convert(char(5),@spid)) + ')'
 exec( @qry )

 fetch next from c into @cpuIncrease, @spid
end

close c
deallocate c